• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

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?


0
avoorheis
Asked:
avoorheis
1 Solution
 
rockiroadsCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now