Using OR in a search form

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

I have a search form with the following fields

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

Values can have any combination of the Yes/No fields.

Here is an example of a search:

Give me all values where State is CO and where Communication is Yes.

Text - Region - Blank
Text - Ship To - Blank
Text - State - CO
Yes/No - Communication - Yes
Yes/No - Performance - Blank
Yes/No - Fuel - Blank

The problem with this filter is all values that equal Yes for both
Communication and Performance do not appear in the results. I only get values
where Yes is under Communications and No on Performance. If I choose Yes for
both Communications and Performance then I don't see values where
Communications and Fuel are Yes and Performance is No. Is there a way to
filter it so rather than looking at Yes or No it can look at Yes, No or Null?

Here's the code I am using. I am not very familiar with writing code so as
much assistance to modifying this would be greatly appreciated! :)

Private Sub cmdFilter_Click()


If Me.ckboxFilterComm = -1 Then
strWhere = strWhere & "([Communications] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Communications] = False) AND "
End If

If Me.ckboxFilterPerf = -1 Then
strWhere = strWhere & "([Performance] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Performance] = False) AND "
End If

If Me.ckboxFilterFuel= -1 Then
strWhere = strWhere & "([Fuel] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Fuel] = False) AND "
End If


Thanks,
Melissa
 
Melissa,

Yes/No fields are either TRUE (checked) or False (unchecked). If you want
you filter to be able to distinguish between True, False, or No Selection,
then you need to use a control other than a checkbox. I generally use a
combo box with options of All Values, True, False. Then, in my code I'll do
something like:

IF me.cboFilterComm = "All Values" Then
'do nothing
Else
strWHERE = strWHERE _
& "([Communications] = " & me.cboFilterComm & ") AND "
ENDIF

Theoretically, you can set the checkboxes "TripleState" property to Yes, but
there is no easy way to distinguish (visually) between a NULL and a False
when you do this.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
That worked perfectly!!!! Thank you so much for your help. I am VBA code
illiterate too and your explanation was very easy to understand and follow.
--
Melissa


Dale Fye said:
Melissa,

Yes/No fields are either TRUE (checked) or False (unchecked). If you want
you filter to be able to distinguish between True, False, or No Selection,
then you need to use a control other than a checkbox. I generally use a
combo box with options of All Values, True, False. Then, in my code I'll do
something like:

IF me.cboFilterComm = "All Values" Then
'do nothing
Else
strWHERE = strWHERE _
& "([Communications] = " & me.cboFilterComm & ") AND "
ENDIF

Theoretically, you can set the checkboxes "TripleState" property to Yes, but
there is no easy way to distinguish (visually) between a NULL and a False
when you do this.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Melissa said:
I have a search form with the following fields

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

Values can have any combination of the Yes/No fields.

Here is an example of a search:

Give me all values where State is CO and where Communication is Yes.

Text - Region - Blank
Text - Ship To - Blank
Text - State - CO
Yes/No - Communication - Yes
Yes/No - Performance - Blank
Yes/No - Fuel - Blank

The problem with this filter is all values that equal Yes for both
Communication and Performance do not appear in the results. I only get values
where Yes is under Communications and No on Performance. If I choose Yes for
both Communications and Performance then I don't see values where
Communications and Fuel are Yes and Performance is No. Is there a way to
filter it so rather than looking at Yes or No it can look at Yes, No or Null?

Here's the code I am using. I am not very familiar with writing code so as
much assistance to modifying this would be greatly appreciated! :)

Private Sub cmdFilter_Click()


If Me.ckboxFilterComm = -1 Then
strWhere = strWhere & "([Communications] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Communications] = False) AND "
End If

If Me.ckboxFilterPerf = -1 Then
strWhere = strWhere & "([Performance] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Performance] = False) AND "
End If

If Me.ckboxFilterFuel= -1 Then
strWhere = strWhere & "([Fuel] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Fuel] = False) AND "
End If


Thanks,
Melissa
Click to expand...
Click to expand...
 
Hi Again Dale,

Does this method only work if the values are True or False? I tried this
code with another field that has several values and when I run the filter it
comes back with "run-time error, You can't assign a value to this object."
--
Melissa


Dale Fye said:
Melissa,

