Can’t Sort by Date using CDate

[David Ball]

Hi,

I have a query that is based on a table that is updated from a spreadsheet that I get from another department. There is a Date column in the spreadsheet for Forecast Delivery Dates. Unfortunately, when an item has arrived they enter “Delivered” instead of the date. So I have a column that has dates and text and I need to be able to sort by the dates to arrange them with the latest delivery dates at the top (I don’t care about the items already delivered).
I created a field that converts the “Delivered” text to a date in the past (say, 01-Jan-12). This looks OK but the dates don’t sort properly.
I added another field that uses the FORMAT function to format as “Medium Date”. Again, it looks OK but dates don’t sort properly because they look like dates but are a string.
I then created another field that uses CDate to convert to a date. When I go to Sort this I get a message “Data Type Mismatch in Criteria Expression”.

What do I need to do to be able to Sort these dates?
Is there a way that I can Sort the original field, containing both dates and text, by date?

Thanks very much.
Dave

 

[pbaldy]

If I had to guess, your error is from a record that's either Null or can't be converted to a date. In a very brief test this appeared to work as a sortable field in a query:

IIf(IsDate([TextField]),CDate([TextField]),0)

 

[MarkK]

This particular error in this particular case may be hiding the fact that you have an error in your data which your CDate function is choking on, for instance, you may have a string date like "13/13/16" or "29-Feb-13" or one of your date values may be Null. The issue is that CDate is a function, and if you do this in a query . . .

SELECT CDate(MyStringDateField) As ConvertedToDate FROM ...

. . . that function is run against every row. If one row has one string against which that conversion fails, this error occurs.

Try writing a select query with SQL like this first . . .

SELECT RowID, IsDate(MyStringDateField) As IsValidDateString 
FROM table
WHERE Not IsDate(MyStringDateField);

. . . which will show the rows in which a date converstion will fail, and in this way confirm that the data you are passing in to your CDate function can actually succeed at being converted.

Hope this helps,

 

[MarkK]

Just noticed that Paul beat me to it. *QuickDraw*

 

[pbaldy]

Just noticed that Paul beat me to it. *QuickDraw*

It's the high altitude here; less air resistance on the fingers.

 

[David Ball]

Thanks very much, guys, I will check this out

 

[arnelgp]

why not use a real date/time field and save you from worrying the correct format to use. you cant' straighten a rusty iron, it will just break.