VBA Query with Query Criteria

[BigNate]

Hello Everyone,

I'm trying to use VBA to run a query with the query criteria being pulled from the values contained in textboxes on the same form. I know how to run the query with no criteria:

Private Sub ResultsBtn_Click()
DoCmd.OpenQuery ("Results")
End Sub

And I know the concept of a wildcard (*) in a query, but I'm not sure how to combine these concepts. Using the picture shown below for example, I am trying to click the button that says "Report" called "ResultsBtn" to query all rows in a table. Using the attached image as an example, I want to try to add to my code above so that I would run the "Results" query to show all items that...

• have 10 in the partNumber somewhere in the input textbox to the right called "PartInput"
• Has a date between the dates given in the two input textboxes called "FromDate" and "ToDate"
• (I think I can figure out the other criteria shown in the screenshot if I get assistance on the above two points)

**If any of the boxes on the right are blank, I want to ignore them and only filter based on other criteria.

Thanks in advance to whomever takes the time to help me. I used to know how to do this, but haven't looked at Access/VBA for several years!

 

[petertenthije]

In the query, in the field PartInPut type:

Like "*"& [forms]![ResultsBtn]![PartInput] & "* " or [forms]![ResultsBtn]![PartInput] is null

In the query, in the field Date type:

>= [forms]![ResultsBtn]![FromData] or [forms]![ResultsBtn]![FromData] is null AND <= [forms]![ResultsBtn]![ToData] or [forms]![ResultsBtn]![ToData]  is null

 

[BigNate]

Thank you very much for your help! For some reason, the date range filter still isn't working. I tried to modify it a bit to troubleshoot myself, but can't see the issue. Here is the line with the issue (note I changed the name of "ResultsBtn" to "HTS"):

>=[forms]![HTS]![FromData] Or [forms]![HTS]![FromData] Is Null And <=[forms]![HTS]![ToData] Or [forms]![HTS]![ToData] Is Null

Any ideas in what I'm not seeing?

 

[Micron]

Grouping is often a good idea when stringing AND, OR, Is Null, etc. Consider
>= A Or A Is Null And B <= C Or C Is Null
Is it
(>= A Or A Is Null) And B <= C Or C Is Null
>= A Or (A Is Null And B <= C) Or C Is Null
(>= A Or A Is Null) And (B <= C) Or C Is Null
(>= A Or A Is Null) And (B <= C Or C Is Null)
or who knows what? There are rules about the order used if not specified and all I can remember about them is they're quite complicated.

Also, this has no qualifier
And <=[forms]![HTS]![ToData] <<- what is <= this?