Question: Calculating working days when some fields are blank

[Eric D. Braden]

First post here (and somewhat new to Access), so let me know if I
should post this question elsewhere. (I'm unsure what daovba is)

My colleague and I are trying to calculate the number of days between
two events. We're using the code found here: http://www.mvps.org/access/datetime/date0006.htm
(the second example). There are two categories, though, and one of
them doesn't have the "startdate." It works just fine for the one
category (Advertised) which has the startdate (Closing Date) and the
end date. However, the other category (Continuous) has no value in
the startdate field and returns #error. We'd like it to return a
value of zero.

I'm probably being a little unclear In our table, one of the
fields is "Type." The type "Advertised" has a Closing Date
(startdate), while the type "Continuous" does not, the field is
empty. Is there a default value we should go with? I'm not a huge
fan of how tacky bogus dates look (like 01/01/1901 or 12/31/2099), is
there another way to do it?

We're trying to tinker with it but are inexperienced. The best I've
come up with so far is plugging the following in before the If
statements, but it doesn't work:

If Weekday(StartDate) <= 0 Then WorkingDays2 = 0
Else

Any suggestions?

 

[Douglas J. Steele]

Either change the declaration of StartDate in the function from Date to
Variant and add a check:

If IsNull(StartDate) Then
WorkingDays2 = 0
Exit Function
End If

at the start of the routine, or else call it as WorkingDays2(Nz([startdate],
Date()), [DateField])

 

[Eric D. Braden]

Either change the declaration of StartDate in the function from Date to
Variant and add a check:

If IsNull(StartDate) Then
WorkingDays2 = 0
Exit Function
End If

at the start of the routine, or else call it as WorkingDays2(Nz([startdate],
Date()), [DateField])

Worked like a charm! Thanks! I used your second recommendation. Can
you explain it a bit?

My first assumption is that Nz is making it ignore startdate if no
value and pick today's date instead, causing it to not run the loop
(because today's date isn't/won't be lessthan/equal to enddate)

 

[Douglas J. Steele]

Either change the declaration of StartDate in the function from Date to
Variant and add a check:

If IsNull(StartDate) Then
WorkingDays2 = 0
Exit Function
End If

at the start of the routine, or else call it as
WorkingDays2(Nz([startdate],
Date()), [DateField])

Worked like a charm! Thanks! I used your second recommendation. Can
you explain it a bit?

My first assumption is that Nz is making it ignore startdate if no
value and pick today's date instead, causing it to not run the loop
(because today's date isn't/won't be lessthan/equal to enddate)

That's correct. See
http://msdn2.microsoft.com/en-us/library/aa172237(office.11).aspx for
details about the Nz function (don't worry if you're not using Access 2003:
it's the same in all versions that have it)

 

[Eric D. Braden]

Either change the declaration of StartDate in the function from Date to
Variant and add a check:
If IsNull(StartDate) Then
WorkingDays2 = 0
Exit Function
End If
at the start of the routine, or else call it as
WorkingDays2(Nz([startdate],
Date()), [DateField])

Worked like a charm! Thanks! I used your second recommendation. Can
you explain it a bit?

My first assumption is that Nz is making it ignore startdate if no
value and pick today's date instead, causing it to not run the loop
(because today's date isn't/won't be lessthan/equal to enddate)

That's correct. Seehttp://msdn2.microsoft.com/en-us/library/aa172237(office.11).aspxfor
details about the Nz function (don't worry if you're not using Access 2003:
it's the same in all versions that have it)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)- Hide quoted text -

- Show quoted text -

Very helpful and thanks for the link.