Changing Text Field to Date Field in Access 1

[jjc3397]

I have transferred a mainframe file down as text file and then brough the text file into Microsoft ACCESS. I have developed a specification in ACCESS and have parsed the fields. One of the fields contains a Transfer Date. This Transfer Date is in a Text format. I need this field in be a Date/Time field so that I can query the field with criteria. I need the Transfer Date to be >=04/01/05 and <=09/30/05. The field coontains date infromation with a 04 year. Also, ACCESS does not recognize the field as a Date Format. Also, some of the records in the Transfer Date Field has 5/05/05 with no trailing zero in front.

 

[PHV]

In table design view add a DateTime field to your table and then you may try this action query:
UPDATE yourTable SET [DateTime field] = CDate([Transfer Date])
WHERE [DateTime field] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjc3397]

Thanks for answering my question. However, you mentioned adding a DateTime Field to my table in Design View. I have done this. But, are you doing a Update Query by placing the code in UpdateTo or do you place the code in the criteria part? I am new to Access and realize you have several action query's. The Update Query is one of them. Is the Update Query the correct action query to use or should I use a Make-table Query?

I have a Transfer Table with all of the Transfer Date Info called a Date Fieldname coming as text. Your code makes sense, but I just need to know where to place the code in the Update Query that I am doing. Also, the Where[DateTime field]is null gives me a syntax error message too.

The DateTime Field that I have added to the transfer Table in Design View is TransferDate. The Date field in this table has the Date in text format.

I have tried to place the code in the updateTo part with

Set[TransferDate]=CDate([Date])where[TransferDate]is null

This is not working because I believe I have this code on the wrong part.

Just confused!

 

[PHV]

The posted code is SQL, so you have to go in the SQL view pane of the query window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjc3397]

Yes, this Sructured Query Language works fine, but the dates are crazy when they appear now. I have dates well into the 21st Century. Dates do not appear as they should.
Possibly a century problem with dates.

Text Date Field Appears as
10805 07/31/29
102704 03/10/81
62205 09/18/75
21005 04/29/29