SQL Date not working as expected

[DBug]

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

 

[plog]

Im using the Format() function to convert DateProv from DateTime to ShortDate

No you are not. You are using Format to convert something to a string--that's how Format works:

https://www.techonthenet.com/access/functions/date/format.php

You should use DateValue (https://www.techonthenet.com/access/functions/date/datevalue.php) to convert your string to a date.

 

[JHB]

When you use a date as criteria in a SQL string, it has to be in US format, mm/dd/yyyy.

 

[DBug]

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]

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]

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<=

 

[DBug]

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]

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]

Yes I agree, often working out the best solution that works for you