Start Free Trial

asked on

Combining multiple filter criteria with combo boces in Access 2003

have 4 combo boxes and want them to work as filters for the form
Howeer the source has been modified to include an <All> which works fine.
I put an event against each combo to do the filtering as item A

But then I noticed that I can only filter by one combo which I'm sure will cause issues.
So I thought of combining the filters with ANDs, but that where I hit an issue.
I thought it would be kinda straightforward but it's more difficult than i thought.
I had thought of something like code b initially

However I jsut cant get it working.it just shows no records at all.
Any ideas where I mightbe going wrong?

Thank you

Thanks a lot

Code A
If [sitecbo].Value = "<All>" Then GoTo nofilter
Me.filter = "QryInvoices.[Site Name] = '" & Me.sitecbo.Value & "'"
Me.FilterOn = True
Exit Sub
nofilter:
Me.FilterOn = False
 
 
CODE B
Dim filterstring, reg, site, prod As String
If [cardcbo].Value <> "<All>" Then crd = "QryInvoices.[Card No] = " & Me.cardcbo.Value & "" Else crd = ""
If [ProdCbo].Value <> "<All>" Then prod = "QryInvoices.[Prod Desc] = '" & Me.ProdCbo.Value & "'" Else prod = ""
If [sitecbo].Value = "<All>" Then site = "QryInvoices.[Site Name] = '" & Me.sitecbo.Value & "'" Else site = ""
If [Regcbo].Value = "<All>" Then reg = "QryInvoices.[Vehicle Reg] = '" & Me.Regcbo.Value & "'" Else reg = ""
If crd = "" And prod = "" And site = "" And reg = "" Then GoTo nofilter
If crd <> "" Then filterstring =  crd
If prod <> "" Then filterstring= filterstring  & " AND " & prod
If site <> "" Then filterstring  = filterstring  & " AND " & site
If reg <> "" Then filterstring  = filterstring  & " AND " & reg
Me.filter = filterstring
Me.FilterOn = True
Exit Sub
nofilter:
Me.FilterOn = False

Open in new window

ASKER

I'M AN IDIOT!
Line14 & Line 15 are the problem.

Sorted it.

I'm not sure if this matters, but if crd="", your filterstring will begin with " AND..."

ASKER

It would indeed
I'm not sure if I'm going about this the right way?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Have so far got this working
If (Me![cardcbo].Value & vbNullString) <> "<All>" Then filterstring = filterstring & "QryInvoices.[Card No] = " & Me!cardcbo.Value & " AND "
If (Me![ProdCbo].Value & vbNullString) <> "<All>" Then filterstring = filterstring & "QryInvoices.[Prod Desc] = '" & Me!ProdCbo.Value & "' AND "
If (Me![sitecbo].Value & vbNullString) <> "<All>" Then filterstring = filterstring & "QryInvoices.[Site Name] = '" & Me.sitecbo.Value & "' AND "
If (Me![Regcbo].Value & vbNullString) <> "<All>" Then
filterstring = filterstring & "QryInvoices.[Vehicle Reg] = '" & Me.Regcbo.Value & "' AND "
If Len(filterstring) > 0 Then
filterstring = Left$(filterstring, Len(filterstring) - 5)
Me.filter = filterstring
Me.FilterOn = True
Else
Me.FilterOn = False
End If

Is there an easy way to filter out the comboboxes?
Normally I would do combo1 filtering combo2 then that filtering combo3 and then 4 etc
However it'spossible that any combination of them could be used in real life?

ASKER

I actually think I should be changing the results of the QUERY that the form is based on rather than just filtering what is displayed.
Anyone care to assist if I upload the file?
It's very rough at the moment and is purely for querying - all data is manually imported from a CSV file
I understand the data underneath is not normalized but it's not important to be in this case.
The querying of the data is all that people are interested in doing.

I can't think of an easier way of doing this. Might be an idea to initialise the value of filterstring to "" at the start of your code.

I'll take a look if you want to upload the file.

ASKER

OK. Cheers
The form is frmInvocies
Like I said - dont worry too much about the data structure
I'll probably sort that out later once I know what I actually need to show.
So much of it is junk.

BP-Invoices.mdb

I can't immediately see a benefit in changing the underlying query rather than just doing the filtering in the form. You could however tidy up the code a bit as follows:

Private Sub cardcbo_AfterUpdate()
    Call UpdateFilter
End Sub
 
Private Sub ProdCbo_AfterUpdate()
    Call UpdateFilter
End Sub
 
Private Sub Regcbo_AfterUpdate()
    Call UpdateFilter
End Sub
 
