Create a field in DATE format based on a field in TEXT (1 Viewer)

[accessfever]

My table has a "PIR Valid-Frm" field in TEXT for data of "valid-from date" . I want to create a new column to have the data in date format so I can do some calculations.

The data in "PIR Valid-Frm" field are TEXT format in MMDDYYYY (e.g. 12152009, 09102010, 03082011). The New fieldname is "PIRDATE".

Any idea? Any help will be appreciated.

 

[Brianwarnock]

This has been asked many times and a search should have found a solution, although I do realise that the search facility is not the best.

Use Dateserial with a combination of Left, Mid and Right

Dateserial(Right([PIR Valid-Frm],4),Left([PIR Valid-Frm],2),mid([PIR Valid-Frm],3,2)) should do it

Dateserial can be found in vba help.

Brian

 

[accessfever]

The new field works like the way exactily I'm looking for. Thanks very much!