Software

Ask something
Discussion Locked

help - I am supporting a ACCESS db app

0 helpful
3 replies

by dfoubert 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have been asked to maintain an ACCESS app and I'm learning as I go - BUT being new to VBA I understand that with error and subsequent debug = 'invalid use of Null (error 94)' that a null is involved in an equation which always will gives null as return value
line 'OldDate = ....' in code following --
Public Function DateChanged(NewDate As Date) As Boolean
Dim OldDate As Date
Dim AvailableUSOnCall As String
DateChanged = False

Set dbs = CurrentDb
'11/18/02/df AvailableUSOnCall = "SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Available, tblEmployees.Canadian, tblEmployees.OnCallStartDate " _
& "FROM tblEmployees WHERE (((tblEmployees.Available)=Yes) AND ((tblEmployees.OnCall)=Yes) and ((tblEmployees.Canadian)=No));"
AvailableUSOnCall = "SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Available, tblEmployees.GC, tblEmployees.OnCallStartDate " _
& "FROM tblEmployees WHERE (((tblEmployees.Available)=Yes) AND ((tblEmployees.OnCall)=Yes) AND ((tblEmployees.GC)=yes));"

Set rstOnCall = dbs.OpenRecordset(AvailableUSOnCall, dbOpenDynaset)
OldDate = rstOnCall!OnCallStartDate

IfNewDate <> OldDate Then
DateChanged = True
End If

End Function
--- pre thanks - dfoubert

3 replies

by glen_mcleod

Try this:

OldDate = Iif(IsNull(rstOnCall!OnCallStartDate), 0, rstOnCall!OnCallStartDate)

This will put the value of 0 (zero) into your date variable IF the the field contains a null, and if it contains a date, then it will put the date into your variable.

Here it is a little more readably:

If Not IsNull(rstOnCall!OnCallStartDate) Then
OldDate = rstOnCall!OnCallStartDate
Else
OldDate = 0
Endif

In VB or VBA, only Variant variables can contain a Null, but they're a fact of life with databases.

Glen

This discussion is closed to new replies.