Query Prompting User to Enter Date Range

[C Muth]

I am starting with a linked OBDC table with a date formatted as Short Text. Example: 20191112 07:22:32.54


Q1 breaks down and rebuilds the the date and time

SELECT log.DATE_TIME, Mid([DATE_TIME],5,2) AS TMONTH, Mid([DATE_TIME],7,2) AS TDAY, Left([DATE_TIME],4) AS TYEAR, CDate([TMonth] & "/" & [TDay] & "/" & [TYear]) AS TDATE, Mid([DATE_TIME],10,5) AS TTIME, [TDate] & " " & [TTime] AS TDATETIME
FROM log

My goal for Q2 is to have the user prompted to enter a range of dates (which have have done successfully in other access databases with no issue)

The Criteria I entered is: >="#" And [Enter Start Date] And "#" And <="#" And [Enter End Date] And "#"


The query runs, however returns no results.
When I hardcode a date range in the Criteria formatted as follows: >=#11/12/2019# And <=#11/14/2019#, it returns the desired results.

Question: what can I do to fix the Criteria to allow the user to enter a range of dates and receive the desired results?

Any guidance would be appreciated.

[June7]

This is in a query object - don't need concatenation nor # characters with input prompt. Try BETWEEN AND instead of >=, <=

BETWEEN [Enter Start Date] AND [Enter End Date]

I never use dynamic parameter popup input because cannot validate user entry. Better to reference controls on form for user input.

[C Muth]

thanks. when I use "BETWEEN" Q2 returns Nov 12, Nov 13 and Nov 14 data, however for years 2016, 2017, 2018, + 2019. Hence the use of the #. The unexpected results were the subject of another thread I started. Any thoughts appreciated.

Query on Date (M/D/YY) field seems to be ignoring YY in Access

Noted re: user entry via a form. For now only a couple users, will enhance if and as we roll out to additional users.

[June7]

Sorry, I am using only Access tables so I am not 'rebuilding' date. So I just tested that and I see the behavior you describe. Even with the symbol operators, not just BETWEEN AND.

Aside from not using dynamic parameters in query, don't have a solution.

[isladogs]

In the end changing the datatype from short text to datetime would solve a lot of problems and save you a lot of time