Check Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

on this form, i have 6 check boxes years from 2007-2003, and ALL years. the
users should be able to check more then one when they are trying to filter on
years. The field just contains year in the main table.

when i click on apply filter whatever year i have checked it should appear
on the subform. Example of the code below. the code i have just lets me
filter one at a time. if i check 2003 and 2004 it doesnt work.


stryear = ""
If Chk2003 Then
stryear = "like " & 2003
End If

If Chk2004 Then
stryear = "like " & 2004
End If

ifchk2005 then
stryear = "like" & 2005
end if

I have the same for the other years


Help!
 
Assuming your year is stored as a numeric field, you can't use Like with it:
Like only works with Text fields.

My preference is to generate a string that uses the IN operator. For
example, if you wanted to see those records for 2003 and 2004, the string
would be something like:

MyYear IN (2003, 2004)

If the All box is checked, you don't actually need a string: you want
everything!

To generate this string, you can use something like:

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If Chk2005 Then
stryear = stryear & "2005, "
End If

If Chk2006 Then
stryear = stryear & "2006, "
End If

If Chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = "MyYear IN (" & Left$(stryear, Len(stryear - 2)) & ")"
End If

End If
 
I tried your code you gave me it only works when i check only one. Say if i
want 2003 and 2004 it doesnt work. even eith like other years say if i want
2004,2005 and 2006. at the end i have a check box call All Years. I just
need it when i need to look up more then one year at a time.

I used this code.

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "like " & 2003
End If

If Chk2004 Then
stryear = stryear & "like " & "2004, "
End If

If chk2005 Then
stryear = stryear & "like " & "2005, "
End If

If chk2006 Then
stryear = stryear & "like " & "2006, "
End If

If chk2007 Then
stryear = stryear & "like " & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = "MyYear IN (" & Left$(stryear, Len(stryear) - 2) & ")"
End If

End If
 
Did you see my statement "Assuming your year is stored as a numeric field,
you can't use Like with it: Like only works with Text fields."? Did you
notice that I do not have Like statements in my code?
 
Yes i notice that. My year is stored as a number, but when i put the Like in
the front it works.



If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If chk2005 Then
stryear = stryear & "2005, "
End If

If chk2006 Then
stryear = stryear & "2006, "
End If

If chk2007 Then
stryear = stryear & "2007, "

End If

If Len(stryear) > 0 Then
stryear = "(" & Left$(stryear, Len(stryear) - 2) & ")"
End If

End If



strfilter = "[type] " & strAdtype & " and [bookname] " & strbookname & "
and [advertiser] " & stradvertiser & " and
" & strheading & " and
[udac]" & strudac & " and [year]" & stryear


Me.FrmSearchsubform.Form.filter = strfilter
Me.FrmSearchsubform.Form.FilterOn = True​
 
it give me a error saying syntax error (missing operator) in query expression
[type] like '*" and [bookname] like'*' and [advertiser] like '*' and
like '*' and [udac] like'*' and [year] Myyear IN(2005, 2006 ,2007)​
 
You were intended to replace MyYear with whatever your actual field name is.
(BTW, year isn't a good choice for a field name, since there's a built-in
Year function that Access might get confused over. At least you've got
square brackets around the name)

Since you seem to be appending my strWhere variable to some other string
you've already created, try

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If Chk2005 Then
stryear = stryear & "2005, "
End If

If Chk2006 Then
stryear = stryear & "2006, "
End If

If Chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = " IN (" & Left$(stryear, Len(stryear - 2)) & ")"
End If

End If

If that still doesn't work for you, post the exact code you're using.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
it give me a error saying syntax error (missing operator) in query expression
[type] like '*" and [bookname] like'*' and [advertiser] like '*' and
like '*' and [udac] like'*' and [year] Myyear IN(2005, 2006 ,2007)

