Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports - Saturday through Sunday (2002)

    I'm doing a query on Hospital Census form a Census database.
    I've changed the [DateOfRecord] to days of the week by using "DayOfWeek: Format([cDateOfRecord],"dddd")"
    That works fine but all of my reports are sorted ascending - Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday.

    Is there an easy way in Access to sort Saturday through Sunday?

    I tried, in the sort box in the query window, to use "01","02", etc....
    and I tried "Sunday", "Monday", "Tuesday" ect.... I get an error that the " The text you entered isn't an item on the list".

    Thanks so much!
    Vicky

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

    Re: Reports - Saturday through Sunday (2002)

    Use the DateOfRecord field itself, and set its Format property to dddd. The underlying value will be a date, so it will be sorted chronologically instead of alphabetically, but it will be displayed as the name of the day.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - Saturday through Sunday (2002)

    Hans, thanks for the quick reply. I've been trying what you said and I must still be doing something wrong. I tried leaving the date field as a date, then formatting it on the report. It still begins the days of the week with Friday. I used =Format([cDateOfRecord],"dddd") on the report.

    I'm not sure if you do much with graphs, but I also have a Graph that's a little harder for me to format. It displays the days of the week in the same alphbetical order. I don't see a place to format the date other than the choices it gives you as Day, Week, Month - the graph then displays dates not words.
    Thank you,
    Vicky

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

    Re: Reports - Saturday through Sunday (2002)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - Saturday through Sunday (2002)

    I have a read only rights to this database with linked tables and it's quite large so I created a small one with only the data that I'm working with.
    I have a quick sample graph and report under Reports. The Graph is using the format function as [DayOfWeek] in the query, the Report is formatting the date field on the Report itself.

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

    Re: Reports - Saturday through Sunday (2002)

    It wasn't clear from your original description that you were aggregating the data by day of the week instead of by date. This requires a slightly different approach.

    I have attached the database with modified form and reports. View the Row Source of the charts to see how it is done. I don't understand the other report.

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports - Saturday through Sunday (2002)

    Thanks again Hans, it's exactly what I need.
    For my future reference, Is "Weekday" part of SQL or Access or is it a name that you chose? =Weekday([cDateOfRecord],7)

    I didn't see this anywhere in help or books.
    Vicky

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

    Re: Reports - Saturday through Sunday (2002)

    Weekday is a VBA function; if you type it in the Visual Basic Editor (for example in the Immediate window) and press F1, you'll get help about this function.

    Access allows you to use VBA functions (both built-in and user-defined) in queries.

Posting Permissions

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