Invalid Use of Null

[Guest]

I have a txt file that I import into an Access table. After it is in the
table I need to compare the above record to the below record for differences.
I am having trouble comparing a field if it's blank or Null. If both the
current record and the next record IsNull I can skip those two records.
However, if the current record IsNull and the next record isn't Null or vice
versa, I would need to update the records. This has to do with parts being
an option or standard in a build. They can switch from standard or optional
or the other direction as well as having price changes or qty changes.

This is what I have so far and get the error message "Invalid use of Null" -
I should also state that this txt file comes from an ancient system that I
have no control over and can't change.



DoCmd.Hourglass (True)
Set db = CurrentDb()
Set rs = db.OpenRecordset("MM877PF5")

Dim strDate, strTime, strUser, strChangeType, strBfrAccy, strAftAccy As
String

rs.MoveFirst
Do While Not rs.EOF
strChangeType = rs!ChangeType

If strChangeType = "Before" Then GoTo 500
If strChangeType = "DELETED" Then GoTo 600
If strChangeType = "ADDED" Then GoTo 700
400
Loop
GoTo 800

500 'Move data from following "After" lines up to "Before" line
rs.MoveNext
strDate = rs!Date
strTime = rs!Time
strUser = rs!User
rs.MovePrevious
rs.Edit
rs!Date = strDate
rs!Time = strTime
rs!User = strUser
rs.Update

'did part change from basic to accy or vice-versa?
strBfrAccy = rs!GrpAccy: rs.MoveNext: strAftAccy = rs!GrpAccy:
rs.MovePrevious

If IsNull(strBfrAccy) And IsNull(strAftAccy) Then
rs.MoveNext
rs.MoveNext
Else
If strBfrAccy <> strAftAccy Then
rs.Edit: rs!ChangeType = "DELETED": rs.Update
rs.MoveNext
rs.Edit: rs!ChangeType = "ADDED": rs.Update
rs.MoveNext
End If
End If

GoTo 400

600
rs.MoveNext
GoTo 400
700
rs.MoveNext
GoTo 400

800
DoCmd.Hourglass (False)
Exit Sub

End Sub

 

[Brian]

I have a txt file that I import into an Access table. After it is in the
table I need to compare the above record to the below record for differences.
I am having trouble comparing a field if it's blank or Null. If both the
current record and the next record IsNull I can skip those two records.
However, if the current record IsNull and the next record isn't Null or vice
versa, I would need to update the records. This has to do with parts being
an option or standard in a build. They can switch from standard or optional
or the other direction as well as having price changes or qty changes.

This is what I have so far and get the error message "Invalid use of Null" -
I should also state that this txt file comes from an ancient system that I
have no control over and can't change.



DoCmd.Hourglass (True)
Set db = CurrentDb()
Set rs = db.OpenRecordset("MM877PF5")

Dim strDate, strTime, strUser, strChangeType, strBfrAccy, strAftAccy As
String

rs.MoveFirst
Do While Not rs.EOF
strChangeType = rs!ChangeType

If strChangeType = "Before" Then GoTo 500
If strChangeType = "DELETED" Then GoTo 600
If strChangeType = "ADDED" Then GoTo 700
400
Loop
GoTo 800

500 'Move data from following "After" lines up to "Before" line
rs.MoveNext
strDate = rs!Date
strTime = rs!Time
strUser = rs!User
rs.MovePrevious
rs.Edit
rs!Date = strDate
rs!Time = strTime
rs!User = strUser
rs.Update

'did part change from basic to accy or vice-versa?
strBfrAccy = rs!GrpAccy: rs.MoveNext: strAftAccy = rs!GrpAccy:
rs.MovePrevious

If IsNull(strBfrAccy) And IsNull(strAftAccy) Then
rs.MoveNext
rs.MoveNext
Else
If strBfrAccy <> strAftAccy Then
rs.Edit: rs!ChangeType = "DELETED": rs.Update
rs.MoveNext
rs.Edit: rs!ChangeType = "ADDED": rs.Update
rs.MoveNext
End If
End If

GoTo 400

600
rs.MoveNext
GoTo 400
700
rs.MoveNext
GoTo 400

800
DoCmd.Hourglass (False)
Exit Sub

End Sub

It would help if you indicated the line on which the error is occurring. I
must also point out that the use of GoTo and labels to control program flow
is very poor programming practice as it leads to "spaghetti" code, of which
this is a good example. It is never necessary to use GoTo, except in the
very specific case of an On Error statement. It is always possible to
achieve the same logic much more elegantly and readably using
control-of-flow constructs such as Do loops, For loops, If...Then and
Select...Case.

 

[Guest]

Hi,

There is a simple solution to this.
VBA has a function nz() this function will check whether the value is null
default it will change it to an empty string but you can also give it a
default value.

Examples

isnull(rs("Field")) => is true but you want to compare e.g. to a string
"hallo"

=> nz(rs("Field"))="hallo" will do the trick

isnull(rs("Field")) => is true but you want to compare e.g. to a long -1527

=> nz(rs("Field"),0)=-1527 will do the trick

- Raoul Jacobs