Filter query between dates

[hockeylvr]

Would appreciate some keywords or a point to a thread that could help me with this. I know the answer must be here somewhere but I've exhausted all ideas. I have a query with calculations in a column [Savings]. I have a form with two text boxes for dates, "txtStartDate" and "txtEndDate". I just want to click on a command button and have the query open to show only those records between the two dates. I've seen that I cannot use RunSQL and OpenQuery does not allow WHERE statements. I've tried the below with no results.

Dim strSavings As String, Criteria As String

Criteria = "[monthdate] Between #" & Me.txtDate & "# AND #" & Me.txtEndDate & "# "
strSavings = DLookup("[monthdate]", "[qryEngSavings]", Criteria)

Thanks!

 

[jadams0173]

in the query itselft put a reference to your forms text boxes.

So for the start date in your query's criteria put

forms!yourformname!txtstartdate

same thing for end date.

You may also need to the format function, to make sure you are comparing dates in the same format. mm/dd/yyyy to mm/dd/yyyy and not mm/dd/yyyy to dd/mm/yyyy

format(forms!yourformname!txtstartdate,"mm/dd/yyyy")

HTH

I tried to have patience but it took to long! -DW

 

[Zion7]

jadams0173, if I may just offer a small correction,

you would actually, in the query pane, in the criteria section, as jadams said, BUT under [monthdate] put

Between forms!yourformname!txtstartdate And forms!yourformname!txtEnddate

...the form must ALWAYS be open though, to run the query
(which may very well be the case)

 

[jadams0173]

good point. thanks Zion7!

I tried to have patience but it took to long! -DW

 

[hockeylvr]

Thanks to both of you - it works perfectly, keep forgetting that I can put that in a query!

Toni