Convert String to Date in MS Access Query

Question

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  amp CDate function with datetime data type and it throws out as Overflow   Here s query    Select   from Events Where Events  Date   gt  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

User · Answer

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

User · Answer

In Access  click Create  gt  Module and paste in the following code  Public Function ConvertMyStringToDateTime strIn As String  As Date ConvertMyStringToDateTime   CDate            Mid strIn  1  4   amp   -   amp  Mid strIn  5  2   amp   -   amp  Mid strIn  7  2   amp       amp            Mid strIn  9  2   amp       amp  Mid strIn  11  2   amp       amp  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   gt  ConvertMyStringToDateTime  20130423014854     --- Edit ---  Alternative solution avoiding user-defined VBA function   SELECT   FROM Events WHERE Format Events  Date   yyyyMMddHhNnSs    gt   20130423014854

User · Answer

Use the DateValue   function to convert a string to date data type  That s the easiest way of doing this   DateValue String Date

User · Answer

cdate Format  Datum im Format DDMMYYYY                     converts string without punctuation characters into date

User · Answer

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     gt   Format  2014-09-01 00 00 00   yyyy-MM-dd hh mm ss

[sql] Convert String to Date in MS Access Query

Examples related to sql

Examples related to ms-access

Examples related to casting