Douglas J Steele said:
Did you see my statement "Assuming your year is stored as a numeric field,
you can't use Like with it: Like only works with Text fields."? Did you
notice that I do not have Like statements in my code?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Say
if i i
want with
it: to
filter should
appear
Click to expand...
Click to expand...
​
 
thanks for your Help that worked.




Douglas J Steele said:
You were intended to replace MyYear with whatever your actual field name is.
(BTW, year isn't a good choice for a field name, since there's a built-in
Year function that Access might get confused over. At least you've got
square brackets around the name)

Since you seem to be appending my strWhere variable to some other string
you've already created, try

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If Chk2005 Then
stryear = stryear & "2005, "
End If

If Chk2006 Then
stryear = stryear & "2006, "
End If

If Chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = " IN (" & Left$(stryear, Len(stryear - 2)) & ")"
End If

End If

If that still doesn't work for you, post the exact code you're using.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
it give me a error saying syntax error (missing operator) in query expression
[type] like '*" and [bookname] like'*' and [advertiser] like '*' and
like '*' and [udac] like'*' and [year] Myyear IN(2005, 2006 ,2007)

Douglas J Steele said:
Did you see my statement "Assuming your year is stored as a numeric field,
you can't use Like with it: Like only works with Text fields."? Did you
notice that I do not have Like statements in my code?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried your code you gave me it only works when i check only one. Say
if i
want 2003 and 2004 it doesnt work. even eith like other years say if i
want
2004,2005 and 2006. at the end i have a check box call All Years. I just
need it when i need to look up more then one year at a time.

I used this code.

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "like " & 2003
End If

If Chk2004 Then
stryear = stryear & "like " & "2004, "
End If

If chk2005 Then
stryear = stryear & "like " & "2005, "
End If

If chk2006 Then
stryear = stryear & "like " & "2006, "
End If

If chk2007 Then
stryear = stryear & "like " & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = "MyYear IN (" & Left$(stryear, Len(stryear) - 2) & ")"
End If

End If


:

Assuming your year is stored as a numeric field, you can't use Like with
it:
Like only works with Text fields.

My preference is to generate a string that uses the IN operator. For
example, if you wanted to see those records for 2003 and 2004, the
string
would be something like:

MyYear IN (2003, 2004)

If the All box is checked, you don't actually need a string: you want
everything!

To generate this string, you can use something like:




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


on this form, i have 6 check boxes years from 2007-2003, and ALL
years.
the
users should be able to check more then one when they are trying to
filter
on
years. The field just contains year in the main table.

when i click on apply filter whatever year i have checked it should
appear
on the subform. Example of the code below. the code i have just
lets me
filter one at a time. if i check 2003 and 2004 it doesnt work.


stryear = ""
If Chk2003 Then
stryear = "like " & 2003
End If

If Chk2004 Then
stryear = "like " & 2004
End If

ifchk2005 then
stryear = "like" & 2005
end if

I have the same for the other years


Help!
Click to expand...
Click to expand...

​
Click to expand...
​
 
Quick Question

if we are using Text instead of Number we will use the same code but
instead i would put Like in the front right Cause i have another set of
check boxs that i need to do the same but its Text. Comp, YB, Canceled, ALL



If chkComp Then
strAdtype = strAdtype & "like " & "Comp, "
End If

If ChkYB then
strAdtype = strAdtype & "like " & "YB, "
End If

If ChkCanceled then
strAdtype = strAdtype & "like " & "Canceled, "
End If


is that correct.


thanks


Douglas J Steele said:
You were intended to replace MyYear with whatever your actual field name is.
(BTW, year isn't a good choice for a field name, since there's a built-in
Year function that Access might get confused over. At least you've got
square brackets around the name)

Since you seem to be appending my strWhere variable to some other string
you've already created, try

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If Chk2005 Then
stryear = stryear & "2005, "
End If

If Chk2006 Then
stryear = stryear & "2006, "
End If

If Chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = " IN (" & Left$(stryear, Len(stryear - 2)) & ")"
End If

End If

