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

[C Muth]

Using a query, I pieced together a date from a table: DATE: DateValue([Month] & "/" & [Day] & "/" & [Year])



I then wrote a query to pull a range of dates: >=[Enter Start Date] And <=[Enter End Date]

The results are what I would expect for Month + Day perspective, however, the year seems to be ignored. Example: for the range 11/12/19 thru 11/14/19, I am receiving 11/12/17, 11/13/17, 11/12/18, 11/12/19, 11/13/19, etc.

Any help would be appreciated.

[ranman256]

use yyyy. It should work.

[C Muth]

thanks. tried yyyy - same results

[JoeM]

Note that MONTH, DAY, and YEAR are reserved words - the names of functions, and should not be used as the names of your variables.
Using reserved words for the names of variables could cause unexpected results and/or errors.

Could you post a small sampling of what your data looks like, and the SQL code of your query?

[ranman256]

I do this too and it works. So Query1 select *,

DateValue([Month] & "/" & [Day] & "/" & [Year]) as MyDate

Q2:
select * from Q1 where MyDate between 1/1/19 and 3/1/19

[C Muth]

Thanks both. I studied a subset of the results and found the following:

- when I select the range >=11/12/2019 and <=11/14/2019, Nov 12, Nov 13 & Nov 14 is included in the results
- when I select the range >=11/12/19 and <=11/14/19, Nov 12, Nov 13, however no Nov 14 included in the results.

Other subsets yielded different results.

Trying without use of reserved words now.

[JoeM]

DateValue([Month] & "/" & [Day] & "/" & [Year]) as MyDate

Are you just trying to get the current date?
If so, then why not just use:

Date() as MyDate

Otherwise, I stand by my previous statement.

[C Muth]

Hi, changing away from reserved words did not yield different results. Below is SQL for the two queries.

Q1

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

Q2

SELECT [Log Data].TDATE, [Log Data].[TYPE], [Log Data].USERID, [Log Data].TDATETIME
FROM [Log Data]
WHERE ((([Log Data].TDATE)>=[Enter Start Date] And ([Log Data].TDATE)<=[Enter End Date]));

Thanks very much!

[C Muth]

Joe M, thanks. Q2 is set to allow for the user to enter a range of dates. It may be the current date; it may be a range of historical dates. See previous for SQL / value any insights you may have.
Thanks

[Micron]

When you use DateValue and provide date separators, Windows Regional Settings comes in to play. So if your setting is mm/dd/yyyy and you coerce the result of DateValue to a double data type as in
cdbl(datevalue("11/12/19")) you get 43781
Change to UK settings and you get 40896

Why not use CDate?
UK - cdate("11/12/19") gives 2011-12-19
Canada - cdate("11/12/19") gives 11/12/2019

Not sure if any of that will help. Some of the date examples given could be interpreted either way.

[isladogs]

Just to add to the previous answers, here are some results from the immediate window.
As you can see Access tries to 'helpfully' interpret dates and doesn't always get it right where there is ambiguity
NOTE I'm UK based so dates are dd/mm/yyyy

First look at supposedly non ambiguous dates

?(#11/14/19#)
14/11/2019 
?(#14/11/19#)
19/11/2014 

However if you use a 4 digit year, all is OK

?(#11/14/2019#)
14/11/2019 
?(#14/11/2019#)
14/11/2019 

Also compare the output with an ambiguous date

?(#11/7/19#)
07/11/2019 
?(#11/7/2019#)
07/11/2019 
?(#7/11/19#)
11/07/2019 
?(#7/11/2019#)
11/07/2019 

So in sql statements, always use date format mm/dd/yyyy to ensure you get expected results

[C Muth]

Thanks. I have changed

DateValue([TMonth] & "/" & [TDay] & "/" & [TYear])

To

CDate([TMonth] & "/" & [TDay] & "/" & [TYear])

No change to results.


I believe Q1, which simply assembles the date, is working properly.
I believe the issue to be in Q2.

When I hard code the following in the criteria row

>=#11/12/2019# And <=#11/14/2019#

I get the desired results, however when I convert the hard code to >=[Enter Start Date] And <=[Enter End Date] and enter MM/DD/YYYY when prompted, I receive results which include days between Nov 12 and Nov 14, however across multiple years. Is there possibly an issue with >=[Enter Start Date] And <=[Enter End Date]?

Any thoughts are much appreciated.

[Micron]

when you use #11/12/2019# it is defined as a proper date. When you enter 11/12/2019 it is a string.
By any chance are the table fields text type?
This is why I suggested CDate but the input has to be passed as a delimited "" string.
I suspect that the root of the problem is the table; i.e. a date split into 3 text parts.

[C Muth]

hi and thanks.

the table is via ODBC link and the DATE_TIME field I chopped up and re-assembled is "short text".

[Micron]

Maybe explain why, because I suspect you may think it made sense at the time (or still does) yet we probably disagree. It's easier to work with numbers as text as opposed to the other way around. You could also concatenate #'s into your expression. That will probably solve the problem, but I think it's a problem created by storing date or date parts as text.