Change Number to Date

[tucker61]

I have 2 query's that import information from a linked table (txt) into a table in my database.

Both queries have a date field but they don't pull the date formatted as a date field, SO I think that during the import query I need to format the text as a date. in query 1 the text in its raw format is 20171109, and in query 2 it is 9112017.

I cannot amend the text files as they are systemic driven, and I would have to jump through hoops to get any changes.

Would I be better putting the data into 2 separate tables ? as every other field is the same apart from this date field ?

Any ideas ?

Thanks

 

[plog]

Don't directly import your files into the ultimate table the data will go into. Instead, import the data from the files into a staging table (tmp_Stage). Then run a query(queries) to move the data into the final table converting the fields to the correct data type as you do.

Actually, I always have a validation set of queries to let me know if any data is bad that will throw off the move query (a date that's not really a date, a number that's not really a number, etc.).

 

[tucker61]

What's the easiest way to convert the numbers into a date ?

 

[plog]

With the format it is coming in at, you would need to extract out the parts you need (Mid()) and then you would put them together and create a date (CDate()).

Check out this page with the functions you would need:

https://www.techonthenet.com/access/functions/

 

[tucker61]

Thanks, I have used left, right and mid before. But never heard of Cdate..

I will have a read of the link you sent..

 

[tucker61]

Interesting. I might have a look at this later. I did create a import specification, but every time I set the format as date and time, the data did not import, but gave me a error similar to !num. I did not know we could change the import format to yymmmdd etc.. Thanks..

 

[tucker61]

Re imported my data, setting the fields to Date & Time, and changing the format. This worked. Thanks...

 

[tucker61]

You're welcome.

Re did my import specification today and the new import works fine.. my problem now is the old data..how to convert this to the correct date format.

I tried to export into .txt file but this did not look correct.

I need to export the old data, then create a new import specification for this data. I tried this in excel and access but doing it in them formats did not let me create the import specification.

What is the best way to do this. In total I have about 300k records that need to be exported .

Cheers.

Sent from my SM-T715 using Tapatalk