Multiple fields query problem on dfferent Access version

[Paolo]

Hi,

I have a 8 column table column and a form with 8 text combo
boxes, one combo for each column. I want to filter my table
on the records that contains what the user type in the
combo boxes. These are the feautures:

1. I don't require that each combo matches exactly the
field in the respective column, but just any part of it.

2. If the user doesn't fill a combo it means that the combo
matches any value.

3 I want get back only the records that match all the 8
values in the combo boxes

This is what I wrote for the first and second column.

SELECT myTable.*, myTable.A, myTable.B

FROM myTable
WHERE ( (((myTable.A) Like "*" & Forms!myForm!myComboA &
"*") Or IsNull(Forms!myForm!myComboA) Or
((Forms!myForm!myComboA) Like "")) And
(((myTable.B) Like "*" & Forms!myForm!myComboB & "*") Or
IsNull(Forms!myForm!myComboB) Or ((Forms!myForm!myComboB)
Like "")));


This query seems to work good on Xp wit Access 2002, but it
doesn't work on Access 2000 on Win2000 an Access 97 on Win98.

There is something wrong in the query? There is something
difference about SQL on different Access version?

Thanks so much,

Paolo

 

[Allen Browne]

This example assumes you have added 8 unbound combos to your form. It
filters on the combination of any that have some value in them. It assumes
all 8 are bound to Text type fields.

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.MyComboA) Then
strWhere = strWhere & "(A Like ""*" & Me.MyComboA & "*"") AND "
End If

If Not IsNull(Me.MyComboB) Then
strWhere = strWhere & "(B Like ""*" & Me.MyComboB & "*"") AND "
End If

'etc for other combos

lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen <=0 Then
Me.FilterOn = False 'Show all records.
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = False
End If

 

[Paolo]

Thanks Allen,

Unfortunally, I'm not expert! I didn't understand exactly how to
insert your code into my application. I put it as the button event
routine. when I push the button I get a run time error (2465) telling
me that there is non reference to the filed Filter.

I'll study about my problem. I'd like to ask you if you found
any error on my query and if it's true that it may not work on
different version.
On Access97 it doesn't filter while in Access2002 it's ok.

Thanks again!

Paolo

 

[Allen Browne]

Theoretically, your approach could work, provided all the fields are Text
type fields, and the bound column of the combos provide the right text, etc.
The approach I suggested should be much more efficient as it only uses the
combos that actually have a value instead of generating 16 phrases in the
WHERE clause.

Filter is a property of the form. You literally include the text:
Me.Filter
just like that.