If that still doesn't work for you, post the exact code you're using.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
it give me a error saying syntax error (missing operator) in query expression
[type] like '*" and [bookname] like'*' and [advertiser] like '*' and
like '*' and [udac] like'*' and [year] Myyear IN(2005, 2006 ,2007)

Douglas J Steele said:
Did you see my statement "Assuming your year is stored as a numeric field,
you can't use Like with it: Like only works with Text fields."? Did you
notice that I do not have Like statements in my code?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried your code you gave me it only works when i check only one. Say
if i
want 2003 and 2004 it doesnt work. even eith like other years say if i
want
2004,2005 and 2006. at the end i have a check box call All Years. I just
need it when i need to look up more then one year at a time.

I used this code.

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "like " & 2003
End If

If Chk2004 Then
stryear = stryear & "like " & "2004, "
End If

If chk2005 Then
stryear = stryear & "like " & "2005, "
End If

If chk2006 Then
stryear = stryear & "like " & "2006, "
End If

If chk2007 Then
stryear = stryear & "like " & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = "MyYear IN (" & Left$(stryear, Len(stryear) - 2) & ")"
End If

End If


:

Assuming your year is stored as a numeric field, you can't use Like with
it:
Like only works with Text fields.

My preference is to generate a string that uses the IN operator. For
example, if you wanted to see those records for 2003 and 2004, the
string
would be something like:

MyYear IN (2003, 2004)

If the All box is checked, you don't actually need a string: you want
everything!

To generate this string, you can use something like:




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


on this form, i have 6 check boxes years from 2007-2003, and ALL
years.
the
users should be able to check more then one when they are trying to
filter
on
years. The field just contains year in the main table.

when i click on apply filter whatever year i have checked it should
appear
on the subform. Example of the code below. the code i have just
lets me
filter one at a time. if i check 2003 and 2004 it doesnt work.


stryear = ""
If Chk2003 Then
stryear = "like " & 2003
End If

If Chk2004 Then
stryear = "like " & 2004
End If

ifchk2005 then
stryear = "like" & 2005
end if

I have the same for the other years


Help!
Click to expand...
Click to expand...

​
Click to expand...
​
 
You can't "short circuit" using LIKE (or =, for that matter).

If they can check more than one of the check boxes, your SQL would have to
look something like:

WHERE MyField LIKE '*Comp*' OR MyField LIKE '*YB*'

Note that there's no point using LIKE unless you're including wildcards.
LIKE without wildcards is no different than =.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
Quick Question

if we are using Text instead of Number we will use the same code but
instead i would put Like in the front right Cause i have another set of
check boxs that i need to do the same but its Text. Comp, YB, Canceled, ALL



If chkComp Then
strAdtype = strAdtype & "like " & "Comp, "
End If

If ChkYB then
strAdtype = strAdtype & "like " & "YB, "
End If

If ChkCanceled then
strAdtype = strAdtype & "like " & "Canceled, "
End If


is that correct.


thanks


Douglas J Steele said:
You were intended to replace MyYear with whatever your actual field name is.
(BTW, year isn't a good choice for a field name, since there's a built-in
Year function that Access might get confused over. At least you've got
square brackets around the name)

Since you seem to be appending my strWhere variable to some other string
you've already created, try

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If Chk2005 Then
stryear = stryear & "2005, "
End If

If Chk2006 Then
stryear = stryear & "2006, "
End If

If Chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = " IN (" & Left$(stryear, Len(stryear - 2)) & ")"
End If

End If

If that still doesn't work for you, post the exact code you're using.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
it give me a error saying syntax error (missing operator) in query expression
[type] like '*" and [bookname] like'*' and [advertiser] like '*' and
like '*' and [udac] like'*' and [year] Myyear IN(2005, 2006 ,2007)

:

