Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Location
    Kendal
    Posts
    10

    Question Unanswered: How do I change Date Time format in a Query?

    I have a query set up that includes a date/time field called ‘StartTime’ in am/pm format. I only want the field to display in a report if it contains data and have set up the query as follows:

    Start Time: IIf([StartTime] Is Not Null,"Start Time: " & [StartTime],Null)

    This works fine for what I need apart from the fact that the StartTime field is converted to 24h format instead of AM/PM.

    Does anyone know how I can keep the format as AM/PM instead of 24h?

    Cheers

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    formatting shouldn't be part of the data layer ie in a query, unless you have a need to do some processing on that format (say if you wanted to do some selecting or ordering based on days or months). Formatting is a function of the presentation layer, ie in the report or form using that data. Set an appropriate format in the control on the report / form using that column

    likely candidate are medium date, or write you own format mask.
    HTH

  3. #3
    Join Date
    Jul 2005
    Location
    Kendal
    Posts
    10

    How do I change Date Time format in a Query?

    Hi there,

    Thanks for your message.

    Formatting in the report was causing problems because the field is concatenated with the text “Start Time:” The reason for this is because I am trying to remove blank space from the report which is why I used the (text+[field]) in the query instead of a label on the report.

    I assume Access now thinks it is a string and will only give the time in 24h format with the prefixed text. I tried adding a format mask in the control on the report but it still shows as 24h time. Any suggestions on forcing the format or should I just ditch the prefixed text?

    Cheers

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thers a real nifty feature in Access, however its hardly ever used - it can be found by pressing F1

    type in format in the search combo box

    look in the resultant list box for
    "Format Property - Date/Time Data Type"

    from this you will then see all the various options you want

    based on the info there I'm guessing that what you want is
    Format([datetime column],"dd-mm-yyyy @ hh:nn:ss AM/PM")

    it should still be part of your report not your query. you can assign the control in the report to be
    ="Starting time:" & Format([DateStamp],"dd-mm-yyyy @ hh:nn:ss AM/PM")

  5. #5
    Join Date
    Jul 2005
    Location
    Kendal
    Posts
    10

    How do I change Date Time format in a Query

    Nice one, thanks for your help. I have moved the formatting into the report concatenated the text and fields required and it's really doing the biz now.

    Thanks again

Posting Permissions

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