How do i do a notnull statement? (If (IsNotNull(..) Then ...)

  • Thread starter Thread starter h3llz
  • Start date Start date
In VBA:

If Not IsNull (Me.SomeField) Then
' Do something
Else
' Do something else
End If

The Else part may not be needed, depending on the situation.

In the control source of a text box:

=IIf([SomeField] Is Not Null,"Not Null","Null")

In a query:

NewField: IIf([SomeField] Is Not Null,"Not Null","Null")

This will cause "Not Null" or "Null" to appear in the text box or query
field. Substitute the actual actions you would have performed.

In all cases use the actual field name. In the query you can name the field
as you choose. You don't need to use NewField.
 
I've got this code and it is always returning true, even when the field is
blank
If Not IsNull(txtSuppliedProductID.Text) Then
WHERE = " AND productID=" & txtSuppliedProductID.Text
Else
cmoSuppliedProductName.SetFocus
If Not IsNull(cmoSuppliedProductName.Text) Then
WHERE = " AND productID=" & cmoSuppliedProductName.Text
End If
End If
unless its not null, and is ""
BruceM said:
In VBA:

If Not IsNull (Me.SomeField) Then
' Do something
Else
' Do something else
End If

The Else part may not be needed, depending on the situation.

In the control source of a text box:

=IIf([SomeField] Is Not Null,"Not Null","Null")

In a query:

NewField: IIf([SomeField] Is Not Null,"Not Null","Null")

This will cause "Not Null" or "Null" to appear in the text box or query
field. Substitute the actual actions you would have performed.

In all cases use the actual field name. In the query you can name the field
as you choose. You don't need to use NewField.

h3llz said:
How do i do a notnull statement? (If (IsNotNull(..) Then ...)
Click to expand...
Click to expand...
 
To check for either Null or zero-length string (""), use

If Len(txtSuppliedProductID & vbNullString) > 0 Then
WHERE = " AND productID=" & txtSuppliedProductID.Text
Else
If Len(cmoSuppliedProductName & vbNullString) > 0 Then
WHERE = " AND productID=" & cmoSuppliedProductName.Text
End If
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


h3llz said:
I've got this code and it is always returning true, even when the field is
blank
If Not IsNull(txtSuppliedProductID.Text) Then
WHERE = " AND productID=" & txtSuppliedProductID.Text
Else
cmoSuppliedProductName.SetFocus
If Not IsNull(cmoSuppliedProductName.Text) Then
WHERE = " AND productID=" & cmoSuppliedProductName.Text
End If
End If
unless its not null, and is ""
BruceM said:
In VBA:

If Not IsNull (Me.SomeField) Then
' Do something
Else
' Do something else
End If

The Else part may not be needed, depending on the situation.

In the control source of a text box:

=IIf([SomeField] Is Not Null,"Not Null","Null")

In a query:

NewField: IIf([SomeField] Is Not Null,"Not Null","Null")

This will cause "Not Null" or "Null" to appear in the text box or query
field. Substitute the actual actions you would have performed.

In all cases use the actual field name. In the query you can name the
field
as you choose. You don't need to use NewField.

h3llz said:
How do i do a notnull statement? (If (IsNotNull(..) Then ...)
Click to expand...
Click to expand...
Click to expand...
 
There are a number of problems here. The text property applies only to a
control that has the focus. I see that you set the focus, but even if that
works it is unnecessary. There are few occasions to use the Text property.
This is not one of them.

You were comparing ProductID to ProductName in the second WHERE. I changed
it to ProductName. Also, note that I used a different arrangement of quotes
for the second WHERE, assuming ProductName is a text field. The same
arrangement would apply to ProductID if that is a text field.

Regarding the WHERE, it is a reserved word, which could be part of the
problem. Did you define WHERE as a String? If not, you should do so, as I
have shown (with the "str" prefix). At the top of the code window, below
Option Compare Database, you should have Option Explicit. If it is not
there, add it. Then in the VBA editor click Tools >> Options >> Editor.
Check Require Variable Declaration. This will apply to future code modules.

Try this:

Dim strWHERE As String

If Not IsNull (Me.txtSuppliedProductID) Then
strWHERE = " AND ProductID = " & Me.txtSuppliedProductID
Else
If Not IsNull(Me.cmoSuppliedProductName) Then
strWHERE = " AND ProductName = """ & Me.cmoSuppliedProductName &
""""
End If
End If



h3llz said:
I've got this code and it is always returning true, even when the field is
blank
If Not IsNull(txtSuppliedProductID.Text) Then
WHERE = " AND productID=" & txtSuppliedProductID.Text
Else
cmoSuppliedProductName.SetFocus
If Not IsNull(cmoSuppliedProductName.Text) Then
WHERE = " AND productID=" & cmoSuppliedProductName.Text
End If
End If
unless its not null, and is ""
BruceM said:
In VBA:

If Not IsNull (Me.SomeField) Then
' Do something
Else
' Do something else
End If

The Else part may not be needed, depending on the situation.

In the control source of a text box:

=IIf([SomeField] Is Not Null,"Not Null","Null")

In a query:

NewField: IIf([SomeField] Is Not Null,"Not Null","Null")

This will cause "Not Null" or "Null" to appear in the text box or query
field. Substitute the actual actions you would have performed.

In all cases use the actual field name. In the query you can name the
field
as you choose. You don't need to use NewField.

h3llz said:
How do i do a notnull statement? (If (IsNotNull(..) Then ...)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top