Yes/No fields are either TRUE (checked) or False (unchecked). If you want
you filter to be able to distinguish between True, False, or No Selection,
then you need to use a control other than a checkbox. I generally use a
combo box with options of All Values, True, False. Then, in my code I'll do
something like:

IF me.cboFilterComm = "All Values" Then
'do nothing
Else
strWHERE = strWHERE _
& "([Communications] = " & me.cboFilterComm & ") AND "
ENDIF

Theoretically, you can set the checkboxes "TripleState" property to Yes, but
there is no easy way to distinguish (visually) between a NULL and a False
when you do this.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Melissa said:
I have a search form with the following fields

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

Values can have any combination of the Yes/No fields.

Here is an example of a search:

Give me all values where State is CO and where Communication is Yes.

Text - Region - Blank
Text - Ship To - Blank
Text - State - CO
Yes/No - Communication - Yes
Yes/No - Performance - Blank
Yes/No - Fuel - Blank

The problem with this filter is all values that equal Yes for both
Communication and Performance do not appear in the results. I only get values
where Yes is under Communications and No on Performance. If I choose Yes for
both Communications and Performance then I don't see values where
Communications and Fuel are Yes and Performance is No. Is there a way to
filter it so rather than looking at Yes or No it can look at Yes, No or Null?

Here's the code I am using. I am not very familiar with writing code so as
much assistance to modifying this would be greatly appreciated! :)

Private Sub cmdFilter_Click()


If Me.ckboxFilterComm = -1 Then
strWhere = strWhere & "([Communications] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Communications] = False) AND "
End If

If Me.ckboxFilterPerf = -1 Then
strWhere = strWhere & "([Performance] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Performance] = False) AND "
End If

If Me.ckboxFilterFuel= -1 Then
strWhere = strWhere & "([Fuel] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Fuel] = False) AND "
End If


Thanks,
Melissa
Click to expand...
Click to expand...
 
No, this method will work with other values as well, as long as you have it
formatted properly. Give me an example of what you are trying to do. My
guess is that you want to test it against a string value. If that is the
case, you have to wrap the value from the combo box in quotes, something
like:

Else
strWHERE = strWHERE & "([SomeTextField] = " & chr$(34) &
me.cboFilterTextField & chr$(34) & ") AND "
ENDIF

You could also write this as:

strWHERE = strWHERE & "([SomeTextField] = '" & me.cboFilterTextField & "')
AND "

Notice that I have placed a single qoute between the = and ", and have also
placed a closing single quote between the " and ).

Personally, I prefer to use a function I wrote that allows me to wrap values
with an optional string character. Using that method, it would look like:

strWHERE = strWHERE & "([SomeTextField] = " & Quotes(me.cboFilterTextField)
& ") AND "

and the function Quotes looks like:

Public Function Quotes(varTextToQuote As Variant, _
Optional WrapWith As Variant = Null, _
Optional ReplaceWith As Variant = Null) As String

If IsNull(WrapWith) Then WrapWith = Chr$(34)
If IsNull(ReplaceWith) Then ReplaceWith = Chr$(39)

Quotes = WrapWith & Replace(Nz(varTextToQuote, ""), WrapWith,
ReplaceWith) & WrapWith

End Function

This function accepts a variant (varTextToQuote) so that it can be passed
NULL values. You could easily modify it to pass back a variant, and pass
back a NULL value if one was passed to it. As it is, it converts NULLs to
empty strings. It also accepts two optional arguments (WrapWith, and
ReplaceWith). WrapWith defaults to a double quote, and replace with
defaults to a single quote. This allows me to wrap text in quotes and
replace embedded quotes with single quotes.

I prefer this method because it is shorter and easier to read than method
#1, and just easier to read than method #2.

HTH
Dale



Melissa said:
Hi Again Dale,

Does this method only work if the values are True or False? I tried this
code with another field that has several values and when I run the filter
it
comes back with "run-time error, You can't assign a value to this object."
--
Melissa


Dale Fye said:
Melissa,

Yes/No fields are either TRUE (checked) or False (unchecked). If you
want
you filter to be able to distinguish between True, False, or No
Selection,
then you need to use a control other than a checkbox. I generally use a
combo box with options of All Values, True, False. Then, in my code I'll
do
something like:

