Working with Dates

[dz2k7]

Hi there,

I have long file (1 mil records) downloaded from AS400 as .csv text file.

there 2 feilds - shipping date and receiving date.

I need to calculate the lead time in days as RecievingDate - (minus) ShippingDate.

It would be easy in Excel, but i have too many lines.

So there are 2 problems.

How to make text data from .CSV the date in Access?
How to calculate using date data in Access?

Thanks to everybody interested to help.

 

[KenHigg]

I'd try first to simply put in a cdate() function and then in a datediff() function...

 

[Brianwarnock]

If the CDate doesn't compute because of the format of the text date, then you may need to look at Dateserial combined with Mid Left and Right and maybe even Instr functions, but it usually works.

Brian

 

[GohDiamond]

I work with .csv daily and here's what I do:
1) give the csv file a generic name like SOURCE1
2) link the csv file to the access database
3) use the "," as the only delimiter , work with this until the viewable sample shows the results you want
4) once the csv SOURCE 1 is linked use a query to move the data into a permanent table.
5) convert the text date field to a real date in the query with something like this Mydate:=DateValue([Text Date Field])
6) Change the query to a Make Table Query the first time you import.

For subsequent similar imports
1) Change the SOURCE1 csv to another name like SOURCE1.old
2) put your new csv in the same folder as the original csv with the name SOURCE1
3) the link will pick up the new csv just as if nothing had been changed
4) modify the MAKE TABLE Query into an APPEND Query, if you want to add this new data to the same table as before. if you want to create a new table change the destination table name in the make table query.

I hope this may help. Let us know if you have other questions.

Cheers,
Goh