SQL Date not working as expected (1 Viewer)

DBug

Registered User.
Local time
Today, 15:11
Joined
Jan 3, 2017
Messages
24
I have a query not working correctly with the dates

field DateProv = DateTime ie '15/07/2017 10:12:10'
string FromDate = ShortDate ie '15/07/2017'


SQLstr = "SELECT * FROM Phones WHERE Status = 'OK' AND Format(DateProv, 'dd/mm/yyyy') >= #" & FromDate & "#"



Sometimes it will select DateProv before FromDate, Im using the Format() function to convert DateProv from DateTime to ShortDate
 

JHB

Have been here a while
Local time
Today, 16:11
Joined
Jun 17, 2012
Messages
7,732
When you use a date as criteria in a SQL string, it has to be in US format, mm/dd/yyyy.
 

DBug

Registered User.
Local time
Today, 15:11
Joined
Jan 3, 2017
Messages
24
Got it, I used DateValue()

firstly I tested the SQL string as:

SQLstr = "SELECT * FROM Phones WHERE Status = 'OK' AND DateProv >= DateValue('26/04/2017')"

this worked so I then replaced the date Text with my FromDate String

SQLstr = "SELECT * FROM Phones WHERE Status = 'OK' AND DateProv >= DateValue('" & FromDate & "')"
 

DBug

Registered User.
Local time
Today, 15:11
Joined
Jan 3, 2017
Messages
24
I have now expanded my SQL statement to include ToDate

SQLstr = "SELECT * FROM Phones WHERE Status = 'OK' AND DateProv >= DateValue('" & FromDate & "') AND DateProv <= DateValue('" & ToDate & "')"

This is working except the query results will not include the date ToDate. For example to be able to get results up to today I need ToDate to be tommorows date for it to work even though I have used <=
 

isladogs

MVP / VIP
Local time
Today, 15:11
Joined
Jan 14, 2017
Messages
18,186
That's the way it works when you include the time in your field

The reason for that is that Access stores datetime values as numbers
For example its currently about 08:45 on 15 July 2017
CLng(Date) gives 42931
CDbl(Now) gives 42931.3646759259

So if you think about it any time after midnight last night won't be included in your SQL string as its greater than 42931
So do the date range to include the following day & it will include all times up to 23:59:59 on the day you are actually interested in

BTW you could use Between And instead of >= And<=
 
Last edited:

DBug

Registered User.
Local time
Today, 15:11
Joined
Jan 3, 2017
Messages
24
I Found the solution with something I found, by using Int(DateProv) it effectivley removes the fractional part or Time element of the date Value.Therefore it is now comparing ShortDate with ShortDate


SQLstr = "SELECT * FROM Phones WHERE Status = 'OK' AND Int(DateProv) >= DateValue('" & FromDate & "') AND Int(DateProv) <= DateValue('" & ToDate & "')"
 

isladogs

MVP / VIP
Local time
Today, 15:11
Joined
Jan 14, 2017
Messages
18,186
Yes it will do as its removing the part of the number date after the decimal point.
There are always several ways of doing anything in access.
The important thing is to understand WHY each solution works.
 

DBug

Registered User.
Local time
Today, 15:11
Joined
Jan 3, 2017
Messages
24
Yes I agree, often working out the best solution that works for you
 

Users who are viewing this thread

Top Bottom