losing date formats when updating field

[craigorama]

Hi everyone!

I'm create and update new date and time fields from an existing date and time fields. Basically converting from one time zone to another.

I've tried to set up the new fields as ShortDate and ShortTime but when i update the fields both time and date display in each. I just want to display the time in the NEW_TIME field and date in the NEW_DATE field.

Here is my code:

'======================================
'create the NEW_TIME field
'======================================

strSQL = "ALTER TABLE " & tabName & " ADD NEW_TIME Date ShortTime"
DoCmd.RunSQL strSQL
MsgBox ("NEW_TIME field added to the table")

'======================================
'create the NEW_DATE field
'======================================
strSQL = "ALTER TABLE " & tabName & " ADD NEW_DATE Date ShortDate"
DoCmd.RunSQL strSQL
MsgBox ("NEW_DATE field added to the table")

'======================================
'change uploadtime from gmt to mst
'by subtracting 7 hrs
'======================================

strSQL = "UPDATE [TELLUS_TEMP_UPLOAD] " & _
"SET [TELLUS_TEMP_UPLOAD].NEW_TIME = ([UPLOAD_DATE]+[TELLUS_TEMP_UPLOAD]!UPLOAD_TIME)-CDate(#12/30/1899 7:0:0#)"

DoCmd.RunSQL strSQL
MsgBox ("Time Changed")

'======================================
'change date from gmt to mst
'by subtracting 7 hrs
'======================================
strSQL = "UPDATE [TELLUS_TEMP_UPLOAD] " & _
"SET [TELLUS_TEMP_UPLOAD].NEW_DATE = ([UPLOAD_DATE]+[TELLUS_TEMP_UPLOAD]!UPLOAD_TIME)-CDate(#12/30/1899 7:0:0#)"

DoCmd.RunSQL strSQL
MsgBox ("Date Changed")

thanks for your help!

 

[AlexCuse]

I am not sure you can change the display properties through Jet SQL. You may need to go into table design view.

However, this seems to me like a bad idea. Why waste two columns on identical values? You should store this value in one date time column, and use your query to display date and time separately (feast your eyes on the mighty format function ;-) )

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[craigorama]

Hey alex,

thanks for your quick response.

I guess i wanted two seperate fields because once I update this table (which i've created out of a text file) I need to import the data from it into another, existing table. That existing table has seperate fields for date and time.

However, if the existing date and time fields are formatted as shorts will the data be formatted correctly when i import it?


I'm trying to do it all programatically to make it easier for end users.

- a noob appreciates the help