Copying data of different formats.

[lasd]

Hi again,

hope again that someone can help me. In my database my date and time intervals are stored in a column with the format as Date/Time. eg. 12/04/2004 00:15:00.
The information I want to copy into this table and field comes to me in the format of 12042004 0015.
Is it possible to change the format of this field when i am doing an append query. There are something like 3000 rows of data so i don't think it would be very practical to go through each row and change the date and time to the format i want, manually.
Thanks in advance for your help.
again so grateful

lasd

 

[KenReay]

Hi

You could try appending from a query based on the table rather than the table itself, then you could (in the query) have a calculated column possibly using the format command (see help) or the CDate() command (see help)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

A starting point:
CDate(Left(ImportedDate,2) & "/" & Mid(ImportedDate,3,2) & "/" & Mid(ImportedDate,5,4) & Mid(ImportedDate,9,3) & ":" & Mid(ImportedDate,12))

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

 

[lasd]

Hi

thanks a million for your help. I am just going to paste the expression that i am using and see if you can see for me where the error is. My program is saying there is a syntax error(comma)and i just can't find it.
Expr1: CDate(Left([Interval],2) & "/" & Mid([Interval],3,2) & "/" & Mid([Interval],5,4) & Mid([Interval],9,3) & ":" & Mid([Interval],11,2))

also do i put this expression in the field box?

thanks
lasd

 

[PHV]

You must be european, I guess.
CDate(Left([Interval];2) & "/" & Mid([Interval];3;2) & "/" & Mid([Interval];5;4) & Mid([Interval];9;3) & ":" & Mid([Interval];12))

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

 

[lasd]

Nope this still doesn't work. we would use the coma's instead of the semi_colon's here too.
you guessed right i am european......

 

[PHV]

Try to paste the formula while in the SQL view.

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

 

[lasd]

Hi I tried that and it seems to be working except it is not bringing back all of the values for me. the first line comes back in correct format(wrong time)and then there are errors for the other 2560 values. it is showing this error message on the table #error.... do you know why this might be

thanks a mill
lasd

 

[PHV]

Is Interval ALWAYS 13 characters with VALID date time ?

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

 

[lasd]

oh just worked it out there... in the sql i was accounting for the fact that it was coming in 01012005 1215 but it is actually coming in like this
010105 1215..
so thanks a million for your help. you have been brilliant..

lasd

 

[lasd]

another quick question...... i have all of these intervals coming in on a daily basis.
the read that i am importing for midnight reads like this
010105 2400
this is the only read that is coming up as an error in my file. i think this is because the function will only read as far as 0-23 and won't read 24:00.. do you know if there is a solution to this.