Query, empty or null field value

[Guest]

My query results do not return the records when there is an empty or null
value in any one field. Below is the my query. How would I modify this allow
the empty or null values. Thanks for yoru help.


strSQL = "SELECT * "
strSQL = strSQL & "FROM Posts INNER JOIN Signs ON Posts.PostID =
Signs.PostID "
strSQL = strSQL & "WHERE (((SIGNS.SignType) like """ & Me![cboSignType] &
""") "
strSQL = strSQL & " AND ((Signs.SignID) Like """ & Me![cboSignID] & """) "
strSQL = strSQL & " AND ((Signs.SignSize) Like """ & Me![cboSignSize] & """)"
strSQL = strSQL & " AND ((Signs.SignDescription) Like """ & Me![cboSignDesc]
& """))"

 

[Jason Lepack]

My query results do not return the records when there is an empty or null
value in any one field. Below is the my query. How would I modify this allow
the empty or null values. Thanks for yoru help.

strSQL = "SELECT * "
strSQL = strSQL & "FROM Posts INNER JOIN Signs ON Posts.PostID =
Signs.PostID "
strSQL = strSQL & "WHERE (((SIGNS.SignType) like """ & Me![cboSignType] &
""") "
strSQL = strSQL & " AND ((Signs.SignID) Like """ & Me![cboSignID] & """) "
strSQL = strSQL & " AND ((Signs.SignSize) Like """ & Me![cboSignSize] & """)"
strSQL = strSQL & " AND ((Signs.SignDescription) Like """ & Me![cboSignDesc]
& """))"

On each line you will have to add " OR (Signs.FieldName is Null)"

 

[Guest]

Perfect! Thanks for such a percise answer.

My query results do not return the records when there is an empty or null
value in any one field. Below is the my query. How would I modify this allow
the empty or null values. Thanks for yoru help.

strSQL = "SELECT * "
strSQL = strSQL & "FROM Posts INNER JOIN Signs ON Posts.PostID =
Signs.PostID "
strSQL = strSQL & "WHERE (((SIGNS.SignType) like """ & Me![cboSignType] &
""") "
strSQL = strSQL & " AND ((Signs.SignID) Like """ & Me![cboSignID] & """) "
strSQL = strSQL & " AND ((Signs.SignSize) Like """ & Me![cboSignSize] & """)"
strSQL = strSQL & " AND ((Signs.SignDescription) Like """ & Me![cboSignDesc]
& """))"

On each line you will have to add " OR (Signs.FieldName is Null)"

 

[Douglas J. Steele]

Incidentally, there's no point using LIKE, since you're not using wildcard
characters.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Perfect! Thanks for such a percise answer.

My query results do not return the records when there is an empty or
null
value in any one field. Below is the my query. How would I modify this
allow
the empty or null values. Thanks for yoru help.

strSQL = "SELECT * "
strSQL = strSQL & "FROM Posts INNER JOIN Signs ON Posts.PostID =
Signs.PostID "
strSQL = strSQL & "WHERE (((SIGNS.SignType) like """ & Me![cboSignType]
&
""") "
strSQL = strSQL & " AND ((Signs.SignID) Like """ & Me![cboSignID] &
""") "
strSQL = strSQL & " AND ((Signs.SignSize) Like """ & Me![cboSignSize] &
""")"
strSQL = strSQL & " AND ((Signs.SignDescription) Like """ &
Me![cboSignDesc]
& """))"

On each line you will have to add " OR (Signs.FieldName is Null)"

 

[Guest]

Actually, the default values for each of the comboboxes are a wildcards.

Incidentally, there's no point using LIKE, since you're not using wildcard
characters.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Perfect! Thanks for such a percise answer.

On Mar 22, 3:48 pm, finster26 <[email protected]>
wrote:
My query results do not return the records when there is an empty or
null
value in any one field. Below is the my query. How would I modify this
allow
the empty or null values. Thanks for yoru help.

strSQL = "SELECT * "
strSQL = strSQL & "FROM Posts INNER JOIN Signs ON Posts.PostID =
Signs.PostID "
strSQL = strSQL & "WHERE (((SIGNS.SignType) like """ & Me![cboSignType]
&
""") "
strSQL = strSQL & " AND ((Signs.SignID) Like """ & Me![cboSignID] &
""") "
strSQL = strSQL & " AND ((Signs.SignSize) Like """ & Me![cboSignSize] &
""")"
strSQL = strSQL & " AND ((Signs.SignDescription) Like """ &
Me![cboSignDesc]
& """))"

On each line you will have to add " OR (Signs.FieldName is Null)"