Results 1 to 3 of 3
  1. #1
    1Giggles is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    3

    Format$ date...

    Access 2007 SP 3, Windows XP Version 2002 SP3

    I have a calculated field ([SendDate]) in a query that calculates a specific date by adding 10 days to it ([DOD]+10). The report based on this query is grouped by month by week on SendDate: Format$([SendDate], "mmm", 0,0)




    The report is then sorted by [SendDate], then by [DOD], and last by [Pt].

    On the printed report each week is separated nicely. However, I'd like for it to show Saturday as the first day of the grouping (instead of the last day of the grouping). How do I accomplish this without too much code? Isn't there a simple way of doing this on the "Design View" layout of the form?

    Thanks so much!

  2. #2
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Not completely sure of the syntax.
    format(weekday([SendDate],7),"mmm,0,0)

    Also the $ is for currency and should not be needed.

    Dale

  3. #3
    1Giggles is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    3
    Thank you for your response. Aside from the end quote missing after mmm, now the month shows as Jan instead of Apr or May like it should... Thus, that is not what I was looking for but I think I left out mentioning earlier -- I have a column for the day of the week when I need to mail something related to the record. I wanted the date to stay the same and change only that the group dates begin with Sat. and not Sun. For instance, where my report usually broke it down was like below (assume that a double space between represents a new grouping week like my report does it):

    Month Mail SendDate MRNo DOD
    Apr Tue 4/23/13 161139 4/13/13
    Thu 4/25/13 244734 4/15/13
    Sat 4/27/13 082640 4/17/13

    Apr Mon 4/29/13 243299 4/19/13
    May Thu 5/2/13 156752 4/22/13

    What I'd like to see in the report is that Sat. row brought to the beginning of the next week. Is this clearer now? Thanks for any assistance.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-12-2013, 06:49 PM
  2. date format
    By slimjen in forum Reports
    Replies: 15
    Last Post: 01-19-2012, 07:29 PM
  3. Date field not in date format
    By Lorlai in forum Access
    Replies: 11
    Last Post: 06-27-2011, 05:14 PM
  4. Date Format
    By lonewolfwfk in forum Programming
    Replies: 4
    Last Post: 11-25-2010, 09:00 PM
  5. date format
    By dollygg in forum Access
    Replies: 2
    Last Post: 01-19-2010, 10:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums