I'm memory serves, I believe the appropriate syntax for casting as a Date in Access uses the
CDate() function :
SELECT Field1
FROM Table1
WHERE CDate(date1) = CDate(@Date1)
I don't remember how Access handles parameters, but they may or may not be named. You might try using '?' characters to denote them if the above approach doesn't like the parameterization :
SELECT Field1
FROM Table1
WHERE CDate(date1) = CDate(?)
Member
117 Points
511 Posts
Casting in WHERE Clause
Nov 15, 2014 12:44 PM|KulerMaster|LINK
In SQL Server when i need to select data of the certain date (day) i usually use the following query casting the date field and the param:
How do i do the same in a saved query in MS Access? If i just say "WHERE date1 = DateTimeParam" it returns no data from that day.
Thank you so much
All-Star
114593 Points
18503 Posts
MVP
Re: Casting in WHERE Clause
Nov 15, 2014 12:56 PM|Rion Williams|LINK
I'm memory serves, I believe the appropriate syntax for casting as a Date in Access uses the CDate() function :
I don't remember how Access handles parameters, but they may or may not be named. You might try using '?' characters to denote them if the above approach doesn't like the parameterization :
All-Star
52793 Points
15764 Posts
Re: Casting in WHERE Clause
Nov 15, 2014 01:05 PM|oned_gk|LINK
Suwandi - Non Graduate Programmer
Member
117 Points
511 Posts
Re: Casting in WHERE Clause
Nov 15, 2014 01:18 PM|KulerMaster|LINK
Thank you for replying (it's much appreciated)
Btw this is a SAVED QUERY (Inside MS Access database) not a query contained in the C# or VB code
However CDate contains the TIME portion as well and therefore it never matches :(
Isn't there a way to cast the datetime to ONLY DATE and then compare?
Thank you again
Member
117 Points
511 Posts
Re: Casting in WHERE Clause
Nov 15, 2014 01:19 PM|KulerMaster|LINK
Thank you for replying it's much appreciated.
Unfortunately if i use Cint function I get an error "Data type mismatch in criteria expression." .
All-Star
114593 Points
18503 Posts
MVP
Re: Casting in WHERE Clause
Nov 15, 2014 01:32 PM|Rion Williams|LINK
You may be able to try the DateValue() function :
Or you could try something crazy like the following :
Member
117 Points
511 Posts
Re: Casting in WHERE Clause
Nov 15, 2014 01:49 PM|KulerMaster|LINK
DateValue worked .. will test the other solution shortly and will let you know if it works as well.
Thank you so much
Member
117 Points
511 Posts
Re: Casting in WHERE Clause
Nov 15, 2014 01:51 PM|KulerMaster|LINK
Nah the other one throws an exception "Data type mismatch in criteria expression."
Anyway thanks for the DateValue suggestion