Custom Search return all values...

[Brook]

Marshall,

Just to let you know, all remaining searches are working fine... i'm just
having trouble with this one b/c of the quotes for the inches, if all else
fails I can set up a field without the quotes for inches...

But here is the code that I updated, and i'm not getting any errors but
its not returning any results for any records:

Private Sub Command31_Click()
Dim strWhere As String

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFt]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

Any other suggestions? if I can't get it to work, I have a work around field
that I can use for the size.

Brook

did you see my last question regard the field size?

Also you mentioned having all the searches from one command button. I tried
it, but it isn't working. Here is the code:

Private Sub Command46_Click()
Dim strWhere As String

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFt]=" _
& """" & cboSizeFeet & """"
End If
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

Yes, I now see the size question. This is the old quoted
quotes issue. Any time the value of a string contains quote
marks and it is going to be included inside quote marks, the
imbedded quote must be doubled up. Sounds like double speak
to me too ;-)

For example:
str = "He said, "Hello""
has ambiguous syntax that can not be parsed. The way to
make it follow the above rule is to use:

str = "He said, ""Hello"""

You can achieve that effect within the combo box's value is
to make the statement even more general:

strWhere = strWhere & " AND [SizeFt]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
which I agree is kind of confusing all by itself. An
Alternative way to do the same thing is:

strWhere = strWhere & " AND [SizeFt]=" & Chr(34) _
& Replace(cboSizeFeet, Chr(34), Chr(34) & Chr(34)) & Chr(34)

Well, that may not really clarify things all that much, but
if you think about it, in spite of the headache, you can
gain a fair insight into the issue.

********
Another problem in your code is that you miscounted the
number of quotes in the DesignName block of code.

********
Are you sure the DesignNumber field in the table is a Text
field?

 

[Brook]

Nevermind ... nevermind the code is working ....

I guess my last question is in response to having only one Command button
which would be very benefitial...

Would I just combine my 3 search codes? into one ?

like this:

Dim strWhere As String
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True

Thanks..

Brook

did you see my last question regard the field size?

Also you mentioned having all the searches from one command button. I tried
it, but it isn't working. Here is the code:

Private Sub Command46_Click()
Dim strWhere As String

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFt]=" _
& """" & cboSizeFeet & """"
End If
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True
End Sub

Yes, I now see the size question. This is the old quoted
quotes issue. Any time the value of a string contains quote
marks and it is going to be included inside quote marks, the
imbedded quote must be doubled up. Sounds like double speak
to me too ;-)

For example:
str = "He said, "Hello""
has ambiguous syntax that can not be parsed. The way to
make it follow the above rule is to use:

str = "He said, ""Hello"""

You can achieve that effect within the combo box's value is
to make the statement even more general:

strWhere = strWhere & " AND [SizeFt]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
which I agree is kind of confusing all by itself. An
Alternative way to do the same thing is:

strWhere = strWhere & " AND [SizeFt]=" & Chr(34) _
& Replace(cboSizeFeet, Chr(34), Chr(34) & Chr(34)) & Chr(34)

Well, that may not really clarify things all that much, but
if you think about it, in spite of the headache, you can
gain a fair insight into the issue.

********
Another problem in your code is that you miscounted the
number of quotes in the DesignName block of code.

********
Are you sure the DesignNumber field in the table is a Text
field?

 

[Marshall Barton]

Nevermind ... nevermind the code is working ....

I guess my last question is in response to having only one Command button
which would be very benefitial...

Would I just combine my 3 search codes? into one ?
like this:

Dim strWhere As String
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True

Alright! Some serious progress ;-)

Yes! Your code above is where I started this whole episode,
EXCEPT that you still have the wrong number of quotes in the
DesignName block of code (you probably got it to work by
itself and forgot to fix it in the combined button's code).

If this actually gets it to work the way you want, take a
ten minute break to feel good about all the progress you've
made. Then get to work on the next feature in your app ;-)

 

[Brook]

Marshall,

