data type mismatch on a form filter?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brook - 21 Apr 2005 17:39 GMT

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

Reply to this Message

Graham Mandeno - 22 Apr 2005 01:43 GMT

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

Signature

Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

>I use a form to filter a rptinventory.
>
[quoted text clipped - 20 lines]
>
> Brook

Reply to this Message