From: jeromez on 22 Dec 2009 11:25 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 22 Dec 2009 11:35 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 24 Dec 2009 08:29 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]
|
Pages: 1 Prev: Possible to stop "Unsafe Expression" warnings (Access 2003 and XP)? Next: this is very strange |