wildcards

[Guest]

I'm stumped on how to do this. I have 4 combo boxes on a form that are not
synched. The user needs to be able to choose one of the selections, part of
one, or leasve it blank for all. After completing the 4 criteria -a filter
query is run based on them to veiw the selections. . My criteria in the
query is :
Like "*" & [forms]![frmInvTransfer_main]![cbocellid] & " *" . This does
not return any records, I have also tried :
Like "*" & IIf([forms]![frmInvTransfer_main]![cbocellid] Is Null, "*",
([forms]![frmInvTransfer_main]![cbocellid])) Each combo box is named
"cbo-(name)". This gives me records only when
the exact choice is made or none. Help and I need to do this for 4 different
combo boxes! Any ideas? I'm working in Access 2000.

 

[Guest]

Try the following:

If IsNull(Me.cboname.Value) Then
strname = "Like '*'"
Else
strname = "='" & Me.cboname.Value & "'"
End If
Also, don't forget to
Dim strName As String
wish it helps.

Vivi

 

[Guest]

When you have the situation you described, you have to build a Where string
that filters on only the combos that have a selection. Here is some VBA code
that will build the string:

Dim strWhere As String

If Not IsNull(Me.Combo1) Then
strWhere = "[FirstField] = " & Me.Combo1
End If

If Not IsNull(Me.Combo2 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = "[SecondField] = " & Me.Combo2
End If

If Not IsNull(Me.Combo3 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = "[ThirdField] = " & Me.Combo3
End If

If Not IsNull(Me.Combo4 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = "[FourthField] = " & Me.Combo4
End If

Now your criteria is in a string filtering on only those combos that have a
value.

How you use it depends on how you are doing the filtering. If you can
describe what you do once the selections have been made, perhaps I can offer
a suggestion.

 

[Guest]

Thanks- let me try it and let you know!

When you have the situation you described, you have to build a Where string
that filters on only the combos that have a selection. Here is some VBA code
that will build the string:

Dim strWhere As String

If Not IsNull(Me.Combo1) Then
strWhere = "[FirstField] = " & Me.Combo1
End If

If Not IsNull(Me.Combo2 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = "[SecondField] = " & Me.Combo2
End If

If Not IsNull(Me.Combo3 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = "[ThirdField] = " & Me.Combo3
End If

If Not IsNull(Me.Combo4 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = "[FourthField] = " & Me.Combo4
End If

Now your criteria is in a string filtering on only those combos that have a
value.

How you use it depends on how you are doing the filtering. If you can
describe what you do once the selections have been made, perhaps I can offer
a suggestion.
--
Dave Hargis, Microsoft Access MVP

I'm stumped on how to do this. I have 4 combo boxes on a form that are not
synched. The user needs to be able to choose one of the selections, part of
one, or leasve it blank for all. After completing the 4 criteria -a filter
query is run based on them to veiw the selections. . My criteria in the
query is :
Like "*" & [forms]![frmInvTransfer_main]![cbocellid] & " *" . This does
not return any records, I have also tried :
Like "*" & IIf([forms]![frmInvTransfer_main]![cbocellid] Is Null, "*",
([forms]![frmInvTransfer_main]![cbocellid])) Each combo box is named
"cbo-(name)". This gives me records only when
the exact choice is made or none. Help and I need to do this for 4 different
combo boxes! Any ideas? I'm working in Access 2000.