Tricky filter issue...

[chin chin]

...well for me anyway. I'm a beginner using Access 2007 and have a problem with a filter. I've made filters on other forms without issue but the difference is that on this form the information displayed on the fields to filter (and the text the user will input) are from another table - they are ID numbers on this table. This is an example of the code I've tried but the obvious fault is that it will only work if the whole name is written perfectly. I need for the user to be able to use *s etc to filter partial names:

If Combo40 = "Business Name" Then
Me.Filter = "BusNum Like """ & DLookup("[BusNum]", "Suppliers", "[BusName] = " & Me.Text42) & """"
Me.FilterOn = True
Me.Requery
End If

I'm stumped for ways around this. Was wondering if I could actually filter the other table from the form?

Thanks a lot for any suggestions.

Chin chin

 

[boblarson]

Okay, first up -

If you want to use the word LIKE so the user doesn't have to key in the exact information, you will need to use wildcards on whichever side, or both, that you want it available.

If you just want the value that is equal to the text entry, then don't use like but use =.

Next, You are looking up a number so you don't want it surrounded by quotes.

And last you will need to use an NZ function in case the value searched for is null.

So, your line should look like this -

If wanting partial text matches:

Me.Filter = "BusNum Like [B][COLOR=red]*" & Nz([/COLOR][/B]DLookup("[BusNum]", "Suppliers", "[BusName] = " & Me.Text42)[B][COLOR=red],0) & "*"[/COLOR][/B]

If wanting an exact match:

Me.Filter = "BusNum = [B][COLOR=red]" & Nz([/COLOR][/B]DLookup("[BusNum]", "Suppliers", "[BusName] = " & Me.Text42)[B][COLOR=red],0)[/COLOR][/B]
[B][COLOR=#ff0000]

[/COLOR][/B]

 

[boblarson]

Oh, and get rid of the Requery, or if you really want it, do it BEFORE applying the filter.

 

[chin chin]

Thanks for the advice, some good tips there I will be using. I'm not sure I explained the main issue very well though - They will be inputting a name or partial name, but from that I need to look up the corresponding number (hence the Dlookup) and filter based on the number.

In short, the wildcards are not in the place I need for them to work - I need them round the Me.Text42, which I can't get to work.

Thanks again,

Chin chin

 

[boblarson]

Oh, yeah that would be this instead (sorry about that):

Me.Filter = "BusNum = [B][COLOR=red]" & Nz([/COLOR][/B]DLookup("[BusNum]", "Suppliers", "[BusName] [B][COLOR=blue]Like " & Chr(34) & "*" &[/COLOR][/B] Me.Text42 [COLOR=blue][B]& "*" & Chr(34)[/B][/COLOR])[COLOR=red][B],0) [/B][/COLOR]
[COLOR=black]

[/COLOR]
I used Chr(34) which is a double quote instead of using the single quote method for BusName because it might have a single quote in the business name and so this way it doesn't create a problem.

 

[chin chin]

Thanks a lot, I'll try get that working in the morning and let you know how I get on.

chin chin

 

[chin chin]

Works great - thanks Bob. There's still one minor issue that I don't know if there'll be a way around - if the partial name matches more than one business then only the records of one of them are shown.

Chin chin.

 

[boblarson]

Works great - thanks Bob. There's still one minor issue that I don't know if there'll be a way around - if the partial name matches more than one business then only the records of one of them are shown.

Chin chin.

A Dlookup will only return one value. If you want to be able to have multiples returned you would need to use another method. Personally I would just do a DCount first and if it comes back >1 then prompt the user that they need to narrow down their search - you might use a recordset to provide them with the multiple values in a message box or another form so they could select the one they want.

 

[vbaInet]

Just to add that it might be an idea to build your sql in code instead if you want to Filter Like of Like. First of all do it in the query design grid pull the two tables together and join them by BusinessID. In the criteria you search using LIKE under the Business Name field of the Suppliers table. Use the resulting sql as the record source of your form. You may need a DISTINCT clause in there too.

I would opt for counting the Record Source and if it results in 0 then prompt.