data type mismatch on a form filter?

[Guest]

I use a form to filter a rptinventory.

I have 5 filter comboboxs and the only one giving me trouble is the cboprice.

here is my setup for my formats for my fields in my tbl, frm, and rpt

tblinventory
pricepersqfoot -- > Currency 2 decimal places

rptinventory
pricepersqfoot -- > Currency 2 decimal places

frmfilter
cboprice
-- > currency 2 decimal places...
here is my lookup:
SELECT DISTINCT tblinventory.PricePerSqFoot FROM tblinventory;

Any ideas? suggestions?

I don't know what else to look for?

Brook

 

[Graham Mandeno]

Hi Brook

You haven't given many clues, but I'm guessing that this question relates to
the other thread on filtering with combo boxes.

If so, then the problem is that you are enclosing the value in quotes -
Chr(34) - in your filter string. You need to do this for text fields, but
not for numeric fields.

The easiest (though probably not the cleverest) way to fix this is to put in
a special case for your numeric field:

Instead of this:
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

.... you should first check intCounter to see if you are on the
PricePerSqFoot combobox.

Say the combobox in question is "Filter3"

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = "
If intCounter = 3 Then
' add the value without quotes
strSQL = strSQL & Me("Filter" & intCounter) & " And "
Else
' add the value WITH quotes
strSQL = strSQL & Chr(34) & Me("Filter" & intCounter) & Chr(34) & "
And "
End If