0

I am using Ms Access and below is my Query

select * from travelapproval 
where CDate(datefrom1) <=  Date() 
and CDate(dateto1) >= Date()

I am using datefrom1 and dateto1 are text type. It shows "Data type mismatch in criteria expression."

How can I convert them to datetime even I tried with CDate function.

enter image description here

Gaurav
  • 557
  • 4
  • 11
  • 28
  • Can you show a couple of sample rows? – rene Jun 14 '13 at 09:56
  • I have attach the image of my MS Access table. – Gaurav Jun 14 '13 at 10:02
  • Silly question; why would you want to store dates as text?? Why not just change the data type to DateTime and be done with it? – Johnny Bones Jun 14 '13 at 14:09
  • I know that this is an old thread, but one important factor is that when looking at a swath of dates, it's much easier to compare them when they are of the form YYYY-MM-DD HH:MM. All subfields are the same width, and the most significant subfields are to the left. – user36800 Jul 14 '15 at 14:21

3 Answers3

5

Assuming that you are not able to change the field type to Date.

Check whether you have non date values in datefrom1 and dateto1 fields.

If you do, use the following to get around it:

CDate(IIf(IsDate([StringDate]),[StringDate],0))
E Mett
  • 2,272
  • 3
  • 18
  • 37
2

you can use this

  SELECT * from travelapproval
  WHERE Format(datefrom1, "mm/dd/yyyy hh:nn:ss am/pm") <=  Date() 
  AND Format(dateto1, "mm/dd/yyyy hh:nn:ss am/pm") >=  Date()

you can find more example and explanations here

echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • shows error ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Format(datefrom1, mm/dd/yyyy hh:hh:ss) <= Date() AND Format(dateto1, mm/dd/yyyy hh:hh:ss) >= Date(). – Gaurav Jun 14 '13 at 10:12
  • this is my new code as you suggested. string strSql = "SELECT * from travelapproval WHERE Format(datefrom1, mm/dd/yyyy hh:nn:ss am/pm) <= Date() AND Format(dateto1, mm/dd/yyyy hh:nn:ss am/pm) >= Date() and approval=?"; – Gaurav Jun 14 '13 at 10:18
  • take a look here how it helps you http://stackoverflow.com/questions/7263766/convert-a-text-to-a-datetime-in-access?rq=1 – echo_Me Jun 14 '13 at 10:34
  • if I do like this string strSql = "SELECT * from travelapproval WHERE datefrom1 <= Date() AND dateto1 >= Date()"; yes it is showing records but ALL. not satisfying the below condition datefrom1 <= Date() AND dateto1 >= Date() – Gaurav Jun 14 '13 at 10:53
  • Is it just me, or are you missing the quotes for the date format? It's a string, afterall. – gdbj Dec 16 '13 at 23:56
  • Doesn't Date() not return the time? AFAIK, if you want both time and date, you use Now() – Carcigenicate Feb 18 '15 at 03:09
  • `Format` returns a string from a date or number. The question is about the opposite – iDevlop Nov 03 '17 at 15:22
0

Assuming the datetime string is from SQL Server with milliseconds,

Format(Replace([SQLSERVER_DATESTRING],".000",""),"mm/dd/yyyy hh:nn:ss am/pm")
Tunaki
  • 132,869
  • 46
  • 340
  • 423
sportsguy
  • 1