Invalid use of Null

[penfold1992]

to test my database I am purposely making things fail some tests that I have in vba in order to get some of the error messages to pop up but I wasnt expecting this one...

"Invalid use of Null"

it appears somewhere here:

nDateDiff = intervaldate(strDateComplete, strDateReceived)
If (nDateDiff < 0) Then
MsgBox ("Completion date can not be before date received.")
strDateComp = Null
strDateComplete = "NULL"
InsertFlag = False
End If
nDate2Diff = TestDates(strDateComp, strAuthourisedDateTime)
If (nDate2Diff >= 0) Then
MsgBox ("Completion date can not be before Authorised Date.")
strDateComp = Null
strDateComplete = "NULL"
InsertFlag = False
End If
End If

because I get the message after the message box "Completion date can not be before authorised date"

why do I get this message?

 

[DJkarl]

to test my database I am purposely making things fail some tests that I have in vba in order to get some of the error messages to pop up but I wasnt expecting this one...

"Invalid use of Null"

it appears somewhere here:

nDateDiff = intervaldate(strDateComplete, strDateReceived)
If (nDateDiff < 0) Then
MsgBox ("Completion date can not be before date received.")
strDateComp = Null
strDateComplete = "NULL"
InsertFlag = False
End If
nDate2Diff = TestDates(strDateComp, strAuthourisedDateTime)
If (nDate2Diff >= 0) Then
MsgBox ("Completion date can not be before Authorised Date.")
strDateComp = Null
strDateComplete = "NULL"
InsertFlag = False
End If
End If

because I get the message after the message box "Completion date can not be before authorised date"

why do I get this message?

based on the variable names I'm guessing that strDateComp is a string, in that case the line of code

strDateComp = Null

will fail because in VBA you cannot set a string equal to null, you can set it equal to Empty, or a zero length string "", but not null.

 

[penfold1992]

strDateComp = Null is to set a field on the form to blank.
the error should appear as the message box and then set the field to blank.
strDateComp is the field strDateComplete string which is defined.

If (IsNull(strDateComp.Value) = False) And (strDateComp <> "") Then
    strDateComplete = strDateComp.Value
Else
    strDateComplete = "NULL"
End If

strDateComplete is defined with

Dim strDateComplete As String

 

[penfold1992]

Im not sure why this is the reason but maybe someone can explain it for me.

If (strDateComplete <> "NULL") Then
nDateDiff = TestDates2(strDateComp, strDateRec)
If (nDateDiff > 0) Then
MsgBox ("Completion date can not be before date received.")
strDateComp = Null
strDateComplete = "NULL"
InsertFlag = False
End If
End If
If (strDateComplete <> "NULL") Then
nDate2Diff = TestDates(strDateComp, strAuthourisedDateTime)
If (nDate2Diff >= 0) Then
MsgBox ("Completion date can not be before Authorised Date.")
strDateComp = Null
strDateComplete = "NULL"
InsertFlag = False
End If
End If

splitting them up into two works and does not give the "incorrect use of null" error.

The first If statement I posted can have a few routes.
statement 1 - if no, move on and ignore that whole if statement.
statement 2 - if ndatediff >0 cause message
statement 3 - if ndate2diff >= 0 then cause message

if the input satisfies all of the clauses it doesnt like it.
so instead ive made it question twice. its hard to explain what i mean i guess.

 

[Solo712]

strDateComp = Null is to set a field on the form to blank.
the error should appear as the message box and then set the field to blank.
strDateComp is the field strDateComplete string which is defined.

If (IsNull(strDateComp.Value) = False) And (strDateComp <> "") Then
    strDateComplete = strDateComp.Value
Else
    strDateComplete = "NULL"
End If

strDateComplete is defined with

Dim strDateComplete As String

Hey, it's ok to be stubborn ! The problem is, sometimes you don't solve your problem that way !

BTW, the best way to test if your field contains a valid date in Access would be :

If IsDate(strDateComp) Then

http://www.techonthenet.com/access/functions/advanced/isdate.php

Best,
Jiri