Date Calc

[Secret Squirrel]

I have a field on my form called "HireDate". I need to add another field that
calculates the first business day of the year that the hire date is in. For
example if the hire date is 03/01/04 I need this new field to be 01/02/04.
How would I create this calculation?

 

[Al Campagna]

Secret,
Just build a loop that starts with the date of 01/02/yyyy. (assuming
the first is always a holiday)
and test if...
Weekday(01/02/yyyy)
it has a value >= 2 and <= 6 (Monday-Friday values).

If it doesn't, loop/add one day to the date... to 01/03/08, and test
that.
As soon as it the date yields a weekday number, Exit the Loop, and post
the date value returned.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[tina]

i suppose you could write a function to take the year of the hire date, add
1/2, so it's 1/1/xx, then run a loop to check for day of week and bump the
date by one day as needed until the check returns a weekday.

and btw, i assume when you said "field" in your post, you meant "control". a
hire date is hard data, of course, but the first business day of the same
year is a calculated value, and shouldn't be stored as hard data in a table
unless you have a sound business reason for doing so.

hth

 

[Secret Squirrel]

Hi Tina,

I acutally figured it out. Here's what I used to calculate the first
business day of the year the employee was hired.

=GetBusinessDay(DateSerial(Format([StartDate],"yyyy")+0,1,1),1)

And yes I meant to say control and not field.

 

[Douglas J. Steele]

Format returns a string, which Access then has to coerce back into a number.

You'd be better off using

=GetBusinessDay(DateSerial(Year([StartDate])+0,1,1),1)

or

=GetBusinessDay(DateSerial(DatePart("yyyy", [StartDate])+0,1,1),1)

(although, to be honest, I doubt you'll notice any difference in
performance!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Tina,

I acutally figured it out. Here's what I used to calculate the first
business day of the year the employee was hired.

=GetBusinessDay(DateSerial(Format([StartDate],"yyyy")+0,1,1),1)

And yes I meant to say control and not field.

i suppose you could write a function to take the year of the hire date,
add
1/2, so it's 1/1/xx, then run a loop to check for day of week and bump
the
date by one day as needed until the check returns a weekday.

and btw, i assume when you said "field" in your post, you meant
"control". a
hire date is hard data, of course, but the first business day of the same
year is a calculated value, and shouldn't be stored as hard data in a
table
unless you have a sound business reason for doing so.

hth

 

[tina]

okay, SS and Doug, you both did me one better (and Al, too), but i didn't
find GetBusinessDay() as a built in function in A97 or A2000-2003. share?

btw, SS, i've seen you post in these groups a lot, so i figured from your
skill level that you already knew the "don't store calculated data" rule.
but sometimes i think of other folks reading threads who may not be as
experienced, and speak to them, too. hope i didn't offend!

Hi Tina,

I acutally figured it out. Here's what I used to calculate the first
business day of the year the employee was hired.

=GetBusinessDay(DateSerial(Format([StartDate],"yyyy")+0,1,1),1)

And yes I meant to say control and not field.

i suppose you could write a function to take the year of the hire date, add
1/2, so it's 1/1/xx, then run a loop to check for day of week and bump the
date by one day as needed until the check returns a weekday.

and btw, i assume when you said "field" in your post, you meant "control". a
hire date is hard data, of course, but the first business day of the same
year is a calculated value, and shouldn't be stored as hard data in a table
unless you have a sound business reason for doing so.

hth


field
that in.
For

 

[Douglas J. Steele]

You're right that there's nothing built into VBA to calculate the next
business day. I assumed it was a custom-written VBA function that SS already
had.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

okay, SS and Doug, you both did me one better (and Al, too), but i didn't
find GetBusinessDay() as a built in function in A97 or A2000-2003. share?

btw, SS, i've seen you post in these groups a lot, so i figured from your
skill level that you already knew the "don't store calculated data" rule.
but sometimes i think of other folks reading threads who may not be as
experienced, and speak to them, too. hope i didn't offend!

Hi Tina,

I acutally figured it out. Here's what I used to calculate the first
business day of the year the employee was hired.

=GetBusinessDay(DateSerial(Format([StartDate],"yyyy")+0,1,1),1)

And yes I meant to say control and not field.

i suppose you could write a function to take the year of the hire date, add
1/2, so it's 1/1/xx, then run a loop to check for day of week and bump the
date by one day as needed until the check returns a weekday.

and btw, i assume when you said "field" in your post, you meant "control". a
hire date is hard data, of course, but the first business day of the same
year is a calculated value, and shouldn't be stored as hard data in a table
unless you have a sound business reason for doing so.

hth


message I have a field on my form called "HireDate". I need to add another field
that
calculates the first business day of the year that the hire date is in.
For
example if the hire date is 03/01/04 I need this new field to be 01/02/04.
How would I create this calculation?

 

[Secret Squirrel]

No offense taken Tina. We're all friends here.

okay, SS and Doug, you both did me one better (and Al, too), but i didn't
find GetBusinessDay() as a built in function in A97 or A2000-2003. share?

btw, SS, i've seen you post in these groups a lot, so i figured from your
skill level that you already knew the "don't store calculated data" rule.
but sometimes i think of other folks reading threads who may not be as
experienced, and speak to them, too. hope i didn't offend!

Hi Tina,

I acutally figured it out. Here's what I used to calculate the first
business day of the year the employee was hired.

=GetBusinessDay(DateSerial(Format([StartDate],"yyyy")+0,1,1),1)

And yes I meant to say control and not field.

i suppose you could write a function to take the year of the hire date, add
1/2, so it's 1/1/xx, then run a loop to check for day of week and bump the
date by one day as needed until the check returns a weekday.

and btw, i assume when you said "field" in your post, you meant "control". a
hire date is hard data, of course, but the first business day of the same
year is a calculated value, and shouldn't be stored as hard data in a table
unless you have a sound business reason for doing so.

hth


message I have a field on my form called "HireDate". I need to add another field
that
calculates the first business day of the year that the hire date is in.
For
example if the hire date is 03/01/04 I need this new field to be 01/02/04.
How would I create this calculation?

 

[tina]

ah, okay.

You're right that there's nothing built into VBA to calculate the next
business day. I assumed it was a custom-written VBA function that SS already
had.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

okay, SS and Doug, you both did me one better (and Al, too), but i didn't
find GetBusinessDay() as a built in function in A97 or A2000-2003. share?

btw, SS, i've seen you post in these groups a lot, so i figured from your
skill level that you already knew the "don't store calculated data" rule.
but sometimes i think of other folks reading threads who may not be as
experienced, and speak to them, too. hope i didn't offend!

Hi Tina,

I acutally figured it out. Here's what I used to calculate the first
business day of the year the employee was hired.

=GetBusinessDay(DateSerial(Format([StartDate],"yyyy")+0,1,1),1)

And yes I meant to say control and not field.

:

i suppose you could write a function to take the year of the hire

date,
add

1/2, so it's 1/1/xx, then run a loop to check for day of week and

bump
the

date by one day as needed until the check returns a weekday.

and btw, i assume when you said "field" in your post, you meant "control". a
hire date is hard data, of course, but the first business day of the same
year is a calculated value, and shouldn't be stored as hard data in a table
unless you have a sound business reason for doing so.

hth


message I have a field on my form called "HireDate". I need to add another field
that
calculates the first business day of the year that the hire date is in.
For
example if the hire date is 03/01/04 I need this new field to be 01/02/04.
How would I create this calculation?