Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    11

    Unanswered: Update Date Field help!

    I have a date field in my DB, however I didnt realize the originating data in excel had some of the dates backwards...

    02.03.2009 should be 03.02.2009, but not all dates are broken like this so I want to do an update on the date field but it wont let me...

    UPDATE [FEB] SET [FEB].[Date] = #2/3/2009#
    WHERE ((([FEB].Date)=#3/2/2009#));

    why wont this work?

    OPERATION MUST USE AN UPDATEABLE QUERY

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Date is a reserved word in access and since you have named a field Date, it must be surrounded with [] every time you reference the [Date] field.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Dec 2003
    Posts
    24

    Red face

    you should be better of if you rename the field eg dtDate use format for checking and changing the date order.
    Further it seems that whateve way you format Access stores the dates in YYYY-MM-DD format, so best approach would be to store the dates in YYYY-MM-DD format and display in queries, forms, report etc as you desire.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No Access stores dates as a number of (integer) ticks for (IIRC) 01/01/1900, it stores times as the number of seconds from midnight (as decimal) so the integer part of a datetime tells you the day/month year, the decimal part the time. when you request a vlaue form a datetime value, unless you specify the format it will be returned in the default short time format as per your systems localisation settings.

    I would agree that generally you are better presenting dates to the system as per the ISO standarad as year month day, or the US standard dd/mm/yyyy. Access is quite keen on identfying dates with a has symbol eg #03/08/2009#

    going back to the OP
    Date is a reserved word and you will have problems over time using a reserved word.. reserved word being a word that JET SQL 'reserves' for its own use. if you do a google on MS Access reserved words you will find plenty of help on what not to use. Why db authors and tool developers don't block attempts to use reserved words is beyond me. JET does do a very good job trying to workaround use of reserved words but occasionally it does come a cropper

    instead of
    UPDATE [FEB] SET [FEB].[Date] = #2/3/2009#
    WHERE ((([FEB].Date)=#3/2/2009#));
    try
    UPDATE [FEB] SET [FEB].[Date] = "#2/3/2009#"
    WHERE [FEB].Date="#3/2/2009#;

    Im assuming that your column 'date' actaully is a date time value
    Im also a little horrified to think that your table is called 'Feb' and you have 11 other tables called Jan, Mar,Apr...Dec... that has the firm smack of bad design
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2007
    Posts
    277
    A link where MS describes a DateTime field.
    Another link I use for reserved words.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •