# Some help with MS Access and a module

**URL:** <https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600>\
**Category:** Factual Questions\
**Created:** [December 7, 2004, 7:20pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600 "2004-12-07T19:20:52Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![rookie523](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@rookie523](https://boards.straightdope.com/u/rookie523)\
**Post date:** [December 7, 2004, 7:20pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/1 "2004-12-07T19:20:52Z")

</div>

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% &lt;&gt; 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 =)

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [December 7, 2004, 8:10pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/2 "2004-12-07T20:10:38Z")

</div>

Wouldn’t changing your recordset selection work?

Instead of

```auto

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

```

use

```auto

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_.

---

<div class="post-metadata">

**Author:** ![rookie523](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@rookie523](https://boards.straightdope.com/u/rookie523)\
**Post date:** [December 8, 2004, 10:43pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/3 "2004-12-08T22:43:43Z")

</div>

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

am I missing quotations or anything of that sort?

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [December 9, 2004, 12:23am UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/4 "2004-12-09T00:23:04Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [December 9, 2004, 1:06am UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/5 "2004-12-09T01:06:43Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![rookie523](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@rookie523](https://boards.straightdope.com/u/rookie523)\
**Post date:** [December 9, 2004, 1:24am UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/6 "2004-12-09T01:24:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rookie523](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@rookie523](https://boards.straightdope.com/u/rookie523)\
**Post date:** [December 9, 2004, 6:19pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/7 "2004-12-09T18:19:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![PoorYorick](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@PoorYorick](https://boards.straightdope.com/u/PoorYorick)\
**Post date:** [December 9, 2004, 6:36pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/8 "2004-12-09T18:36:21Z")

</div>

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](http://support.microsoft.com/?kbid=95918) or [Reporting the Median Value of a Group of Records](http://support.microsoft.com/?kbid=103271).

---

<div class="post-metadata">

**Author:** ![larsenmtl](https://avatars.discourse-cdn.com/v4/letter/l/d78d45/32.png) [@larsenmtl](https://boards.straightdope.com/u/larsenmtl)\
**Post date:** [December 9, 2004, 6:38pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/9 "2004-12-09T18:38:09Z")

</div>

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:

```auto

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.

---

<div class="post-metadata">

**Author:** ![PoorYorick](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@PoorYorick](https://boards.straightdope.com/u/PoorYorick)\
**Post date:** [December 9, 2004, 6:41pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/10 "2004-12-09T18:41:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [December 9, 2004, 7:18pm UTC](https://boards.straightdope.com/t/some-help-with-ms-access-and-a-module/278600/11 "2004-12-09T19:18:47Z")

</div>

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?