Re: Date Conversion

No. You cannot alter the date with an update query, because internally
Access stores the date/time data as a floating point number, where the
integer part represents the date and the fraction represents the time of
day. The format is applied at display time, so it does not make sense to
talk about altering the stored data to match a display.

The data is displayed to the user according to how they have dates defined
in Regional Settings in the Windows Control Panel. You could therefore alter
your settings there, and Access would display the dates that way. Of course
the change would affect all your other well-behaved Windows programs as
well.

If that is not acceptable, you can use the Format property of the field in
the table to specify how the data should be displayed. Or, you could even
set the Format property of the field in the query.

Note that literal date/time values in VBA code or SQL statements is NOT
affected by your regional settings. For more info on that and the 3 cases
where Access tends to misunderstand dates, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul" <paul@xxxxxxxx> wrote in message
news:1125500587.843150.76410@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Is there a way in Access to convert a date in format "mdy - 08/05/2005"
> to "ymd - 2005-08-05" using an update query?


.

Relevant Pages

  • Re: Format Date in Table
    ... I tried to write an update query for this but not ... just change the Format property where ... ever you display it. ... query or text box to replace the spaces with slashes: ...
    (microsoft.public.access.queries)
  • Re: Table Format Property > not recognized cuasing no data to be d
    ... In the design mode if you create a look up box and set the format property ... The MsAccess .mde file is accessed by multiple people in the company ... I have created a table and defined some fields names (i.e customer) ... MsAccess in form view mode will not display the ...
    (microsoft.public.access.modulesdaovba)
  • Re: If Null - Display Text
    ... Microsoft Access MVP ... I don't very often use the Format property, ... Format property of the text box to display "Not Available" if the value is ... Available" if a field named RequestDate is null, ...
    (microsoft.public.access.reports)
  • Re: copy value from field to another in same table, different reco
    ... the reason for my questions is to identify how to trigger the update and ... how to do the update...it'll require either an update query or some ... >>> opening amount displayed on the register form screen for the next day. ... >>> having problems with how to display a field value from a previous ...
    (microsoft.public.access.macros)
  • Re: Limit decimal places without format statement
    ... forget the Round function, and modify the Format and Decimal ... In query design view, click on ... Set the Format property to "Fixed" ... but causes it to display as rounded. ...
    (microsoft.public.access.forms)