IF me.cboFilterComm = "All Values" Then
'do nothing
Else
strWHERE = strWHERE _
& "([Communications] = " & me.cboFilterComm & ") AND "
ENDIF

Theoretically, you can set the checkboxes "TripleState" property to Yes,
but
there is no easy way to distinguish (visually) between a NULL and a False
when you do this.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Melissa said:
I have a search form with the following fields

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

Values can have any combination of the Yes/No fields.

Here is an example of a search:

Give me all values where State is CO and where Communication is Yes.

Text - Region - Blank
Text - Ship To - Blank
Text - State - CO
Yes/No - Communication - Yes
Yes/No - Performance - Blank
Yes/No - Fuel - Blank

The problem with this filter is all values that equal Yes for both
Communication and Performance do not appear in the results. I only get
values
where Yes is under Communications and No on Performance. If I choose
Yes for
both Communications and Performance then I don't see values where
Communications and Fuel are Yes and Performance is No. Is there a way
to
filter it so rather than looking at Yes or No it can look at Yes, No or
Null?

Here's the code I am using. I am not very familiar with writing code so
as
much assistance to modifying this would be greatly appreciated! :)

Private Sub cmdFilter_Click()


If Me.ckboxFilterComm = -1 Then
strWhere = strWhere & "([Communications] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Communications] = False) AND "
End If

If Me.ckboxFilterPerf = -1 Then
strWhere = strWhere & "([Performance] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Performance] = False) AND "
End If

If Me.ckboxFilterFuel= -1 Then
strWhere = strWhere & "([Fuel] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Fuel] = False) AND "
End If


Thanks,
Melissa
Click to expand...
Click to expand...
Click to expand...
 
Thank you Dale. That worked perfectly. Could I bother you with one more
question? Would this work with a list box? I would like to be able to choose
more than one value to filter from. For example, I have a category field with
values a,b,c. I would like to filter where it looks for a only or a,b or b,c
or a,c or a,b,c. Is this possible?
--
Melissa


Dale Fye said:
No, this method will work with other values as well, as long as you have it
formatted properly. Give me an example of what you are trying to do. My
guess is that you want to test it against a string value. If that is the
case, you have to wrap the value from the combo box in quotes, something
like:

Else
strWHERE = strWHERE & "([SomeTextField] = " & chr$(34) &
me.cboFilterTextField & chr$(34) & ") AND "
ENDIF

You could also write this as:

strWHERE = strWHERE & "([SomeTextField] = '" & me.cboFilterTextField & "')
AND "

Notice that I have placed a single qoute between the = and ", and have also
placed a closing single quote between the " and ).

Personally, I prefer to use a function I wrote that allows me to wrap values
with an optional string character. Using that method, it would look like:

strWHERE = strWHERE & "([SomeTextField] = " & Quotes(me.cboFilterTextField)
& ") AND "

and the function Quotes looks like:

Public Function Quotes(varTextToQuote As Variant, _
Optional WrapWith As Variant = Null, _
Optional ReplaceWith As Variant = Null) As String

If IsNull(WrapWith) Then WrapWith = Chr$(34)
If IsNull(ReplaceWith) Then ReplaceWith = Chr$(39)

Quotes = WrapWith & Replace(Nz(varTextToQuote, ""), WrapWith,
ReplaceWith) & WrapWith

End Function

This function accepts a variant (varTextToQuote) so that it can be passed
NULL values. You could easily modify it to pass back a variant, and pass
back a NULL value if one was passed to it. As it is, it converts NULLs to
empty strings. It also accepts two optional arguments (WrapWith, and
ReplaceWith). WrapWith defaults to a double quote, and replace with
defaults to a single quote. This allows me to wrap text in quotes and
replace embedded quotes with single quotes.

I prefer this method because it is shorter and easier to read than method
#1, and just easier to read than method #2.

HTH
Dale



Melissa said:
Hi Again Dale,

Does this method only work if the values are True or False? I tried this
code with another field that has several values and when I run the filter
it
comes back with "run-time error, You can't assign a value to this object."
--
Melissa


Dale Fye said:
Melissa,

Yes/No fields are either TRUE (checked) or False (unchecked). If you
want
you filter to be able to distinguish between True, False, or No
Selection,
then you need to use a control other than a checkbox. I generally use a
combo box with options of All Values, True, False. Then, in my code I'll
do
something like:

