Query partial match (1 Viewer)

andysh

New member
Local time
Today, 11:36
Joined
Nov 26, 2020
Messages
5
Hi,

I have the below where clause to return data from a date range.

WHERE ((LEFT(tbl_Triage.TIMESTAMP, 10) >=[Forms]![frm_SearchTransactions]![txtDateFrom]) OR ([Forms]![frm_SearchTransactions]![txtDateFrom] Is Null))
AND ((LEFT(tbl_Triage.TIMESTAMP, 10) <=[Forms]![frm_SearchTransactions]![txtDateTo]) OR ([Forms]![frm_SearchTransactions]![txtDateTo] Is Null))

The reason for the use of LEFT is that the TIMESTAMP format is 'dd/mm/yyyy hh:mm' whereas the txtDateFrom and txtDateTo format is 'dd/mm/yyyy'.

The issue I have is that when I search a range from e.g. 16/02/2021 to 23/02/2021 it returns the 16th to 23rd of every month not just February.

Any suggestions as to what is causing this or how I can rectify it would be greatly appreciated. I'm guessing but is it something to do with the LEFT function not recognising special characters?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:36
Joined
Oct 29, 2018
Messages
21,357
Hi. You're probably experiencing the issue with International Dates in Access. Check out this article to see if it might help figure it out.

 

andysh

New member
Local time
Today, 11:36
Joined
Nov 26, 2020
Messages
5
Hi DBguy,

I don't think it's an international date issue, more that the query returns results for the days entered but ignores the month and year.
To go back to the example if I enter 16/02/2021 to 23/02/2021 I get results for 16/12/2020 to 23/12/2020 and 16/01/2021 to 23/01/2021 and 16/02/2021 to 23/02/2021

It only seems to be reading first 2 characters instead of the first 10 as set out in the LEFT function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:36
Joined
Oct 29, 2018
Messages
21,357
Hi DBguy,

I don't think it's an international date issue, more that the query returns results for the days entered but ignores the month and year.
To go back to the example if I enter 16/02/2021 to 23/02/2021 I get results for 16/12/2020 to 23/12/2020 and 16/01/2021 to 23/01/2021 and 16/02/2021 to 23/02/2021

It only seems to be reading first 2 characters instead of the first 10 as set out in the LEFT function
Okay, just curious though, did you read the article?
 

andysh

New member
Local time
Today, 11:36
Joined
Nov 26, 2020
Messages
5
I did read the article but didn't see anything in there which would relate to my particular problem (apart from the bit about unbound fields but I made sure the short date format was in there)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:36
Joined
May 21, 2018
Messages
8,463
You really should read the article. If you are worried about how your dates are formated, then you have not read the article. Access could care less about your date format, it is not relevant.
 

Isaac

Lifelong Learner
Local time
Today, 04:36
Joined
Mar 14, 2017
Messages
8,738
To piggy back on what the others have said, and possibly provide another fun & interesting way to gain more clarity on your end, try running this query:

Code:
select tbl_triage.timestamp as [Actual Value], left(cstr(tbl_triage.timestamp),10) as [Left 10 CSTR Value] from tbl_triage
(untested aircode, but if I made a syntax mistake hopefully you can see the intent and correct, and run).
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:36
Joined
Sep 21, 2011
Messages
14,038
See if you can understand this quick test? All in the Immediate window and I'm in the UK?
Code:
tt = now
? left(tt,10)
26/02/2021

tt1=#01/02/2021#
? tt-tt1
 55.9331365740727 

tt1=#02/01/2021#
? tt-tt1
 25.9331365740727
 

Isaac

Lifelong Learner
Local time
Today, 04:36
Joined
Mar 14, 2017
Messages
8,738
And if all this is not bringing you much clarity, here's yet another idea: Abandon any attempt to compare dates to strings at all.

You have dates in your table. Now ask Access to compare a Date value (#mm/dd/yyyy#) to that value. Using =, >, <, or Between, or DateAdd or DateDiff.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:36
Joined
May 7, 2009
Messages
19,169
Code:
..
..
WHERE DatePart(tbl_Triage.TIMESTAMP) >= Nz([Forms]![frm_SearchTransactions]![txtDateFrom], tbl_Triage.TIMESTAMP)
AND DatePart(tbl_Triage.TIMESTAMP) <= Nz([Forms]![frm_SearchTransactions]![txtDateTo], tbl_Triage.TIMESTAMP)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:36
Joined
Feb 19, 2002
Messages
42,970
Let me say it differently. Left() is a string function. When you use Left() on a date field, the query engine must convert the date which is NOT STORED AS A STRING from a numeric value to a string so it can pick off x number of characters. The result is a string. When you compare two strings or a string and a number, you get a STRING comparison. So

14/12/2020 is less than 25/12/2019. That is probably not what you anticipated.

NEVER, EVER format dates to sort them or compare them. Always use date functions. If you have a date and that date includes time but you don't want time to affect the comparison, then use DatePart(myDate) to return only the date part AS A DATE.

FYI, dates are stored internally in Access, SQL Server, and Excel as double precision numbers. The integer portion is the number of days since 12/30/1899 (for Access, the others use a different origin date but the concept is the same). So in Access 1 = 12/31/1899, 0 = 12/30/1899, and -1 = 12/29/1899. The decimal part is the fraction of time since midnight so .5 = noon. .25 = 6 AM, and .75 = 6 PM.

The database engine has no trouble sorting and comparing dates when they are stored as double precision numbers. If you have to format dates before using them to sort or compare, you MUST use yyyy/mm/dd as the field order. Neither dd/mm/yyyy nor mm/dd/yyyy will work.
 

Users who are viewing this thread

Top Bottom