Alpha numeric value

A

Albert

I have an Excel sheet that I import to Access for analysis with other data.
The table contains mainly Date fields (11/3/2005), but it also contains, text
fields (NA), and empty fields. In order to import the data, I save it first
as a *.csv and then import all field as "Text". That keeps the integrity of
the data. However, when I create the queries, and I want to, for example have
the criteria "give me all records that were updated after 11/01/2005
(>11/01/2005), it won't work, it does it undertand that I am working with
dates.
HOW CAN I HAVE A FIELD TYPE THAT READS "DATA," "TEXT," AND ALLOWED ME TO
HAVE EMPTY FIELDS?
Thank you
 
O

Ofer

Hi Albert
Try this

SELECT FieldName
FROM TableName
WHERE (((CVDate(IIf([FieldName]="NA" Or [FieldName]="" Or [FieldName] Is
Null,"1/1/1850",[FieldName])))>#11/01/2005#))

Incase of an empty field or NA it will return an old date, and then convert
the field to date field
 
J

John Vinson

I have an Excel sheet that I import to Access for analysis with other data.
The table contains mainly Date fields (11/3/2005), but it also contains, text
fields (NA), and empty fields. In order to import the data, I save it first
as a *.csv and then import all field as "Text". That keeps the integrity of
the data. However, when I create the queries, and I want to, for example have
the criteria "give me all records that were updated after 11/01/2005
(>11/01/2005), it won't work, it does it undertand that I am working with
dates.
HOW CAN I HAVE A FIELD TYPE THAT READS "DATA," "TEXT," AND ALLOWED ME TO
HAVE EMPTY FIELDS?
Thank you
Click to expand...

Ofer's suggestion is a good one; you may also want to try

IIF(IsNull([textdate], #1/1/100#, IIF(IsDate([textdate],
CDate([textdate]), #1/1/100#))

The IsDate function will reject any text string that can't be
interpreted as a valid date; it will gag on a Null hence the need for
the outer IIF.

Substitute any other useful default date for the 1/1/100 if you wish -
this is just the earliest date that Access can handle.

John W. Vinson[MVP]
 
Top