IF me.cboFilterComm = "All Values" Then
'do nothing
Else
strWHERE = strWHERE _
& "([Communications] = " & me.cboFilterComm & ") AND "
ENDIF

Theoretically, you can set the checkboxes "TripleState" property to Yes,
but
there is no easy way to distinguish (visually) between a NULL and a False
when you do this.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a search form with the following fields

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

Values can have any combination of the Yes/No fields.

Here is an example of a search:

Give me all values where State is CO and where Communication is Yes.

Text - Region - Blank
Text - Ship To - Blank
Text - State - CO
Yes/No - Communication - Yes
Yes/No - Performance - Blank
Yes/No - Fuel - Blank

The problem with this filter is all values that equal Yes for both
Communication and Performance do not appear in the results. I only get
values
where Yes is under Communications and No on Performance. If I choose
Yes for
both Communications and Performance then I don't see values where
Communications and Fuel are Yes and Performance is No. Is there a way
to
filter it so rather than looking at Yes or No it can look at Yes, No or
Null?

Here's the code I am using. I am not very familiar with writing code so
as
much assistance to modifying this would be greatly appreciated! :)

Private Sub cmdFilter_Click()


If Me.ckboxFilterComm = -1 Then
strWhere = strWhere & "([Communications] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Communications] = False) AND "
End If

If Me.ckboxFilterPerf = -1 Then
strWhere = strWhere & "([Performance] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Performance] = False) AND "
End If

If Me.ckboxFilterFuel= -1 Then
strWhere = strWhere & "([Fuel] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Fuel] = False) AND "
End If


Thanks,
Melissa
Click to expand...
Click to expand...
Click to expand...
 
Yes, but it is not quite as simple. Lists convert all of the fields that are
part of the rowsource to text values, so, even if one of your columns is a
date field, or a numeric, referring to that column in the list will return a
string. So I've got a function I use, that allows me to pass the name of the
list, and a column (0 based). The function then determines the data type of
the field in the underlying query, and wraps the values in the appropriate
wrapper " (strings), # (dates), or none for numeric values. If no items are
selected, the function returns a NULL. If only one is selected it returns a
string that starts with an equal sign, and then concatenates the appropriate
values. If multiple items are selected, it returns a string like: "IN
(....)".

In your case you would call it something like:

strWHERE = strWHERE & "([SomeField] " + fnMultiList(me.lstSomeField) + ")
AND "

As you can see, I've used the "+" to concatenate the returned value to the
field name and parenthesis. This way, if no items are selecte, the value on
the right of the equal sign will evaluate to strWHERE.

The function looks like:

Public Function fnMultiList(lst As ListBox, Optional WhatColumn As Variant =
Null) As Variant

Dim varItem As Variant
Dim varList As Variant
Dim lngColumn As Long
Dim lngDataType As Long

Dim rs As DAO.Recordset

'If no values are selected, return a NULL
If lst.ItemsSelected.Count = 0 Then
fnMultiList = Null
Exit Function
End If

varList = Null
lngColumn = Nz(WhatColumn, lst.BoundColumn - 1)

'Determine the datatype of the field being queried
Set rs = CurrentDb.OpenRecordset(lst.RowSource, , dbFailOnError)
If lngColumn > rs.Fields.Count - 1 Then lngColumn = 0
lngDataType = VarType(rs(lngColumn))
rs.Close
Set rs = Nothing

'Loop through the list of selected items, concatenating them
For Each varItem In lst.ItemsSelected
Select Case lngDataType
Case vbString
varList = (varList + ", ") & """" & lst.Column(lngColumn,
varItem) & """"
Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
varList = (varList + ", ") & lst.Column(lngColumn, varItem)
Case vbDate
varList = (varList + ", ") & "#" & lst.Column(lngColumn,
varItem) & "#"
End Select
Next

If lst.ItemsSelected.Count = 1 Then
fnMultiList = "= " & varList
Else
fnMultiList = "IN (" & varList & ")"
End If

End Function


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Melissa said:
Thank you Dale. That worked perfectly. Could I bother you with one more
question? Would this work with a list box? I would like to be able to choose
more than one value to filter from. For example, I have a category field with
values a,b,c. I would like to filter where it looks for a only or a,b or b,c
or a,c or a,b,c. Is this possible?
--
Melissa


