Desperately need help with Select Date criteria in Form:

[Stylianos]

Hello, I am still very new to Access and have an issue that surfaced after importing data from another database. I have 2 criteria to a query to add “Search by Date” and “Search by Date Range” with the control on a form “frmProductionReports”. The Search by Date Range works great but the Search by Date yields no results. When I set up the tables and queries, I did not specify any format criteria but the data I imported to the tables was set to “general” format, thereby giving me Date and Time. The text buttons on the Form were originally set to “Short Date” which worked fine with the dummy data I added during the build stage. Now that I added official data… the problem surfaced! Is there a method to either fix or work around this issue?
Form (frmProductionReports)


Between [Forms]![frmProductionReports]![txtStrtDateSum] And [Forms]![frmProductionReports]![txtEndDateSum] Or [Forms]![frmProductionReports]![txtDateSum]
To run the query, I have set a command button (ProductionResultsSummary) with a macro “On Click” as follows:







Query: qryUnionQryProdDataSummary (pulled from a union query)

[June7]

Really need to see data. If you want to provide db for analysis, follow instructions at bottom of my post.

[Stylianos]

Hello June7! I will check with my manager if I can release the db...
Thank you!

[ArviLaanemets]

It looks like you have date AND time in field. Instead equals try

Form (frmProductionReports)>= [Forms]![frmProductionReports]![txtDateSum] AND < [Forms]![frmProductionReports]![txtDateSum]+1

And probably you have some records left out when you use BETWEEN too. Instead use

(frmProductionReports) >= [Forms]![frmProductionReports]![txtStrtDateSum] And < [Forms]![frmProductionReports]![txtEndDateSum]+1

[Stylianos]

It looks like you have date AND time in field. Instead equals try

Form (frmProductionReports)>= [Forms]![frmProductionReports]![txtDateSum] AND < [Forms]![frmProductionReports]![txtDateSum]+1

And probably you have some records left out when you use BETWEEN too. Instead use

(frmProductionReports) >= [Forms]![frmProductionReports]![txtStrtDateSum] And < [Forms]![frmProductionReports]![txtEndDateSum]+1

Hello ArvilLaanemets!
It was a good start, I undated the query criteria. run the macro to filter the data and create the report and was able to pull all data regardless of format. The problem though is that it pulled ALL the data for both Select Date and Select by Date Range options. I noticed that it split the criteria into 2 separate fields when I saved but wasn't sure why. (see attached) Did I go wrong somewhere?

[June7]

WHERE CompletionDate BETWEEN [Forms]![frmProductionReports]![txtStrtDateSum] AND [Forms]![frmProductionReports]![txtEndDateSum] +1

or

WHERE CompletionDate >=[Forms]![frmProductionReports]![txtStrtDateSum] AND CompletionDate < [Forms]![frmProductionReports]![txtEndDateSum] +1

[Stylianos]

Thank you very much ArvilLaanemets and June7~ I Tweeked it a bit but now all cmd functions are working! Much appreciated