From: jeromez on

The following code calcuates the difference in work days between two
dates:

Function BusinessDays(PosHireDate, RepDate) As Long
BusinessDays = (DateDiff("d", PosHireDate, RepDate) - _
(DateDiff("ww", PosHireDate, RepDate) * 2) + 1) + _
(Weekday(PosHireDate) = vbSunday) + _
(Weekday(RepDate) = vbSaturday)
End Function

Date 1 = [PosHireDate] or Position Hire Date
Date 2 = [RepDate] or Report Date

when I execute my query I get "#ERROR" for those records where the
[PosHireDate} is 'blank" or null

My question is,

what is the best way to handle this?

I know the solution is to have a date for each record in the
[PosHireDate], but what about if someone accidently forgets - I still
want it to run, but may have the word "missing" or some huge number
like "1,000" show up.

Thanks!
From: timmg on
On Dec 22, 10:25 am, jeromez <jeromezilin...(a)gmail.com> wrote:
> The following code calcuates the difference in work days between two
> dates:
>
> Function BusinessDays(PosHireDate, RepDate) As Long
> BusinessDays = (DateDiff("d", PosHireDate, RepDate) - _
> (DateDiff("ww", PosHireDate, RepDate) * 2) + 1) + _
> (Weekday(PosHireDate) = vbSunday) + _
> (Weekday(RepDate) = vbSaturday)
> End Function
>
> Date 1 = [PosHireDate] or Position Hire Date
> Date 2 = [RepDate] or Report Date
>
> when I execute my query I get "#ERROR" for those records where the
> [PosHireDate} is 'blank" or null
>
> My question is,
>
> what is the best way to handle this?
>
> I know the solution is to have a date for each record in the
> [PosHireDate], but what about if someone accidently forgets  - I still
> want it to run, but may have the word "missing" or some huge number
> like "1,000" show up.
>
> Thanks!

You can trap for a null: If isnull([PosHireDate]) then
BusinessDays = 1000


or you can use NZ: NZ([PosHireDate],Date()) is my favorite in that
it runs but produces an obvious error.


Good luck

Tim
From: jeromez on
On Dec 22, 11:35 am, timmg <tmillsgronin...(a)gmail.com> wrote:
> On Dec 22, 10:25 am, jeromez <jeromezilin...(a)gmail.com> wrote:
>
>
>
>
>
> > The following code calcuates the difference in work days between two
> > dates:
>
> > Function BusinessDays(PosHireDate, RepDate) As Long
> > BusinessDays = (DateDiff("d", PosHireDate, RepDate) - _
> > (DateDiff("ww", PosHireDate, RepDate) * 2) + 1) + _
> > (Weekday(PosHireDate) = vbSunday) + _
> > (Weekday(RepDate) = vbSaturday)
> > End Function
>
> > Date 1 = [PosHireDate] or Position Hire Date
> > Date 2 = [RepDate] or Report Date
>
> > when I execute my query I get "#ERROR" for those records where the
> > [PosHireDate} is 'blank" or null
>
> > My question is,
>
> > what is the best way to handle this?
>
> > I know the solution is to have a date for each record in the
> > [PosHireDate], but what about if someone accidently forgets  - I still
> > want it to run, but may have the word "missing" or some huge number
> > like "1,000" show up.
>
> > Thanks!
>
> You can trap for a null:  If isnull([PosHireDate]) then
>       BusinessDays = 1000
>
> or you can use NZ:  NZ([PosHireDate],Date())  is my favorite in that
> it runs but produces an obvious error.
>
> Good luck
>
> Tim- Hide quoted text -
>
> - Show quoted text -

Where would this line of code go in my original code:

[quote]
Function BusinessDays(PosHireDate, RepDate) As Long
> > BusinessDays = (DateDiff("d", PosHireDate, RepDate) - _
> > (DateDiff("ww", PosHireDate, RepDate) * 2) + 1) + _
> > (Weekday(PosHireDate) = vbSunday) + _
> > (Weekday(RepDate) = vbSaturday)
> > End Function
[/quote]