Private Sub sitecbo_AfterUpdate()
    Call UpdateFilter
End Sub
 
Private Sub UpdateFilter()
Dim filterstring As String
filterstring = ""
If (Me![cardcbo].Value & vbNullString) <> "<All>" Then filterstring = filterstring & "QryInvoices.[Card No] = " & Me!cardcbo.Value & " AND "
If (Me![ProdCbo].Value & vbNullString) <> "<All>" Then filterstring = filterstring & "QryInvoices.[Prod Desc] = '" & Me!ProdCbo.Value & "' AND "
If (Me![sitecbo].Value & vbNullString) <> "<All>" Then filterstring = filterstring & "QryInvoices.[Site Name] = '" & Me.sitecbo.Value & "' AND "
If (Me![Regcbo].Value & vbNullString) <> "<All>" Then filterstring = filterstring & "QryInvoices.[Vehicle Reg] = '" & Me.Regcbo.Value & "' AND "
If Len(filterstring) > 0 Then
    filterstring = Left$(filterstring, Len(filterstring) - 5)
    Me.filter = filterstring
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If
    Exit Sub
End Sub

Open in new window

ASKER

OK. - that makes sense right enough.
What does one do about the combo boxes not filtering out the rubbish?

Erm... what are you counting as 'rubbish'?

ASKER

Sorry. Items in the combos that shouldn't be displayed.
EG Select Card 540123,
There are only 2 registrations and one Fuel type in the filtered results
However both combo boxes for those fields are populated with the full list.
My problem is that any combination of the combos could be used...

Ah, I see what you mean. I've got to go out now, but I'll take another look at this evening.

ASKER

Cheers

ASKER

Hello again - any progress at all calpurnia?

Sorry about the delay - due to unforeseen circumstances, I didn't get home until stupidly late last night. I'm on the case now!

ASKER

Thanks very much :)
Seems only you have the know how (or interest?)

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Fantastic.

Is it easy to get the dates to work?
I've tried a between in the query
And a >=
And tried to filter the form too!

ASKER

I see what you've done!
It's very very quick and neat!

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Yes I see how that works also.
I never thought of usin gthe combo's to create a filter string.
It' sonly now it makes sense.

If I could get the dates working that'd be it, however > or < doesnt work
And between in the query doesn't work either. Most weird,.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Yes that sort of works. I think I'm losing the plot here as I'm not getting <all> rather than <any> and I cant see where thats coming from!
I'll award you the points but there's something totally bizarre going on!

Sorry, that was me - I changed <All> to <Any> as it seemed more logical.

ASKER

Ah.
Problem is I cant even see it i nthe code!
Maybe I should go with <Any>
The dats aren't working so I'm not sure what I'm going to do.
Thanks for all your help though

In what sense are the dates 'not working'?

> One word of caution ..
> "[convdate]>= #" & Me!fromcbo & "# AND [convdate]<= #" & Me!tocbo & "#"

True.
Remember that return values form combos are always strings. If coming from dates with no explicit prior formatting to strings, they will be string expression as to the settings of Windows for the user. Thus, in such cases, one safe method is to use DateValue (which uses the same setting to parse the string) to read them back to date values ... and then format these as string expressions in either the "reverse" US format or, preferred for SQL and a must for ADO, the ISO format:

"[convdate]>= #" & Format(DateValue(Me!fromcbo), "yyyy\/mm\/dd") & "# AND [convdate]<= #" & Format(DateValue(Me!tocbo), "yyyy\/mm\/dd") & "#"

/gustav

ASKER

I dont really want the date combos filtered at all.
The filtering works, but there is no easy way of changing date beyond the current range you've selected.
Say you chose a dat as the to date
There is no way to go back in and change the date to alater date
I'm not explaining myself at all well here am I.

Perhaps it is my fault for making them combo boxes?
Maybe they should be text fields that can be altered with a calendar control.
Should I post what I've got again?
Or do you want to email me? This psot is getting kinda long - seems a shame to drag it out on here forever and a day?

They're fins as combo boxes - you just don't want to update their RowSource. All you need to do is remove the lines relating to fromcbo and tocbo from Sub UpdateComboBoxes.

oops, that should've been *fine*, not *fins*.

Anyway, once you've done the above, I'd also recommend going in to the Properties for the 2 date combos and making sure that their Row Sources are set to the correct initial values:
SELECT DISTINCT [convdate] FROM qryInvoices ORDER BY qryInvoices.convdate;
SELECT DISTINCT [convdate] FROM qryInvoices ORDER BY qryInvoices.convdate DESC;

ASKER

An absolute star.
Thank you very much