type mismatch on recordcount

[lartigue64]

Hi,
I have a function that calculates the median of a selection of values. This function worked fine in November when i ran the report, but in December it didn't run. It seems that i suddenly have a type mismatch error in my Median function when i try to get the number of records selected. I am wondering if I could be possibly missing a object library or something like that? I am using Access 2003.

These are the references i have checked when i go to tools/references in VBA:
* Visual Basic for Applications
* Microsoft Access 11.0 Object Library
* Microsoft DAO 3.6 Object Library
* OLE Automation

This is what my code looks like:

Public Function Median(TableName As String, FieldName As String, Condition As String)

Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer
Dim strSQL As String
Dim x As Variant

If Condition <> "" Then
strSQL = strSQL & " WHERE " & Condition
End If

Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT " & FieldName & strSQL & " ORDER BY " & FieldName, _ dbOpenDynaset)

RCount% = ssMedian.RecordCount

If n > 1 Then
ssMedian.Move n \ 2 ' move at 1/2 (round down) the way
x = ssMedian.Fields(FieldName).Value

ssMedian.Move n Mod 2
Median = 0.5 * (x + ssMedian.Fields(FieldName).Value)
Else
If n = 1 Then
Median = ssMedian.Fields(FieldName).Value
Else
Median = 0
End If
End If

ssMedian.Close

End Function

I've also tried using a select count(*) statement to get the count, but that didn't seem to work either. Any ideas?

 

[Remou]

There seems to be several problems with the code, for example, the SQL statement does not include a table name.

Public Function Median(TableName As String, FieldName As String, Condition As String)

Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer
Dim strSQL As String
Dim x As Variant

If Condition <> "" Then
    strSQL = strSQL & " WHERE " & Condition
End If

Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & FieldName & "] FROM " _
    & TableName & strSQL & " ORDER BY [" & FieldName & "]", dbOpenDynaset)

n = ssMedian.RecordCount

ssMedian.Move (n \ 2) - 1

If n Mod 2 = 1 Then 'odd number of elements
    Median = ssMedian.Fields(FieldName).Value
Else                'even number of elements
    x = ssMedian.Fields(FieldName).Value
    ssMedian.MoveNext
    Median = 0.5 * (x + ssMedian.Fields(FieldName).Value)
End If

ssMedian.Close
Set ssMedian = Nothing
Set MedianDB = Nothing
End Function

 

[lartigue64]

ok, that was actually my error in copying the code here. i forgot to copy in that line with the 'from tablename'.
your median calculation is slightly different but comes to the same thing.
for some reason my access is no longer giving me error messages. the macro just stops. so i've been putting in msgboxes to track where i'm at. everything is fine through the 'set' commands. its when i try to get the count that it stops.

 

[Remou]

Are any of your libraries maked MISSING? Have you tried Compact & Repair? Can you get a recordcount on other recordsets?

 

[lartigue64]

I can't get a recordcount on other recordsets if i substitute for example "Select * From Vacants2007" in my openrecordset statement.
Where do you find compact and repair?
Where would the libraries be marked missing? In the references window? If so, it doesn't look like it...

 

[Remou]

I can't get a recordcount on other recordsets if i substitute for example "Select * From Vacants2007" in my openrecordset statement.
Another thought occurs ... have you got Option Explicit at the top of the module? If not, add it, then compile. Try this before anything else. Double check that the recordset is defined as DAO.Recordset

Where do you find compact and repair?
Tools->Database Utilities
in the main window.

Where would the libraries be marked missing? In the references window?
Yes.

 

[lartigue64]

I added Option Explicit.
the recordset is defined as a DAO.Recordset
I ran the compact and repair.
Same thing as before. It seems to stop after the set ssmedian line.
(I did add an error to see what it would do, I added a variable that wasn't defined, just a random thing, and it did at least give me the warning when i compiled now.)

 

[Remou]

Perhaps you could attach a zipped copy, if it is not very large or very private?

 

[lartigue64]

ok, i created a new mdb, with just the tables and form that calls the code in question.
the form called frmUpdate fills in the table tblVacants with the information from Vacants2007, calculating medians and giving counts for each time period.
i really really appreciate your help!! (and any other suggestions you might have while looking at this, i'm not a big access programmer so i can use any help you have to offer!)

 

[lartigue64]

interesting... i opened my link to make sure it worked, and ran it, and it went through the median calculations, and i got a type mismatch later on... so maybe the problem isn't in my median function?
anyways let me know what you see.

 

[Remou]

The problem is not with the median code, although you do not seem to have made the changes I suggested, which means that n will always equal zero, for one thing. The problem is in the SQL string that follows the median code. Here is an attempt to deduce what you wanted:

[tt]strSQL = "INSERT INTO tblVacants (AreaName,TimePeriod,Year," _
& "ThePeriod,DateForm,CountOfId,MedSalesPrice," _
& "MedPriceSqFt) Values ('" _
& areas(i) & "', '" & periods(j) & "', 2007, '" _
& periods(j) & " 2007', '00-00-00'," _
& DCount("ID", "Vacants2007") & ", " _
& medianSales & ", " & medianPriceSF & ")"[/tt]

You are inserting values, so you need Value rather than select, which means that the later where statement:

[tt] 'whereStr = " WHERE " + whereStr[/tt]

Has no place in the SQL. Nor does this:

[tt] 'strSQL = strSQL + str(medianSales) + " AS MedSalesPrice, " + str(medianPriceSF) + " AS MedPriceSqFt FROM Vacants2007" + whereStr + ";"[/tt]

The ampersand is used for string concatenation, not the plus sign.

It is a good idea to build the SQL in the query design window when you are starting out and cut and paste into code, where you can tidy up.

You will need to re-examine your code in the light of the above and make a number of changes.

 

[lartigue64]

ok, thanks for your help. i'll see what i can tidy up tonight and let you know how things go.
thanks again for all your help!!

 

[lartigue64]

ok, so i applied all the changes that you suggested, by the way your median function doesn't work if there is only one record returned. and with the dcount function i was able to use the insert into with values(...) so that simplifies this a lot. and things seem to be working fine. thanks a lot for all your help. as you can see i'm not an access wiz and am trying to figure this out as i go...