Results 1 to 11 of 11
  1. #1
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318

    Get the weekending date from today's date


    I'm trying to create a formula to retrieve the final day of the week depending upon the day the report is open. Here is the formula I used when it was in an excel sheet, where A1 is today's date. Can you tell me how to do this in Access?

    =IF(WEEKDAY(A1)=7,A1,A1+(7-WEEKDAY(A1)))

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Take a look at the attached db. It has many useful date calculations in it.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    it's not allowing me to use the database due to version conflicts

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Have you unzipped it. I know it seems a silly question to ask but I am using the same version as you.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    Yes and it opened but it popped up with errors and then went to the debugging screen

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Ok. Try this version:
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    it says I'm "missing or broken reference to the file MSCALOCX version 7.0. I'll click on it several time and it will open to the form screen. Then when I click on the form it tells me missing reference to 'utility.mda'. It give me the first error again. Then the form comes up with data in it, then it opens to the debugger.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    This is the function is used in that db.
    Code:
    Function tbLastDateW(dtmDate)
        '
        'Warning dtmDate must be a valid date
        '
        'Return the Last date of provided week
        '
        Dim intDayCnt As Integer
        
        intDayCnt = 7 - WeekDay(dtmDate, 1)
        tbLastDateW = DateAdd("d", intDayCnt, dtmDate)
    End Function
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    Thanks. I'll work on it next week. Have a great weekend

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Your welcome. Good luck
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    Here is the expression for Friday: Date() - WeekDay(Date()) + 7 for Saturday: Date() - WeekDay(Date()) + 8

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

Similar Threads

  1. Replies: 12
    Last Post: 04-26-2012, 04:01 AM
  2. OutputTo Macro Include today's date
    By Lorlai in forum Access
    Replies: 2
    Last Post: 09-27-2011, 01:42 PM
  3. Entering Today's Date
    By Alex Motilal in forum Access
    Replies: 5
    Last Post: 08-23-2011, 12:22 AM
  4. Set Calendar to default to today's date
    By RickM in forum Access
    Replies: 1
    Last Post: 02-22-2009, 04:51 AM
  5. query date that is five years older than today
    By cpsummer in forum Queries
    Replies: 2
    Last Post: 09-26-2007, 02:31 PM

Tags for this Thread

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