Trying to apply a dynamic multi-criteria filter and can't find a good reference

[ScubaBart]

Trying to do something that's a couple steps outside my comfort zone and so far, I can't find a useful example or explanation that I can wrap my head around.

I have a continuous form open and I want to be able to filter the content based on three criteria. 1) The primary key, 2) The earliest date and 3) The latest date.



I figure I could do it with a conglomerate of many If Then Else operations but that seems like it will be clumsy at best.

I would also want to error check the two date entries to make sure the earliest does not occur after the latest.

I had a thought about setting up three Dim variables in that section of code and apply default values and then when a value changes for any given field, change the that appropriate variable and reapply the filter.

I would have to figure out how to reset the variables if the field value was removed, for instance, the Primary Key value could be set to null to bring back all entries just within the date range(s).

I will start playing with stuff later but if there are some good examples anyone can point me to, that would be great.

TIA

[isladogs]

Try my example Multiple Group & Filter apps

[June7]

Can't avoid If Then Else coding for this (unless you like queries with parameters).

Here is another example http://allenbrowne.com/ser-62.html

[Vstar920AZ♣]

the Allenbrowne example above is much slicker that this, but you can go the recordsource route with SQL.
dim sSQL as string
sSQL = "SELECT * FROM TABLE WHERE PK=Me.cbo_PrimaryKey and DateInTbl >= Me.txb_StartDate and DateInTbl <= Me.txb_EndDate"
'If you are uncomfortable with SQL, just create a query using the Query Editor and copy the SQL over. Adapt for criteria as needed.
me.recordsoucre = sSQL
me.requery 'May not be needed

You will need a button or function to return to all records i.e. "SELECT * FROM TABLE"
me.recordsoucre = sSQL

[ScubaBart]

Guys, I'm looking at the examples and getting the date filters to work isn't working. Not sure of my problem.

I have reduced the actual code to apply just a single date filter but when applied, it isn't filtering as it should.

Will clean things up a bit and post probably tomorrow current file with highlights of what and where in the app I'm trying to implement this.

[Welshgasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.
Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Exaample:
tt="Eg'g"
? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")