Some help with MS Access and a module

Factual Questions
1

was wondering if somebody could shed some light on this topic for me.

I made a module that gives me the median value of some numbers (for some stupid reason MS Access doesn’t have a built-in median function). I found a module that gave me the basic outlines of the median function:

Function MedianMaker(tName As String, fldName As String) As Double

 Dim MedianDB As Database
 Dim ssMedian As Recordset
 Dim RCount%, i%, x%, y%, OffSet%
 Set MedianDB = CurrentDb()
 Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & "] FROM [" & tName & "] WHERE [" & fldName & "] IS NOT NULL ORDER BY [" & fldName & "];")
           
 ssMedian.MoveLast
 RCount% = ssMedian.RecordCount
 x% = RCount% Mod 2
 If x% <> 0 Then
    OffSet% = ((RCount% + 1) / 2) - 2
    For i% = 0 To OffSet%
       ssMedian.MovePrevious
    Next i
    MedianMaker = ssMedian(fldName$)
 Else
    OffSet% = (RCount% / 2) - 2
    For i% = 0 To OffSet%
       ssMedian.MovePrevious
    Next i
    x% = ssMedian(fldName$)
    ssMedian.MovePrevious
    y% = ssMedian(fldName$)
    MedianMaker = (x% + y%) / 2
 End If
 ssMedian.Close
 MedianDB.Close

End Function

This works all nice and well, but it doesn’t really give me what I need.

My data set looks something like this:

OBJECT CODE VALUES
111111 1
111111 2
111112 1
111112 4
111112 4
111112 5
111113 9
111113 2
111113 4
111114 4
111115…etc

In order for me to get just the median for object code 111111, would I have to do that seperately? Or is there a way where I can cross-tab all these suckers together and get the median for each individual object code?

I’ve been on this for awhile now and I am uber frustrated, hehe. Any help would be appreciated. Thanks =)

2

Wouldn’t changing your recordset selection work?

Instead of


Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & "] FROM [" & tName & "] WHERE [" & fldName & "] IS NOT NULL ORDER BY [" & fldName & "];")

use


Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & "] FROM [" & tName & "] WHERE [" & fldName & "]" &  qryParameter &  "ORDER BY [" & fldName & "];")

then set your qryParameter variable to whatever you’re looking for:
= ‘111111’
or
between ‘111111’ and ‘111115’
or etc.

If you’re needing to get separate medians for each object, I’d set this as a function and then call the function for each object. Pass in your object code and set the query parameter to = objectCode.

3

I’m getting an error message at the qryParameter part.

am I missing quotations or anything of that sort?

4

Hiya rookie. I’m not entirely sure what you’re asking, so I’ve made a guess here. I’m also not sure how familiar you are with VBA, so I’ve assumed you don’t really do any coding. If I’ve gone the wrong direction, please try again, maybe with more detail (at what step it fails, the error message, etc.).
OK, somewhere in your database, values have been set for some variables. You need to add one more.

Look for the place that you’re calling this function. It will look something like:
Call MedianMaker(fldName, tName).
Those may not be the actual words in the parentheses. If not, then substitute whatever is in the parentheses for fldName and tName in my notes below.
The line of code I clipped is using these variables:
fldName
tName

So somewhere, it’s getting set that
fldName = “blah” where ‘blah’ is the name of your field
and
tName = “blah” where ‘blah’ is the name of your table

I replaced some text in the clipped section with another variable, qryParameter.

So somewhere, you need to set the value for that variable.
qryParameter = “blah” where ‘blah’ is what you want to query

So look for something like this:
Dim fldName as String
Dim tName as String

and add
Dim qryParameter as String

Then look for some code like:
fldName = “YourFieldName”
tName = “YourTableName”

and add
qryParameter = “= 111111”

That will turn this piece of code:
Set ssMedian = MedianDB.OpenRecordset(“SELECT [” & fldName & “] FROM [” & tName & “] WHERE [” & fldName & “]” & qryParameter & “ORDER BY [” & fldName & “];”)

into this when the variable values are substituted:
Set ssMedian = MedianDB.OpenRecordset(“SELECT [YourFieldName] FROM [YourTableName] WHERE [YourFieldName] = 111111 ORDER BY [YourFieldName];”)
If you’ve set up your code and variable correctly, the code posted previously should work.

Does that make sense?

5

And I’m an idiot, I realized as I was pulling onto the interstate. It’s been a long day…

You’ll also need to add that variable to the parameters you’re passing to the function.

Change
Function MedianMaker(tName As String, fldName As String) As Double
to
Function MedianMaker(tName As String, fldName As String, qryParameter As String) As Double

I was also wrong about calling the function. This is a function, not a sub, so it will be called differently.

It will look something like
MedianValue = MedianMaker(fldName, tName)
(not Call MedianValue(…) like I said before).

You’ll need to change that to
MedianValue = MedianMaker(fldName, tName, qryParameter)
If you’re calling this from a query, first change the parameters on your function as above. Then in your query, it should give you the prompts to add fldName,tName,qryParameter. Fill in the fieldname and tablename as you’ve been doing, and then put “=111111” for the qryParameter.
Sorry for the confusion!

6

Thanks for the quick replies…
actually my friend is the guy asking…
i’m not a coder at all and have no idea how to help him…

hehehe… i thought i put that right in the beginning of the post.

7

i tried this yesterday when u gave me the original code. i still have to debug at:

Set ssMedian = MedianDB.OpenRecordset(“SELECT [” & fldName & “] FROM [” & tName & “] WHERE [” & fldName & “]” & qryParameter & “ORDER BY [” & fldName & “];”)

Meh, I dunno, I’ve been on this stinkin’ database since Monday I think. It’s almost done, except for this one stupid median thing.

You know what else really stinks? There isn’t one specialist in the building that knows VBA all that well.

8

In my code library, I have a function for returning the median of an array, but it doesn’t sound like this is what you want. You might take a look at How to Use Code to Derive a Statistical Median or Reporting the Median Value of a Group of Records.

9

What’s the error message?

Note that on the WHERE statement if your criteria is a text field than it must be wrapped in single quotes.

You might be better off doing something like this:



Dim strSQL as string
strSQL = "SELECT [" & fldName & "] FROM [" & tName & "] WHERE [" & fldName & "]" & qryParameter & "ORDER BY [" & fldName & "];"
msgbox strSQL 'for debug
Set ssMedian = MedianDB.OpenRecordset(strSQL)

This way you can QC the SQL statement to make sure it’s formatted properly. Or paste in here and someone will tell you if it’s formatted properly.

10

Hmmm, it looks like this might have been what redtail23 was getting at. Sorry if I just repeated what he said.

11

From where are you calling the function - a query or another module?

What error message is it giving you?

Have you tried stepping through the code with debug on to check the variables?