Return null values when using wild cards.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 13 Nov 2006 21:27 GMT

I'm using the following code to set up paramenters for a query. For some
reason, Like '*' isn't returning null values for this field although it is
for other fields. How can I change this code to read that if Len = 0 to
return records where Bldg is null as well? Thanks you much.

'Build Criteria string from Bldg Listbox
For Each VarItem In Me.cmdBldg.ItemsSelected
StrBldg = StrBldg & ",'" & Me.cmdBldg.ItemData(VarItem) & "'"
Next VarItem
If Len(StrBldg) = 0 Then
StrBldg = "Like'*'"
Else
StrBldg = Right(StrBldg, Len(StrBldg) - 1)
StrBldg = "IN(" & StrBldg & ")"
End If

Reply to this Message

Klatuu - 13 Nov 2006 22:17 GMT

Change:
StrBldg = "Like'*'"
To:
StrBldg = "IS NULL OR Like'*'"

> I'm using the following code to set up paramenters for a query. For some
> reason, Like '*' isn't returning null values for this field although it is
[quoted text clipped - 11 lines]
> StrBldg = "IN(" & StrBldg & ")"
> End If

Reply to this Message

John Spencer - 14 Nov 2006 00:01 GMT

Since you want to return all records, why not leave strBldg out of the query's
where clause completely? IF Len(strBldg) = 0 then ...

Reply to this Message