I don't see where there is an extra quote, b/c everything is working great
now... even my single commmand button for the search feature...

Thanks, so much...

Brook

Nevermind ... nevermind the code is working ....

I guess my last question is in response to having only one Command button
which would be very benefitial...

Would I just combine my 3 search codes? into one ?
like this:

Dim strWhere As String
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True

Alright! Some serious progress ;-)

Yes! Your code above is where I started this whole episode,
EXCEPT that you still have the wrong number of quotes in the
DesignName block of code (you probably got it to work by
itself and forgot to fix it in the combined button's code).

If this actually gets it to work the way you want, take a
ten minute break to feel good about all the progress you've
made. Then get to work on the next feature in your app ;-)

 

[Brook]

ok... now I see what you meant by the wrong number of quotes....

My last and final question....

everything is working great... searching great...

can I create a button to "clear" the search so that I don't have to exit
then re-enter the form to perform a new search?

Brook

Nevermind ... nevermind the code is working ....

I guess my last question is in response to having only one Command button
which would be very benefitial...

Would I just combine my 3 search codes? into one ?
like this:

Dim strWhere As String
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True

Alright! Some serious progress ;-)

Yes! Your code above is where I started this whole episode,
EXCEPT that you still have the wrong number of quotes in the
DesignName block of code (you probably got it to work by
itself and forgot to fix it in the combined button's code).

If this actually gets it to work the way you want, take a
ten minute break to feel good about all the progress you've
made. Then get to work on the next feature in your app ;-)

 

[Marshall Barton]

OK, we're cooking now!

You do not have to exit the form to do another search. Just
delete the old search values or enter new values over the
old ones. However, a Clear button is still a good idea:

Sub btnClear_Click()
Me.cbodesign = Null
Me.cboSizeFeet = Null
Me.cbodesignnumber = Null
End Sub
--
Marsh
MVP [MS Access]

ok... now I see what you meant by the wrong number of quotes....

My last and final question....

everything is working great... searching great...

can I create a button to "clear" the search so that I don't have to exit
then re-enter the form to perform a new search?

Nevermind ... nevermind the code is working ....

I guess my last question is in response to having only one Command button
which would be very benefitial...

Would I just combine my 3 search codes? into one ?
like this:

Dim strWhere As String
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True

Alright! Some serious progress ;-)

Yes! Your code above is where I started this whole episode,
EXCEPT that you still have the wrong number of quotes in the
DesignName block of code (you probably got it to work by
itself and forgot to fix it in the combined button's code).

If this actually gets it to work the way you want, take a
ten minute break to feel good about all the progress you've
made. Then get to work on the next feature in your app ;-)

 

[Brook]

Marshall....

This is looking and working great....

Do I need to add some type of Requery / refresh to the clear button to
repopulate the form with all data?

Brook

OK, we're cooking now!

You do not have to exit the form to do another search. Just
delete the old search values or enter new values over the
old ones. However, a Clear button is still a good idea:

Sub btnClear_Click()
Me.cbodesign = Null
Me.cboSizeFeet = Null
Me.cbodesignnumber = Null
End Sub
--
Marsh
MVP [MS Access]

ok... now I see what you meant by the wrong number of quotes....

My last and final question....

everything is working great... searching great...

can I create a button to "clear" the search so that I don't have to exit
then re-enter the form to perform a new search?

Brook wrote:
Nevermind ... nevermind the code is working ....

I guess my last question is in response to having only one Command button
which would be very benefitial...

Would I just combine my 3 search codes? into one ?
like this:

Dim strWhere As String
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True

Alright! Some serious progress ;-)

Yes! Your code above is where I started this whole episode,
EXCEPT that you still have the wrong number of quotes in the
DesignName block of code (you probably got it to work by
itself and forgot to fix it in the combined button's code).

If this actually gets it to work the way you want, take a
ten minute break to feel good about all the progress you've
made. Then get to work on the next feature in your app ;-)

 

[Marshall Barton]

To return the form to an unfiltered state add this line to
the Clear button's Click procedure:

