Dates and Invalid Use of Null

[beanbag]

Hey guys, it's been a while since I was last here. Personal issues kept me otherwise occupied. (prev. known as startingover).

ANYhoo...I'm having a dandy of a time with one line in this code. I'm getting error on line 20, error 94 - invalid use of null.
Now, I have looked all over creation on here and other sites and all I seem to find even remotely close is all SQL. Don't want SQL. Don't need SQL. I love SQL but only where it's needed. Not for this.
It's something simple I just know it is.
On any of the comparisons, it works just fine, but when the date is a null value b/c it hasn't been put in yet, I get the above listed error on line 20. I've tried converting it but to no avail. It is only the year value but the current year and the recorded year need to match. Again, the issue is when comparing to a blank date, it throws an error. I do have a workaround but it's really not my favorite idea.

Private Sub HandleDate()
10 On Error GoTo HandleDate_Err

20 Me.txtRecordedYear = Nz(Year(Me.W4Date))
30 Me.txtCurrentDateYear = Year(Date)

40 If Me.NewRecord Then
50 Me.txtCurrentDateYear = Year(Date)
60 Me.lblw4Year.Caption = " - New"
70 Me.lblw4Year.ForeColor = RGB(164, 213, 226)
80 End If

90 If Me.Verified = -1 And Me.txtRecordedYear = Me.txtCurrentDateYear Then
100 Me.lblw4Year.Caption = " - " & Me.txtRecordedYear
110 Me.lblw4Year.ForeColor = RGB(164, 213, 226)
120 End If

130 If Me.Verified = 0 And Me.txtRecordedYear <> Me.txtCurrentDateYear Then
140 Me.lblw4Year.Caption = " - " & Me.txtRecordedYear
150 Me.lblw4Year.ForeColor = vbRed
160 End If

HandleDate_Exit:
170 Exit Sub

HandleDate_Err:
180 Select Case Err.Number
Case 94
190 MsgBox "Error at line #: " & Erl & vbCrLf & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Location: Private Sub HandleDate()"
200 Case 2501
210 MsgBox "some message"
220 Case Else
230 MsgBox "Error at line: " & Erl & " " & Err.Number & "--" & Err.Description
240 End Select

250 Resume HandleDate_Exit
End Sub

Thx guys.

 

[June7]

Consider:

Nz(Year(Null))

returns an empty string because no value to substitute for Null is specified and Nz() defaults to empty string.

Therefore, I am surprised you get that particular error message. If txtRecordedYear is bound to a number field, it cannot accept an empty string. Don't use Nz() because Year(Null) will return Null and field can accept Null.

Line 20 is not doing a comparison, it is setting value of textbox.

Side note, please post code between CODE tags to retain indentation and readability.

 

[The_Doc_Man]

First thing to do is put a breakpoint on line 20. When it breaks, it will do so BEFORE executing the line in question. So the error condition has not yet been detected and you can examine some things.

Normally, if you hover the mouse cursor over a variable or a control reference that can have a value, it will tell you what is in that "hovered" object. If that is a bound control, Me.W4Date CAN be null. (And I'm betting that it is, in fact, null.) If so, then Year(Me.W4Date) is seeing a null and that is your error 94. Year(x) is INSIDE the NZ so isn't protected by it. Had you done Year(NZ(Me.W4Date)) then even though you had no value for the NZ fall-back, you would have not seen the error because the thing that was null was isolated inside the NZ where nothing else could see it. You would have seen a zero for the year, but not a null. (Or, considering that the Access internal year for time 0 is 1899, you might have gotten that instead.)

 

[Gasman]

You can also test if it is a date before using it?

 

[gemma-the-husky]

Try this variation. I think you need the nz around the W4date field

Me.txtRecordedYear = Year(nz(Me.W4Date,0))

 

[beanbag]

Thank you Doc_Man, that was just the ticket. I don't know why I couldn't find that but I knew someone brilliant in here could do it. I actually thought about it that way and it just seemed in my mind that that wouldn't work. Doc_Man is DE MAN. Also, thank you gemma-the-husky. Your answer being the same is of course right though explain to me the purpose of putting the '0' in there? I've seen that done in several different places, mostly dlook-ups if I remember right. I thought it was always 'if you don't find anything there, make it a 0 so you have something to compare. I even tried that hoping that was what it would do, but alas, I had the year and nz reversed. Thanks again to all.

 

[June7]

See post #2. Without specifying 0 Nz() will default to empty string.

 

[The_Doc_Man]

Here is an explanation: The NZ function, like most VBA functions that don't have an explicit type associated with them, returns a Variant data type - which can be anything. The 2nd argument of NZ is what you want it to return if the 1st argument was really a null. If you want a specific value in the case of nulls, that goes in the 2nd argument. Since you are returning a Variant, it can be anything including a date/time value, an integer, a floating-point number, or a string. But the default for that 2nd argument is an empty string. If that isn't what you wanted, then specify something.

 

[Pat Hartman]

Since Nz() returns "" or 0 when no default has been specified (it makes an assumption based on context), it is really much better to always be specific so you won't be surprised. Plus, the rules are different when Nz() is used in a query than when it is used in VBA.

Nz Function - Microsoft Support

Syntax: Nz(variant [, valueifnull ] )

support.microsoft.com

Surprisingly, if you use Nz(SomeNumericField) in a query without specifying the default value, it returns a ZLS under some conditions rather than raising an error. Who knows what problems that will cause.

The most common use in a query is Nz(fld1) + Nz(fld2) + Nz(fld3)
In this context, Access works out that even though you omitted the default, you want a 0 returned as the function name implies but that fools people into thinking that it will always return 0 but as you can see, the help entry tells you it does not.