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.
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.
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.
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
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.
Ah, okay thanks!
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.
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?
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.
Good point, and thanks again!
You're welcome