UPDATE query

[John Thomas]

I want to create an UPDATE query that updates a table that
has a text field that contains dates in the following
format:

FromDMMYY
To:YYYYMMDD

How can I do this if

OP CMDS is my table
CensusDate is my field
Also the CensusDate field may have blank fields.

 

[Michel Walsh]

Hi,



The date_time is internally store with bits, you don't want to change
that, isn't it? The REPRESENTATION of the bits in a readable manner is left
to the FORMAT. You change the format, you do not change the VALUE. Use a
format that will use the order you want to see, like "yyyy.mm.dd".

Remember that if you distribute the application to many clients, it is
preferable to LET the default format: that is the one the end user is likely
used to (through its specific parameter in the Regional Settings)... and the
end-user it the one that will use the application, not the developer.

Sure, I assumed your field data type was date_time. If it is a string,
then that is another complete whole story. I would say to convert the whole
field to a date_time field asap. Set the setting to ddmmyy, then, use
CDate( CensusDate ) to convert the string to a date_time value
(internally, a floating point number, not a string, expressed as a number of
days, and decimal fraction of a day). That is generally the safest way to
store a date, avoiding regional settings configurations, from that point.




Hoping it may help,
Vanderghast, Access MVP