Results 1 to 10 of 10
  1. #1
    bonecone is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    25

    Question Getting the last and the next Friday


    How do I use Visual Basic to calculate the next and previous Friday of a given date? I'm sure there's a better way of doing this than using a loop.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    When you say Visual Basic, are you referring to the programming language or are you referring to Visual Basic for Application (VBA) used within the Microsoft Office product suite such as within Access?

    If you are indeed referring to Access, you technically do not need VBA to do the calculations you mention. The calculations can be done using an expression that in turn, utilizes some of Access' built-in functions (those same functions can be used in VBA as well).

    For example, to find the date of the next Friday given a date, the expression would look like this:

    yourdate represents the date you input
    =IIf(Weekday([yourdate])>5,DateAdd("d",7-(Weekday([yourdate])-6),[yourdate]),DateAdd("d",6-Weekday([yourdate]),[yourdate]))

    The above expression uses several built-in functions: IIF(), Weekday() and DateAdd(). You can learn more about each via Access' help section.

    I do not know anything about the Visual Basic programming language, so I can not help you if that is what you are really after.

  3. #3
    bonecone is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    25
    Thanks, but I need it to be in Access Visual Basic. I'm using the onChange event of a textbox. If I enter a date into the box, I want it to check and see if that date is a Friday and, if not, change it to the first Friday before the entered date. Then I want it to set a second textbox to the first Friday after that date

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What I showed can be used in Access Visual Basic (aka VBA) or in a query in Access or in a control on an Access form. It will yield the next Friday for any given date, so this will address your second text box question. In your code, you would substitute your the name of your first text box control in place of [yourdate] in the expression.

    You can adjust the expression I gave you to handle the change of the first textbox. You will need the same built-in functions. If you have not looked up the weekday() function, if you do not specify the starting day of the week, it is assumed to be Sunday (day 1), Friday is day 6 (hence the use of 6 in my expression). Since you are going to be using VBA code for the first textbox, you will need an IF..THEN

    IF weekday(me.firsttextbox)<>6 THEN
    me.firsttextbox=expression
    END IF

    I'll let you try the expression first. If you have trouble, please post back.

  5. #5
    bonecone is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    25
    Ah, okay thanks!

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I decided to play around with it. I think you will have to use the after update event of the textbox control. See the attached.
    Attached Files Attached Files

  7. #7
    bonecone is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    25
    Thanks for that! Yes I was having trouble using the onChange event and I switched over to the After Update event instead. But it still throws error messages about the invalid use of NULL (runtime error 94) under certain circumstances.

    I'm using a datasheet form that has a table as its recordsource. To simplify for now I've coded the from_date textbox like this

    Private Sub from_date_AfterUpdate()
    MsgBox (from_date.Value)
    End Sub

    I try to plan for everything the end user might do when I'm coding. If I delete the contents of the from_date field and then tab over to the to_date field, that's when I get the error message. When I try to catch the error, the event either fails to fire altogether or it executes everything both before and after the error label.

    The same thing happens when I try it in your form too. How can I set this up so that if I delete the contents of the from_date field it will delete the contents of the to_date field rather than throwing an error message?

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You will have to include code to check if the field is null (in the after update event as well)

    IF not isNull(me.from_date) then
    execute existing code
    ELSE
    me.to_date=null
    END IF

    BTW, if the to_date is always calculated based on the from_date, you would not store the to_date in your table. You would use the expression I gave you to detemine the to_date when you need it. The general rule is that calculated values should not be stored. Only under certain circumstances should they be stored--I have yet to run into one of those special circumstances.

  9. #9
    bonecone is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    25
    Good point, and thanks again!

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome

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

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