SQL Syntax error with ms-access query

Question

Here is my query below. The problem im having is that B.REVISED_DATE is a string and B.REVISED_DATE a date and when i try to run the query, i get a data mismatch error. What i am trying to do is change B.REVISED_DATE into a string but I dont remember the exact syntax. I am using microsoft access as the database. Any help would be appreciated.

     SELECT A.ICAO, A.RWY, A.REVISED_DATE, B.REVISED_DATE
     FROM RUNWAYS A, RUNWAYS_UPDATABLE B, AIRPORT_CHECKLIST C
     WHERE A.RWYKEY = B.RWYKEY
     AND A.ICAO = C.ICAO
     AND A.REVISED_DATE <> B.REVISED_DATE
     AND TO_CHAR(B.REVISED_DATE) <> C.EMAIL_DATE
     ORDER BY A.ICAO

What format of string are you using for A.REVISED_DATE?. YYYYMMDD?, MM/DD/YYYY?, etc — Lamak
– Lamak, Commented Sep 12, 2011 at 19:55

HansUp · Accepted Answer · 2011-09-12 20:20:28Z

4

Access' database engine doesn't support a TO_CHAR function.

If B.REVISED_DATE is Date/Time data type and C.EMAIL_DATE is text, you can use CDate() to get a Date/Time value from C.EMAIL_DATE.

B.REVISED_DATE <> CDate(C.EMAIL_DATE)

If you prefer to use the text equivalent of B.REVISED_DATE, you could use the CStr() function. However, unless it is formatted the same as C.EMAIL_DATE, your inequality comparison could be troublesome. You might be better off using Format() so that REVISED_DATE is formatted the same as EMAIL_DATE

Format(B.REVISED_DATE, "mm/dd/yyyy") <> CDate(C.EMAIL_DATE)

But seems simpler to me to do the comparison with both as dates, so you needn't be concerned about formatting issues.

answered Sep 12, 2011 at 20:20

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James213 · Accepted Answer · 2011-09-12 20:39:40Z

1

If you are looking to turn B.Revised_Date into a string use the Format() function.

Format(B.Revised_Date, "Format of date here")

Format will convert you date into whatever format you would like to use as shown here

If you are looking to turn B.Revised_Date into a Date use the CDate() function.

CDate(B.Revised_Date)

So if you are looking to have both of your dates be text then you can do something along these lines.

AND Format(A.REVISED_DATE, "MM/dd/yyyy") <> Format(B.REVISED_DATE, "MM/dd/yyyy")

Or however you want your dates to be formatted.

Hope this helps!

edited Sep 12, 2011 at 20:39

answered Sep 12, 2011 at 20:30

James213

9775 gold badges31 silver badges57 bronze badges

Collectives™ on Stack Overflow

SQL Syntax error with ms-access query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related