Converting a string to Date in an MS Access request

Question

I am trying to get data from my access table based on a Date column. My requirement is to display anything greater than a certain value. I am trying to cast use my value, which is a string using the Format &CDate with a datetime data type, and it is thrown as an overflow.

Here's the request:

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

An example of a date record value from a table: 2013-04-23 13:48:54.0

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

How can i fix this?

Answer 1

Use the DateValue() function to convert the string data type to date . This is the easiest way to do this.

 DateValue(String Date) 

Answer 2

In Access, click Create > Module and paste 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 

Press 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 that excludes custom VBA function:

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

Answer 3

In principle, this will not work

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

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

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

And you need to indicate what type of data the field is [Date] , because I can not put this value 2013-04-23 13:48:54.0 in the General Date field (I use MS access2007 ). You can look at this topic: choose a date between

Answer 4

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

converts a string without punctuation to a date

Answer 5

If you need to display all entries 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")