Solved Access gives Null error

[R-Berg]

I have the following code that does not seem to work.
I am generating a Word dokument without any problems, but any empty textbox generates a Null error.

To prevent this i thought of using this code, but it is not solving the issue.
It just seems to jump over thevbNullString part.

Any tips?

       If Me.MaxBar <> vbNullString Then
                 MsgBox "Field shall contain a value"
                 Exit Sub
                 Else
        FindText = "[PdX]"
        ReplaceWith = Me.MaxBar
        .Execute FindText:=FindText, ReplaceWith:=ReplaceWith, Replace:=2
           End If

 

[arnelgp]

can you try:

If IsNull(Me.MaxBar) = False Then
'''
...

 

[cheekybuddha]

OR test both Null and empty string in one go:

      If Len(Me.MaxBar & vbNullString) = 0 Then
        MsgBox "Field shall contain a value"
        Exit Sub
      Else
        FindText = "[PdX]"
        ReplaceWith = Me.MaxBar
        .Execute FindText:=FindText, ReplaceWith:=ReplaceWith, Replace:=2
      End If

 

[The_Doc_Man]

Any tips?

The others have given you sample code. I'll explain the problem.

If Me.MaxBar <> vbNullString Then

All of <, =, and > and combinations thereof are comparisons that normally return TRUE or FALSE. The problem is that if ANYTHING is compared to a null, the result is not TRUE or FALSE - it is also null. Null overtakes and propagates its way through expressions unless you use functions or specific relationship keywords specifically targeted to work with nulls. The fact that your comparison DIDN'T use a null-catcher is why the IF barfed.

One more suggestion:

Since that is being used as text, this also would work in your context, comparing against "", the "empty" string:

IF NZ( Me.MaxBar, "" ) <> "" THEN

By the way - that was your first post. Hello and welcome to the forum.

 

[Pat Hartman]

To continue:
vbNullString = zero length string which is not the same as Null. Numeric and Date fields to not support vbNullString which is also represented by "" which is two double quotes with nothing between them so vbNullString = "" but it does not = Null. Also as Doc mentioned, you cannot use the = or any other relational operator successfully when one of the operands is Null. There are a couple of methods commonly used.

1. If Me.SomeField & "" = "" Then --- this handles both null and ZLS because when you concatenate a field that is null with a ZLS, you end up with a ZLS so if SomeField was originally Null it will test true to being equal to the ""
2. If Len(Me.Somefield) > 0 --- works for ZLS only
3. If Nz(Me.Somefield, "") = "" --- works for null only
4. If Me.SomeField = "" --- works for ZLS only
5. If Me.SomeField = vbNullString --- works for ZLS only

ZLS only applies to text data types. ie "strings". It also defeats the purpose of defining a text data type as required since "" will be considered OK and that is probably not what you anticipated. So, if a text field is required, you MUST set the AllowZeroLengthString property to no. I always set it to No for all text fields. That allows me to set "empty" fields always to Null and expect "empty" fields to always be "null" and not "zls".

 

[Josef P.]

The check in the code from #1 works - only the logic is wrong.

If Me.MaxBar <> vbNullString Then ' => Me.MaxBar contain a value   (BTW: a string is never < vbNullString)
     MsgBox "Field shall contain a value"  ' <-- This should happen if the control does not contain a value, right?
     Exit Sub
Else
     FindText = "[PdX]"
     ReplaceWith = Me.MaxBar
     .Execute FindText:=FindText, ReplaceWith:=ReplaceWith, Replace:=2
End If

=> See #3 for correct code.
Or switch if blocks:

If Me.MaxBar > vbNullString Then ' => Me.MaxBar contain a value
     FindText = "[PdX]"
     ReplaceWith = Me.MaxBar
     .Execute FindText:=FindText, ReplaceWith:=ReplaceWith, Replace:=2
Else
     MsgBox "Field shall contain a value"  ' <-- This should happen if the control does not contain a value, right?
     Exit Sub
End If

Note:
If exit sub is used, you can write the Else part directly after the If.

    If Len(Me.MaxBar & vbNullString) = 0 Then
        MsgBox "Field shall contain a value"
        Exit Sub
    End If

    FindText = "[PdX]"
    ReplaceWith = Me.MaxBar
    .Execute FindText:=FindText, ReplaceWith:=ReplaceWith, Replace:=2

 

[R-Berg]

Thanks for all the explanations. I got it to work.