Change Date Format on Update

[Guest]

Table A has a date field that must be formatted ONLY as Medium date
(mm-ddd-yy). I am updating this date field everyday from another source
where the date field is mm-dd-yy hh:mm:ss. How can I modify my update query
OR Table A to drop the hours minutes secs from the input source? I cannot
have the hours minutes seconds in Table A as it is prohibiting some date
calculations that I am doing in queries.

 

[George Nicholson]

Check out the vb DateValue function: if Time information is included in its
argument, it is dropped and only date information is returned.

You could either include this function in your queries or do an Update query
on TableA and apply DateValue to the offending field (or add an additional
field to hold just the date...)

HTH,

 

[John Vinson]

Table A has a date field that must be formatted ONLY as Medium date
(mm-ddd-yy). I am updating this date field everyday from another source
where the date field is mm-dd-yy hh:mm:ss. How can I modify my update query
OR Table A to drop the hours minutes secs from the input source? I cannot
have the hours minutes seconds in Table A as it is prohibiting some date
calculations that I am doing in queries.

It's not really a *formatting* issue. A date, with or without time, is
actually stored as a double float number, a count of days and
fractions of a day (times) since midnight, December 30, 1899. That
date can be formatted any way you like without affecting what's stored
in the table.

It sounds like it's a data *content* issue - you want to store only
whole-number dates, eliminating any time portion, in tableA.

Rather than updating TableA to [TableB].[DateTimeField], update it to
DateValue([TableB].[DateTimeField]). The DateValue function will
discard any time portion and store only the pure date (which actually
has a time portion, namely midnight at the beginning of that day, but
this will not hurt your calculations).

John W. Vinson[MVP]