Me.FilterOn = False

Now that you have all that working and without trying to
confuse you, I should point out that having the form display
all the records in the table is an inefficient use of
resources. Some day, when you have a ton of records in the
table, you might want to reconsider your current approach
and change things to not load/display any data until after
the filter is specified. Not something you need to worry
about immediately, but keep it in the back of your mind for
future reference.
--
Marsh
MVP [MS Access]

Do I need to add some type of Requery / refresh to the clear button to
repopulate the form with all data?

You do not have to exit the form to do another search. Just
delete the old search values or enter new values over the
old ones. However, a Clear button is still a good idea:

Sub btnClear_Click()
Me.cbodesign = Null
Me.cboSizeFeet = Null
Me.cbodesignnumber = Null
End Sub

ok... now I see what you meant by the wrong number of quotes....

My last and final question....

everything is working great... searching great...

can I create a button to "clear" the search so that I don't have to exit
then re-enter the form to perform a new search?


Brook wrote:
Nevermind ... nevermind the code is working ....

I guess my last question is in response to having only one Command button
which would be very benefitial...

Would I just combine my 3 search codes? into one ?
like this:

Dim strWhere As String
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True


:
Yes! Your code above is where I started this whole episode,
EXCEPT that you still have the wrong number of quotes in the
DesignName block of code (you probably got it to work by
itself and forgot to fix it in the combined button's code).

If this actually gets it to work the way you want, take a
ten minute break to feel good about all the progress you've
made. Then get to work on the next feature in your app ;-)

 

[Brook]

Marshall...

I can say that this is complete... and fully functional...

I was trying Me.Requery or Me.Refresh...

but the Me.Filter=False worked perfectly...

I want to say thank you so much for hanging in there with me!

you have been wonderfull.... this is going to help my office staff so much

Take care.. have a great holiday...

Brook

To return the form to an unfiltered state add this line to
the Clear button's Click procedure:

Me.FilterOn = False

Now that you have all that working and without trying to
confuse you, I should point out that having the form display
all the records in the table is an inefficient use of
resources. Some day, when you have a ton of records in the
table, you might want to reconsider your current approach
and change things to not load/display any data until after
the filter is specified. Not something you need to worry
about immediately, but keep it in the back of your mind for
future reference.
--
Marsh
MVP [MS Access]

Do I need to add some type of Requery / refresh to the clear button to
repopulate the form with all data?

You do not have to exit the form to do another search. Just
delete the old search values or enter new values over the
old ones. However, a Clear button is still a good idea:

Sub btnClear_Click()
Me.cbodesign = Null
Me.cboSizeFeet = Null
Me.cbodesignnumber = Null
End Sub


Brook wrote:
ok... now I see what you meant by the wrong number of quotes....

My last and final question....

everything is working great... searching great...

can I create a button to "clear" the search so that I don't have to exit
then re-enter the form to perform a new search?


Brook wrote:
Nevermind ... nevermind the code is working ....

I guess my last question is in response to having only one Command button
which would be very benefitial...

Would I just combine my 3 search codes? into one ?
like this:

Dim strWhere As String
If Not IsNull(cbodesign) Then
strWhere = strWhere & " AND [DesignName]=" _
& """ & cbodesign & """
End If

If Not IsNull(cboSizeFeet) Then
strWhere = strWhere & " AND [SizeFeet]=" _
& """" & Replace(cboSizeFeet, """", """""") & """"
End If

If Not IsNull(cbodesignnumber) Then
strWhere = strWhere & " AND [DesignNumber]=" _
& """" & cbodesignnumber & """"
End If
Me.Filter = Mid(strWhere, 6) 'set filter w/o extra AND
Me.FilterOn = True


:
Yes! Your code above is where I started this whole episode,
EXCEPT that you still have the wrong number of quotes in the
DesignName block of code (you probably got it to work by
itself and forgot to fix it in the combined button's code).

If this actually gets it to work the way you want, take a
ten minute break to feel good about all the progress you've
made. Then get to work on the next feature in your app ;-)