Did you see my statement "Assuming your year is stored as a numeric field,
you can't use Like with it: Like only works with Text fields."? Did you
notice that I do not have Like statements in my code?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried your code you gave me it only works when i check only one. Say
if i
want 2003 and 2004 it doesnt work. even eith like other years​
Click to expand...
say if
i
want
2004,2005 and 2006. at the end i have a check box call All Years.
Click to expand...
I
just
need it when i need to look up more then one year at a time.

I used this code.

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "like " & 2003
End If

If Chk2004 Then
stryear = stryear & "like " & "2004, "
End If

If chk2005 Then
stryear = stryear & "like " & "2005, "
End If

If chk2006 Then
stryear = stryear & "like " & "2006, "
End If

If chk2007 Then
stryear = stryear & "like " & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = "MyYear IN (" & Left$(stryear, Len(stryear) - 2)
Click to expand...
&
")"
End If

End If


:

Assuming your year is stored as a numeric field, you can't use
Click to expand...
Like
with
it:
Like only works with Text fields.

My preference is to generate a string that uses the IN operator. For
example, if you wanted to see those records for 2003 and 2004, the
string
would be something like:

MyYear IN (2003, 2004)

If the All box is checked, you don't actually need a string: you want
everything!

To generate this string, you can use something like:




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


on this form, i have 6 check boxes years from 2007-2003, and ALL
years.
the
users should be able to check more then one when they are
Click to expand...
trying
to
filter
on
years. The field just contains year in the main table.

when i click on apply filter whatever year i have checked it should
appear
on the subform. Example of the code below. the code i have just
lets me
filter one at a time. if i check 2003 and 2004 it doesnt work.


stryear = ""
If Chk2003 Then
stryear = "like " & 2003
End If

If Chk2004 Then
stryear = "like " & 2004
End If

ifchk2005 then
stryear = "like" & 2005
end if

I have the same for the other years


Help!
Click to expand...
Click to expand...
​
Click to expand...
​
 
the code you gave me yesterday for the check box using years. it was working
but today i tried it and now its not working. Is their something missing


If Not ChkALL Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If chk2005 Then
stryear = stryear & "2005, "
End If

If chk2006 Then
stryear = stryear & "2006, "
End If

If chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
stryear = " IN (" & Left$(stryear, Len(stryear) - 2) & ")"
End If


End If




strfilter = "[type] " & strAdtype & " and [bookname] " & strbookname & "
and [advertiser] " & stradvertiser & " and
" & strheading & " and
[udac]" & strudac & " and [year]" & stryear


Me.FrmSearchsubform.Form.filter = strfilter
Me.FrmSearchsubform.Form.FilterOn = True

Douglas J Steele said:
You were intended to replace MyYear with whatever your actual field name is.
(BTW, year isn't a good choice for a field name, since there's a built-in
Year function that Access might get confused over. At least you've got
square brackets around the name)

Since you seem to be appending my strWhere variable to some other string
you've already created, try

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If Chk2005 Then
stryear = stryear & "2005, "
End If

If Chk2006 Then
stryear = stryear & "2006, "
End If

If Chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = " IN (" & Left$(stryear, Len(stryear - 2)) & ")"
End If

End If

If that still doesn't work for you, post the exact code you're using.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
it give me a error saying syntax error (missing operator) in query expression
[type] like '*" and [bookname] like'*' and [advertiser] like '*' and
like '*' and [udac] like'*' and [year] Myyear IN(2005, 2006 ,2007)

Douglas J Steele said:
Did you see my statement "Assuming your year is stored as a numeric field,
you can't use Like with it: Like only works with Text fields."? Did you
notice that I do not have Like statements in my code?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried your code you gave me it only works when i check only one. Say
if i
want 2003 and 2004 it doesnt work. even eith like other years say if i
want
2004,2005 and 2006. at the end i have a check box call All Years. I just
need it when i need to look up more then one year at a time.

I used this code.

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "like " & 2003
End If

If Chk2004 Then
stryear = stryear & "like " & "2004, "
End If

If chk2005 Then
stryear = stryear & "like " & "2005, "
End If

If chk2006 Then
stryear = stryear & "like " & "2006, "
End If

