Date value in a Text field problem (1 Viewer)

[purepremiumpulp]

Hi, I have created a table with a column called lastlogon, which stores the date of the last logon from windows active directory for a user id. Everything is being imported from a text file. Most of the time there is a valid date of format, "6/8/2006 1:10:23 PM" . I can declare the column as a Date/Time with that format, but sometimes if the user has never logged on, the source data will "never" for that column.

I have resorted to creating the column as a text field and trying to "convert" the valid fields to real dates for use in queries. In a very similar situation, I had the same problem with numbers and text. There is another column that keeps track of the password age, and it will either contain a number (in days) or the string, "NeverChanged". I was able to use the Val() function to get workable results. Is there something similar that can be done for dates to do an expression conversion but still store it as a text field so that I can accomodate for the occasional string, "never" ?

There are ~25,000 records that are being imported so I don't want to simply do a search and replace the "never" string with a bogus date.

Thanks for any help!

 

[RuralGuy]

Have you looked at the IsDate() function?

 

[purepremiumpulp]

Thanks RuralGuy, I used that in the where statement so that the junk text wasn't treated as a date... also found DateValue(), so this worked out great

SELECT [Active Directory].psfield, [Active Directory].cwid, DateValue([logonlast]) AS [Last logon]
FROM [Active Directory]
WHERE (((IsDate([logonlast]))=True));

 

[RuralGuy]

Excellent! Glad I could help.