Date comparison after format cdate

I have a linked csv table that contains a date in the format of Oct 2006 and my link spec brings it in as text.
This query works ok:
SELECT Format(CDate([Month]),"yy/mm") AS Expr1
FROM tblPCGoogleReport;

this query works ok:
SELECT Format(CDate([Month]),"yy/mm") AS Expr1
FROM tblPCGoogleSLXMatch INNER JOIN tblPCGoogleReport ON tblPCGoogleSLXMatch.[Google Account Name] = tblPCGoogleReport.Account
ORDER BY Format(CDate([Month]),"yy/mm");

this query errors with data type mismatch:
SELECT Format(CDate([Month]),"yy/mm") AS Expr1
FROM tblPCGoogleReport
ORDER BY Format(CDate([Month]),"yy/mm");

I don't understand why I get an error in the 3rd query...any ideas?


avoorheisAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
isnt the 3rd query the same as the 1st except u have ordering?

try ordering using columnn numbers
e.g.

SELECT Format(CDate([Month]),"yy/mm") AS Expr1
FROM tblPCGoogleReport
ORDER BY 1


only other thing is perhaps [Month] contains nulls or an invalid date
0
 
avoorheisAuthor Commented:
Silly me, for expecting to be sent clean data. Yes, there was a record that had bad data.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.