I'm pretty new to Access, so any help I can get is appreciated. I'm using a form I've created to search a query. When I assign criteria to the query, it finds the records filtered by the first three criteria and simply ignores the fourth. This is just a test so far, but if I can't get 4 to work, 60-some isn't going to work either. My criteria format is [Forms]![Dredge_Data_Search]![txtCritDate] Or [Forms]![Dredge_Data_Search]![txtCritDate] Is Null. This works for my Date box, my File box and my Time box. When I use that as [Forms]![Dredge_Data_Search]![txtAmmonia1] Or [Forms]![Dredge_Data_Search]![txtAmmonia1] Is Null the search returns all records regardless of the number typed in my Ammonia1 search box.
Best to post that stuff on the page inside code tags.
Unfortunately, Access makes a right mess of SQL so even then this particular query would be hard to read. I think this is one where it would be easier to see a screen shot of the query builder, ensuring we can see all the WHERE conditions please.
Here are the screenshots of the criteria builder. Note that the additional lines in the "Or" section were put in by Access not by me. All my criteria was originally written on one line. Hopefully someone can find an answer for me.
You want loads of parameters, and you want to work out a SQL statement that will filter based on a match of all the parameters the user has selected a value for, and ignore the parameters that they have not selected a valaue for - correct?
That sounds about right. I posted the sql because it was the only way I could think of illustrating what was going on. I'll ultimately have 64 different criteria that can be searched, while ignoring any blank boxes. Every criteria will be numbers.
Ok. You can do this sort of thing with a handful of criteria but 64! You are getting into thousands and thousands of combinations.
You need to use dynamic SQL by VBA for this. Something like (pseudo code):
sql = "SELECT col1, col2 FROM myTable WHERE 1 = 1"
If Me.thisTextbox > "" THEN
sql = sql & " AND col1 = '" & Me.thisTextbox & "'"
If Me.anotherTextbox > "" THEN
sql = sql & " AND col2 = '" & Me.anotherTextbox & "'"
'... and so on
Once the correct SQL starts printing out, your VBA is good and you need to execute it.