Assuming that AreaID is the Number field, when it is Null the code creates
just:
(AreaID = )
which is clearly not correctly formed.
To avoid that, you could use IsNull() to test for null before generating the
string, or use Nz() to assign a zero for null.
The most useful idea will probably be to ignore the field if the user did
not enter a criterion. The following example shows how to concatenate as
many of these together as you need by adding " AND " to the end of each one,
and then chop off the trailing " AND ".
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.cbArea) Then 'Number field
strWhere = strWhere & "([AreaID] = " &Me.cbArea & ") AND "
End If
If Not IsNull(Me.cbUnit) Then 'Text field
strWhere = strWhere & "(UnitID = """ & Me.cbUnit & """) AND "
End If
'etc for other controls if you need them.
Now chop off the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left(strWhere, lngLen)
End If
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Joel said:
Ok here is another thing I came across.
When one field is null, i get an error "extra ) in query"
Any idea on null fields or a way to search by all in one field and some
criteria in another?
EX: Area= 1 and Unit= Null; this should return all units in area 1
Ex2:Area= 1 and Unit= C; would return area 1 only unit C
Thanks
Allen Browne said:
Try:
stWhere = "([AreaID] = " & [Forms]![MyForm]![cbArea] & _
") AND ([UnitID] = " & [Forms]![MyForm]![cbUnit] & ")"
If these are Text fields, (not Number fields), you need extra quotes:
stWhere = "([AreaID] = """ & [Forms]![MyForm]![cbArea] & _
""") AND ([UnitID] = """ & [Forms]![MyForm]![cbUnit] & """)"
Joel said:
I need to have 2 criterias for my report.
EX: [AreaID]=[Forms]![MyForm]![cbArea] &
[UnitID]=[Forms]![MyForm]![cbUnit]
I have this vb code for when you click the preview report button on the
form:
Me.FilterOn = True
stWhere = "[AreaID]=" & "[Forms]![MyForm]![cbArea]" & "[UnitID]=" &
"[Forms]![MyForm]![cbUnit]"
stDocName ="MST"
DoCmd.OpenReport stDoc, acPreview, ,stWhere
Any idea how to get this filter to use 2 criterias?
