Allen
I apologize for delay in writing, my computer was out of the web for a
couple of weeks.
Trying your code vba displays this message: Compile error: Expected: end
of
statement.
Next line is coloured in yellow and the pointer in the , after (strTmp)
is
coloured in blue.
strWhere = "NombCom IN (" & Left$(strTmp), lngLen) & ") AND "
How can I fix this error?
Many thanks in advance
:
Okay, I think I must have missed your train of thought somewhere, so
have
just re-read this thread.
You have a text field named NombCom. You have a form with 5 unbound
check
boxes, representing 5 possible values in that field. You want to filter
this
form, so that any record that matches the value associated with any of
the
checked boxes gets included.
You'll need to build a filter string that looks like this:
NombCom IN ('Alfred','Betty','Charlene')
The code would look like this:
Dim strTmp As String
Dim lngLen As Long
strTmp = ""
If Me.CheckA.Value Then
strTmp = "'Alfred',"
End If
If Me.CheckB.Value Then
strTmp = strTmp & "'Betty',"
End If
'and so on to CheckE.
lngLen = Len(strTmp) - 1
If lngLen > 0 Then
strWhere = "NombCom IN (" & Left$(strTmp), lngLen) & ") AND "
End If
'and so on with the other filter options for your form.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hello Allen
After reading your article, I see that my first table relationship
design
is
correct. That means I cannot use check box to filter records with my
correct
design?
My idea to use check boxes is to allow user to select one or more
checks
in
the form to filter records. For example filtering by two years and
two or
more. check boxes.
If check boxes is not good, how can I filter one or more fields in a
txtbox?
Thank you very much.
:
Yes, you will certainly have the problem of constantly having to
modify
your
table (and everything else that depends on it) when you use lots of
yes/no
fields like that.
Is there any chance I can talk you into using a correctly normalized
design
for your tables instead? See:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hello Allen
I have to modify my table (with the check box field) and then
linked to
the
realtions. I let you know if I success.
Thank you very much indeed.
:
Your quotes are not right.
You need to replace MyYesNofield with the name of your yes/no
field.
strWhere = strWhere & "(MyYesNofield = " & Nz(Me.chkA, False)
& "
AND
"
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
message
Allen
I'm really confused, I know is my little skills in programming.
With this code:
If Not IsNull(Me.txtFilterAID) Then
strWhere = strWhere & "([AID] Like """ & Me.txtFilterID &
""")
AND "
End If
When I type a number in txtFilterAID and click the cmdFilter
the
form
shows
the records.The field AID is a string.
Nevertheless, with this code:
'If NotIsNull(Me.chkA) Then
'strWhere = strWhere & "(MyYesNofield = & Nz(Me.chkA, False)
&"
And
""
'End If
When I click the chkA and then click the cmdFilter codeVBA
displays
Compile
error: Sub or Function not defined.
The piece of code NotIsNull is selected in blue colour.
Could you tell me what I'm doing wrong? Please
What I'm trying to do is if I click in a check box (chkA) and
then
I
click
the cmdFilter the form will displays the filtered records in
the
form.
The table that I'm trying to filter records has the filed A
without
the
check box.
I hope my message is clear. I'm so sad due to my mother tongue
is
not
English.
Hoping to hear from you
:
You can append the true/false value of a check box into a
WHERE
string,
e.g.:
strWhere = strWhere & "(MyYesNoField = " & Nz(Me.Check1,
False)
&
")
AND
"
This doesn't allow you to leave it blank (so it doesn't filter
on
this
field.) An unbound check box can be null, but that's not very
visually
useful to the user. I think you're better using a combo box
where
the
user
can choose yes or no or leave it blank.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
message
Allen.
I can see the check box in my table in DataSheet. Thank you
very
much
indeed.
Now. How can I use this check box in your search form?I have
been
using
your
search form example successfully.
In my form I have this code to filter using a txtbox and I
know
that
using
check box will allow user to check one or more check boxes
This is the code I'm using to search by txtbox:
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
If Not IsNull(Me.txtFilterAID) Then
strWhere = strWhere & "([AID] = """ & Me.txtFilterAID &
""")
AND
"
End If
If Not IsNull(Me.txtFilterB) Then
strWhere = strWhere & "(
Like ""*" & Me.txtFilterB &
""")
AND
"
End If
If Not IsNull(Me.txtFilterC) Then
strWhere = strWhere & "([C] Like ""*" & Me.txtFilterC &
""")
AND
"
End If
If Not IsNull(Me.txtFilterD) Then **** Here is where I need
to
search
using
the Check box***
strWhere = strWhere & "([D] Like ""*" & Me.txtFilterD &
""")
AND
"
End If
Many thanks.
:
Try entering the expression in the Immediate Window.
Press Ctrl+G to open the Immediate Window.
