Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Date in Query (2k)

    In an update query - I am trying to format a text field with the system date in a "yyyymmdd" format. I have tried Format(Date, "yyyymmdd") and Format(Date, "yyyy", "mm", "dd"). Neither seem to work. In other parts of my application i have this working in vba code using the first formatting option. What would be the proper syntax for the Update To field in my query? Once again...thanks for you help!

    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting Date in Query (2k)

    If you want to use the Date function in an expression, you must use parentheses () after it, otherwise Access will think that Date is a field name. So try this:
    [pre]
    Format(Date(),"yyyymmdd")
    [pre]
    Make sure that there are no square brackets around Date.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Date in Query (2k)

    Thanks Hans...that worked great. Quick question...why does Access automatically change the minutes (mm) to NN?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting Date in Query (2k)

    The official formatting code for minutes is N or NN, while m, mm, mmm etc. are for months, as you will see if you look up custom date/time format in the online help. But Access is forgiving, if you use m or mm in combination with h, hh, s or ss, it assumes that you mean minutes, not months, so it will change the m's to N's.
    So the m's in yyyymmdd will be interpreted as months, but those in hhmmss will be interpreted as minutes, and hence changed to N's.

Posting Permissions

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