Adjusting field data with update query

[Guest]

I run an import where the date is formated as follows:
Wed Aug 22 11:47:43 -0500 2007

In excel I created the following macro to adjust the format to mm/dd/yy
hh:mm:ss
For Each Cell In Range("e2:h" & Range("e65535").End(xlUp).Row)
If Cell.Value <> "" Then
Y = Right(Cell, 4)
M = Month(Mid(Cell, 5, 6))
D = Mid(Cell, 9, 2)
T = TimeValue(Mid(Cell, 12, 8))
Cell = DateSerial(Y, M, D) + T
End If
Next

Can I create something similar with an update query?
I tried placing the following into the 'update to', but it simply deletes
the entire field:

DateSerial(Year(Right([Created On],4)),Month(Mid([Created
On],5,3)),Day(Mid([Created On],9,2)))

Microsoft Access 2003

 

[Bob Quintal]

I run an import where the date is formated as follows:
Wed Aug 22 11:47:43 -0500 2007

In excel I created the following macro to adjust the format to
mm/dd/yy hh:mm:ss
For Each Cell In Range("e2:h" & Range("e65535").End(xlUp).Row)
If Cell.Value <> "" Then
Y = Right(Cell, 4)
M = Month(Mid(Cell, 5, 6))
D = Mid(Cell, 9, 2)
T = TimeValue(Mid(Cell, 12, 8))
Cell = DateSerial(Y, M, D) + T
End If
Next

Can I create something similar with an update query?
I tried placing the following into the 'update to', but it simply
deletes the entire field:

DateSerial(Year(Right([Created On],4)),Month(Mid([Created
On],5,3)),Day(Mid([Created On],9,2)))

Microsoft Access 2003

Your year() month() and day() functions serve to extract the
relevant portions from the date, which is stored in Access as a
double precision number, with the integer part being the number of
days since Dec 31, 1899 and the fraction the percentage of the time
passed in the day. You cannot use them against a string.

Also since a date in Access is a number, it doesn't belong in a text
field, but in a proper date/time type field. Excel doesn't care what
goes where.

To convert a string to a date, use the cdate() function in your
update to row of a new field to hold the date.

CDate(Mid([created on], 5, 6) & Right([created on], 5) & Mid
([created on], 11, 9))

 

[Guest]

Bob,

Thank you so much for the explanation, and the code necessary to make it do
what I needed to.
--
KamronsDad

I run an import where the date is formated as follows:
Wed Aug 22 11:47:43 -0500 2007

In excel I created the following macro to adjust the format to
mm/dd/yy hh:mm:ss
For Each Cell In Range("e2:h" & Range("e65535").End(xlUp).Row)
If Cell.Value <> "" Then
Y = Right(Cell, 4)
M = Month(Mid(Cell, 5, 6))
D = Mid(Cell, 9, 2)
T = TimeValue(Mid(Cell, 12, 8))
Cell = DateSerial(Y, M, D) + T
End If
Next

Can I create something similar with an update query?
I tried placing the following into the 'update to', but it simply
deletes the entire field:

DateSerial(Year(Right([Created On],4)),Month(Mid([Created
On],5,3)),Day(Mid([Created On],9,2)))

Microsoft Access 2003

Your year() month() and day() functions serve to extract the
relevant portions from the date, which is stored in Access as a
double precision number, with the integer part being the number of
days since Dec 31, 1899 and the fraction the percentage of the time
passed in the day. You cannot use them against a string.

Also since a date in Access is a number, it doesn't belong in a text
field, but in a proper date/time type field. Excel doesn't care what
goes where.

To convert a string to a date, use the cdate() function in your
update to row of a new field to hold the date.

CDate(Mid([created on], 5, 6) & Right([created on], 5) & Mid
([created on], 11, 9))