Dale Fye said:
No, this method will work with other values as well, as long as you have it
formatted properly. Give me an example of what you are trying to do. My
guess is that you want to test it against a string value. If that is the
case, you have to wrap the value from the combo box in quotes, something
like:

Else
strWHERE = strWHERE & "([SomeTextField] = " & chr$(34) &
me.cboFilterTextField & chr$(34) & ") AND "
ENDIF

You could also write this as:

strWHERE = strWHERE & "([SomeTextField] = '" & me.cboFilterTextField & "')
AND "

Notice that I have placed a single qoute between the = and ", and have also
placed a closing single quote between the " and ).

Personally, I prefer to use a function I wrote that allows me to wrap values
with an optional string character. Using that method, it would look like:

strWHERE = strWHERE & "([SomeTextField] = " & Quotes(me.cboFilterTextField)
& ") AND "

and the function Quotes looks like:

Public Function Quotes(varTextToQuote As Variant, _
Optional WrapWith As Variant = Null, _
Optional ReplaceWith As Variant = Null) As String

If IsNull(WrapWith) Then WrapWith = Chr$(34)
If IsNull(ReplaceWith) Then ReplaceWith = Chr$(39)

Quotes = WrapWith & Replace(Nz(varTextToQuote, ""), WrapWith,
ReplaceWith) & WrapWith

End Function

This function accepts a variant (varTextToQuote) so that it can be passed
NULL values. You could easily modify it to pass back a variant, and pass
back a NULL value if one was passed to it. As it is, it converts NULLs to
empty strings. It also accepts two optional arguments (WrapWith, and
ReplaceWith). WrapWith defaults to a double quote, and replace with
defaults to a single quote. This allows me to wrap text in quotes and
replace embedded quotes with single quotes.

I prefer this method because it is shorter and easier to read than method
#1, and just easier to read than method #2.

HTH
Dale



Melissa said:
Hi Again Dale,

Does this method only work if the values are True or False? I tried this
code with another field that has several values and when I run the filter
it
comes back with "run-time error, You can't assign a value to this object."
--
Melissa


:

Melissa,

Yes/No fields are either TRUE (checked) or False (unchecked). If you
want
you filter to be able to distinguish between True, False, or No
Selection,
then you need to use a control other than a checkbox. I generally use a
combo box with options of All Values, True, False. Then, in my code I'll
do
something like:

IF me.cboFilterComm = "All Values" Then
'do nothing
Else
strWHERE = strWHERE _
& "([Communications] = " & me.cboFilterComm & ") AND "
ENDIF

Theoretically, you can set the checkboxes "TripleState" property to Yes,
but
there is no easy way to distinguish (visually) between a NULL and a False
when you do this.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a search form with the following fields

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

Values can have any combination of the Yes/No fields.

Here is an example of a search:

Give me all values where State is CO and where Communication is Yes.

Text - Region - Blank
Text - Ship To - Blank
Text - State - CO
Yes/No - Communication - Yes
Yes/No - Performance - Blank
Yes/No - Fuel - Blank

The problem with this filter is all values that equal Yes for both
Communication and Performance do not appear in the results. I only get
values
where Yes is under Communications and No on Performance. If I choose
Yes for
both Communications and Performance then I don't see values where
Communications and Fuel are Yes and Performance is No. Is there a way
to
filter it so rather than looking at Yes or No it can look at Yes, No or
Null?

Here's the code I am using. I am not very familiar with writing code so
as
much assistance to modifying this would be greatly appreciated! :)

Private Sub cmdFilter_Click()


If Me.ckboxFilterComm = -1 Then
strWhere = strWhere & "([Communications] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Communications] = False) AND "
End If

If Me.ckboxFilterPerf = -1 Then
strWhere = strWhere & "([Performance] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Performance] = False) AND "
End If

If Me.ckboxFilterFuel= -1 Then
strWhere = strWhere & "([Fuel] = True) AND "
ElseIf Me.ckboxFilterDistComm = 0 Then
strWhere = strWhere & "([Fuel] = False) AND "
End If


Thanks,
Melissa
Click to expand...
Click to expand...
Click to expand...
 
Back
Top