SpaceCoastLife
asked on
How to Determine Odd or Even Occurrences
I have a table with about 1500 bi-weekly records and a requirement to determine if the repeat occurrence will be every odd week or every even week based on the start date.
How do I do that using Access vba?
How do I do that using Access vba?
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Btw, you could simply add 2 weeks with DateAdd(), but it sounds like it might need more control than that.
Jim.
Jim.
ASKER
Gustav: using your suggestion I end up with either 0 or 1. How do I translate that into odd or even? 0 = odd; 1 = even?
If it's 0, it's even (divisible by 2). If it's 1,it's odd. Modulus returns the remainder as a whole number. So:
1 mod 10 = 1
2 mod 10 = 2
...
9 mod 10 = 9
10 mod 10 = 0
11 mod 10 = 1
12 mod 10 = 2
and so on. so you want:
Format(Date(),"WW") Mod 2
which yields 0 for today (week 14), so it's an even week this week. Next week:
Format(Date()+7,"WW") mod 2
would yield 1
Jim.
1 mod 10 = 1
2 mod 10 = 2
...
9 mod 10 = 9
10 mod 10 = 0
11 mod 10 = 1
12 mod 10 = 2
and so on. so you want:
Format(Date(),"WW") Mod 2
which yields 0 for today (week 14), so it's an even week this week. Next week:
Format(Date()+7,"WW") mod 2
would yield 1
Jim.
ASKER
Thanks to you both!
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Jim.