Invalid Use of Null 1

[ohmbru2]

I get the "Invalid Use of Null" error, but I don't understand where the null is comming from. The BeginDate and Enddate evaluate to valid dates.


Public Function Test(BeginDate As Date, EndDate As Date)

Dim BusDays As Integer

BusDays = DSum("[BusinessCalendar!BType]", "BusinessCalendar", "BusinessCalendar!BDate between " & BeginDate & " and " & EndDate)

 

[PHV]

Perhaps this ?
BusDays = Nz(DSum("BType", "BusinessCalendar", "BDate Between #" & BeginDate & "# And #" & EndDate & "#"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ohmbru2]

Thanks, that did the trick.

 

[KornGeek]

I would take this a step further to insure that you don't have future problems if one of those dates eventaully does evaluate as a null.

(Building of PHV's excellent suggestion):

BusDays = Nz(DSum("BType", "BusinessCalendar", "BDate Between #" & Nz(BeginDate, #1/1/1980#) & "# And #" & Nz(EndDate, #12/31/9999#) & "#"), 0)

 

[JoeAtWork]

KornGeek - I can't see that as being good solution because it could hide bugs and produce unexpected results.

Let's say one of those dates is null. What will happen is the user will be blissfully unaware there is a problem - but will have inaccurate results.

Better to have a big old nasty error message and at least become aware that the date variables are not being initilized.

 

[KornGeek]

Joe,

while I understand your point of view, I have to disagree with you. In my opinion, if it is bad to have null values, you should screen them out and display a nifty error message of your own rather than display a system error. If it is not bad to have them, they should default to some value that makes sense (for instance, if no end date is specified, I was using an absurdly large date to ensure that there was no end).

In my experience, if something is in error, it is always better to notify the end-user with your own message rather than let them see the system errors. These tend to make them panic.

 

[JoeAtWork]

Yes, I am in agreement with you, any variables that might be invalid should be tested before they are used, and a user-friendly message displayed. What concerned me about the code was that there were no validity checks, but rather the replacement of invalid variables with seemingly arbitrary values.

I grant you that depending on the context of the code it may be perfectly legitimate to assume a null value can be replaced with some default. There wasn't really enough info from the original post to determine if that is so in this case. I choose to interpret null as being invalid.

Understand that my opposition to handling errors by assigning arbitrary values comes from having seen the chaos that can be caused by this strategy. An application I inherited had "On Error Resume Next" at the beginning of some key functions for calculating amount due. I can only guess the original programmer was tired of seeing the periodic errors that were resulting from his poorly structured code. The upshot was that for months the company was charging its customers less than it should have, because these functions were often returning zero when an unhandled error occurred.