If chk2007 Then
stryear = stryear & "like " & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = "MyYear IN (" & Left$(stryear, Len(stryear) - 2) & ")"
End If

End If


:

Assuming your year is stored as a numeric field, you can't use Like with
it:
Like only works with Text fields.

My preference is to generate a string that uses the IN operator. For
example, if you wanted to see those records for 2003 and 2004, the
string
would be something like:

MyYear IN (2003, 2004)

If the All box is checked, you don't actually need a string: you want
everything!

To generate this string, you can use something like:




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


on this form, i have 6 check boxes years from 2007-2003, and ALL
years.
the
users should be able to check more then one when they are trying to
filter
on
years. The field just contains year in the main table.

when i click on apply filter whatever year i have checked it should
appear
on the subform. Example of the code below. the code i have just
lets me
filter one at a time. if i check 2003 and 2004 it doesnt work.


stryear = ""
If Chk2003 Then
stryear = "like " & 2003
End If

If Chk2004 Then
stryear = "like " & 2004
End If

ifchk2005 then
stryear = "like" & 2005
end if

I have the same for the other years


Help!
Click to expand...
Click to expand...

​
Click to expand...
​
 
The way your code is set up, you will have a problem if none of the
year-related checkboxes are checked.

Are you sure that it's the year-related stuff that's causing a problem,
though? If the original code you posted for the year-related checkboxes is
anything to go by, how you populate the strings like strAdtype, strbookname,
stradvertiser etc could be the cause of the problem.

I've suggested a couple of times that you post your code, but you seem
reluctant to do that. It's difficult for me to help when I don't have all of
the facts.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
the code you gave me yesterday for the check box using years. it was working
but today i tried it and now its not working. Is their something missing


If Not ChkALL Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If chk2005 Then
stryear = stryear & "2005, "
End If

If chk2006 Then
stryear = stryear & "2006, "
End If

If chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
stryear = " IN (" & Left$(stryear, Len(stryear) - 2) & ")"
End If


End If




strfilter = "[type] " & strAdtype & " and [bookname] " & strbookname & "
and [advertiser] " & stradvertiser & " and
" & strheading & " and
[udac]" & strudac & " and [year]" & stryear


Me.FrmSearchsubform.Form.filter = strfilter
Me.FrmSearchsubform.Form.FilterOn = True

Douglas J Steele said:
You were intended to replace MyYear with whatever your actual field name is.
(BTW, year isn't a good choice for a field name, since there's a built-in
Year function that Access might get confused over. At least you've got
square brackets around the name)

Since you seem to be appending my strWhere variable to some other string
you've already created, try

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If Chk2005 Then
stryear = stryear & "2005, "
End If

If Chk2006 Then
stryear = stryear & "2006, "
End If

If Chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = " IN (" & Left$(stryear, Len(stryear - 2)) & ")"
End If

End If

If that still doesn't work for you, post the exact code you're using.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
it give me a error saying syntax error (missing operator) in query expression
[type] like '*" and [bookname] like'*' and [advertiser] like '*' and
like '*' and [udac] like'*' and [year] Myyear IN(2005, 2006 ,2007)

:

Did you see my statement "Assuming your year is stored as a numeric field,
you can't use Like with it: Like only works with Text fields."? Did you
notice that I do not have Like statements in my code?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I tried your code you gave me it only works when i check only one. Say
if i
want 2003 and 2004 it doesnt work. even eith like other years​
Click to expand...
say if
i
want
2004,2005 and 2006. at the end i have a check box call All Years.
Click to expand...
I
just
need it when i need to look up more then one year at a time.

I used this code.

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "like " & 2003
End If

If Chk2004 Then
stryear = stryear & "like " & "2004, "
End If

If chk2005 Then
stryear = stryear & "like " & "2005, "
End If

If chk2006 Then
stryear = stryear & "like " & "2006, "
End If

