Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Change Date Format

    Hi,

    Can you someone please help?

    I want to change a date format from American format (mm/dd/yyyy hh:mm:ss AM) to English format (dd/mm/yyyy) but i am struggling to put a query together that will change it.

    Any help would be appreciated.

    Thanks,

    Gaz

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Whatever the settings of Windows can be, Access always works with dates in the mm/dd/yyyy (US) format, in SQL as well as in VBA. You can change the way a date/time data will be output (for display, export, print, etc...) by using the Format function:
    Code:
    Format([DateValue], "dd/mm/yyyy")
    Where DateValue is the name of a column (field) in a table or a query.
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Posts
    2
    Thanks for your reply.

    I have tried that method but it only changed so many and the rest were in a mm/dd/yyyy format.

    I tried the follow query....

    IIf(IsNull(CDate(Mid([CALL1DATE],4,3) & Left([CALL1DATE],3) & Mid([CALL1DATE],7,4))),#01/01/1900#,CDate(Mid([CALL1DATE],4,3) & Left([CALL1DATE],3) & Mid([CALL1DATE],7,4)))

    but i am getting 'Invalid use of Null'

    I can't see where i have gone wrong.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The Left and Mid functions will raise an error if [CALL1DATE] occurs to be Null. Try using Nz([CALL1DATE]) and adapt your expression accordingly.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    IIf(IsNull(CDate(Mid([CALL1DATE],4,3) & Left([CALL1DATE],3) & Mid([CALL1DATE],7,4))),#01/01/1900#,CDate(Mid([CALL1DATE],4,3) & Left([CALL1DATE],3) & Mid([CALL1DATE],7,4)))
    wont work as #01/01/1900# should be in quotes
    "#01/01/1900#"

    why you are messing around with formatting on this is beyond me

    I'd try something like
    iif(NOT isdate(Call1Date),"#01/01/1900#",Call1Date)
    you have two separate issues here
    Access/JET stores dates as a number of ticks since 31/12/1899, although its default display format is US mm/dd/yyyy or ISO yyyy/mm/dd

    the trick isw to make certain that call1date is a date value.. use a date picker or make certain the underlying dataype is datetime

    whenever I see a column called something like Call1Date it sends shivers down by normalised spine similar to running fingers down a blackboard.. are you absolutely certain your mode is normalised?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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