Checking for invalid date formats

[meckeard]

Hi,

I have a database where a date is entered into a field that is nvarchar. But when I try and compare the field to a date supplied on a new form, I get the following:

The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I have an older copy of the database that works fine. So I assume that it's because one or more of the dates are invalid.

Is there an easy way to check the values in the table? Like this:

select * from table where field not IsDate(field)

???

I thought about scrubbing the data. But is there a better approach?

Thanks,
Mark

 

[nicsin]

Hi Mark,

It would help if you could post your code here and indeicate where the error occurs. It may not be a problem with the data.

 

[jrbarnett]

Mark

nvarchar field type suggests to me that you are using Access as the front end to another database as there is no such data type in Access.
You can use a text field to store date values, but if you want to compare a date value with a text value in a field, you must temporarily convert the text value to date format for comparison. Thus:

If CDate (Textfield) = Datefield Then
....
End If

Access itself won't allow invalid dates in a date/time field (eg extra days in months that are invalid; months over 12 etc) but you can probably pick up any from your back end table with something like the following SQL query:

select * from table where IsDate(field) = False

John

 

[SteveR77]

Mark,

If the format of the date field that you are working with is correct try this -

CDate([MyDateField])

Then use your date criteria on this field in your query.


Steve

 

[meckeard]

Opps, the datatype is text. It's hard for me to stop thinking in terms of SQL Server.

Here is the query in question:

SELECT tblPackets.strEnteredBy, Count(tblPackets.strEnteredBy) AS CountOfstrEnteredBy
FROM tblFiles INNER JOIN tblPackets ON tblFiles.lngFileID=tblPackets.lngFileID
WHERE (((CDate(tblFiles.strCISID))=Forms!frmReportGenerator.txtFromDate))
GROUP BY tblPackets.strEnteredBy;

I was able to run this query to find the 1 record that was an invalid date format:

select *
from tblFiles
where not IsDate(strCISID)

There is a value for 09/31/2003 -- and there is only 30 days in September 2003.

I will work on changing the field to a datetime type.

Thanks,
Mark

 

[nicsin]

Mark,

After you fix it try this query:

SELECT tblPackets.strEnteredBy, Count(tblPackets.strEnteredBy) AS CountOfstrEnteredBy
FROM tblFiles INNER JOIN tblPackets ON tblFiles.lngFileID=tblPackets.lngFileID
WHERE (((tblFiles.strCISID)=#cdate(Forms!frmReportGenerator.txtFromDate)#))
GROUP BY tblPackets.strEnteredBy;