If chk2007 Then
stryear = stryear & "like " & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = "MyYear IN (" & Left$(stryear, Len(stryear) - 2)
Click to expand...
&
")"
End If

End If


:

Assuming your year is stored as a numeric field, you can't use
Click to expand...
Like
with
it:
Like only works with Text fields.

My preference is to generate a string that uses the IN operator. For
example, if you wanted to see those records for 2003 and 2004, the
string
would be something like:

MyYear IN (2003, 2004)

If the All box is checked, you don't actually need a string: you want
everything!

To generate this string, you can use something like:




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


on this form, i have 6 check boxes years from 2007-2003, and ALL
years.
the
users should be able to check more then one when they are
Click to expand...
trying
to
filter
on
years. The field just contains year in the main table.

when i click on apply filter whatever year i have checked it should
appear
on the subform. Example of the code below. the code i have just
lets me
filter one at a time. if i check 2003 and 2004 it doesnt work.


stryear = ""
If Chk2003 Then
stryear = "like " & 2003
End If

If Chk2004 Then
stryear = "like " & 2004
End If

ifchk2005 then
stryear = "like" & 2005
end if

I have the same for the other years


Help!
Click to expand...
Click to expand...
​
Click to expand...
​
 
At a very minimum, please post the content of strfilter, so that we can see
whether it's a valid filter.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas J Steele said:
The way your code is set up, you will have a problem if none of the
year-related checkboxes are checked.

Are you sure that it's the year-related stuff that's causing a problem,
though? If the original code you posted for the year-related checkboxes is
anything to go by, how you populate the strings like strAdtype, strbookname,
stradvertiser etc could be the cause of the problem.

I've suggested a couple of times that you post your code, but you seem
reluctant to do that. It's difficult for me to help when I don't have all of
the facts.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dimple said:
the code you gave me yesterday for the check box using years. it was working
but today i tried it and now its not working. Is their something missing


If Not ChkALL Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If chk2005 Then
stryear = stryear & "2005, "
End If

If chk2006 Then
stryear = stryear & "2006, "
End If

If chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
stryear = " IN (" & Left$(stryear, Len(stryear) - 2) & ")"
End If


End If




strfilter = "[type] " & strAdtype & " and [bookname] " & strbookname & "
and [advertiser] " & stradvertiser & " and
" & strheading & " and
[udac]" & strudac & " and [year]" & stryear


Me.FrmSearchsubform.Form.filter = strfilter
Me.FrmSearchsubform.Form.FilterOn = True

Douglas J Steele said:
You were intended to replace MyYear with whatever your actual field
Click to expand...
Click to expand...
name
is.
(BTW, year isn't a good choice for a field name, since there's a built-in
Year function that Access might get confused over. At least you've got
square brackets around the name)

Since you seem to be appending my strWhere variable to some other string
you've already created, try

If Not ChkAll Then

stryear = ""

If Chk2003 Then
stryear = stryear & "2003, "
End If

If Chk2004 Then
stryear = stryear & "2004, "
End If

If Chk2005 Then
stryear = stryear & "2005, "
End If

If Chk2006 Then
stryear = stryear & "2006, "
End If

If Chk2007 Then
stryear = stryear & "2007, "
End If

If Len(stryear) > 0 Then
strWhere = " IN (" & Left$(stryear, Len(stryear - 2)) & ")"
End If

End If

If that still doesn't work for you, post the exact code you're using.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


it give me a error saying syntax error (missing operator) in query
expression
[type] like '*" and [bookname] like'*' and [advertiser] like '*' and
like '*' and [udac] like'*' and [year] Myyear IN(2005, 2006
,2007)

:

Did you see my statement "Assuming your year is stored as a numeric
field,
you can't use Like with it: Like only works with Text fields."?​
Click to expand...
​
Click to expand...
Did
you
Click to expand...
Years.
I
Click to expand...
2)
&
Click to expand...
operator.
For
Click to expand...
have
just
Click to expand...
Click to expand...
​
 
Back
Top