Convert String to Date in MS Access Query

I am trying to retrieve data from my access table based on Date column. My requirement is to display everything greater than the certain value. I am trying to cast my value, which is a string using Format &CDate function with datetime data type and it throws out as Overflow.

Here's query :

Select * from Events Where Events.[Date] > cDate(Format("20130423014854","yyyy-MM-dd hh:mm:ss")) 

Sample Date Record Value from Table : 2013-04-23 13:48:54.0

Events.[Date] is a Date/Time type field in access

How can I fix this ?

5 Answers

Use the DateValue() function to convert a string to date data type. That's the easiest way of doing this.

DateValue(String Date) 

In Access, click Create > Module and paste in the following code

Public Function ConvertMyStringToDateTime(strIn As String) As Date ConvertMyStringToDateTime = CDate( _ Mid(strIn, 1, 4) & "-" & Mid(strIn, 5, 2) & "-" & Mid(strIn, 7, 2) & " " & _ Mid(strIn, 9, 2) & ":" & Mid(strIn, 11, 2) & ":" & Mid(strIn, 13, 2)) End Function 

Hit Ctrl+S and save the module as modDateConversion.

Now try using a query like

Select * from Events Where Events.[Date] > ConvertMyStringToDateTime("20130423014854") 

--- Edit ---

Alternative solution avoiding user-defined VBA function:

SELECT * FROM Events WHERE Format(Events.[Date],'yyyyMMddHhNnSs') > '20130423014854' 

cdate(Format([Datum im Format DDMMYYYY],'##/##/####') ) 

converts string without punctuation characters into date

Basically, this will not work out

Format("20130423014854","yyyy-MM-dd hh:mm:ss") 

the format function will only work if your string has correct format

Format (#17/04/2004#, "yyyy/mm/dd") 

And you need to specify, what datatype of field [Date] is, because I can't put this value 2013-04-23 13:48:54.0 under a General Date field (I use MS access2007). You might want to view this topic: select date in between

If you need to display all the records after 2014-09-01, add this to your query:

SELECT * FROM Events WHERE Format(Events.DATE_TIME,'yyyy-MM-dd hh:mm:ss') >= Format("2014-09-01 00:00:00","yyyy-MM-dd hh:mm:ss")