question about using strFilter with AND

  • Thread starter Thread starter Joe S.
  • Start date Start date
J

Joe S.

Access 2007 database and using a form to filter records and import the
results into a report.

There are 4 list boxes:
lstpayment
lststore
lstkeyword
lstbuyer

I can get what I want to work one at a time i.e.
strFilter = "[store_name] = '" & Me!lststore & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter

Works fine for the storename filter. But if I try to use an AND statement I
get a type mismatch error

strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = '"
& Me!lstpayment & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
Anyone have any ideas?
 
Daniel - Thanks for your reply :D

I was just informed that i was getting too excited about quotation marks,
the correct syntax is

strFilter = "[store_name] = '" & Me!lststore & "' And [payment_type] = '" &
Me!lstpayment & "'"


One thing i just realized is, if a person doesn't want to include one of the
list boxes it will never return any results. (i.e. they only want to know
payment type and store name, but not buyer or keyword).

What syntax would i use to specify if nothing is selected, to not filter
based off that list box?


Daniel Pineault said:
Your bracketing appear off, try:

strFilter = "[store_name] = '" & Me!lststore & "' AND [payment_type] = '" &
Me!lstpayment & "'"
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



Joe S. said:
Access 2007 database and using a form to filter records and import the
results into a report.

There are 4 list boxes:
lstpayment
lststore
lstkeyword
lstbuyer

I can get what I want to work one at a time i.e.
strFilter = "[store_name] = '" & Me!lststore & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter

Works fine for the storename filter. But if I try to use an AND statement I
get a type mismatch error

strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = '"
& Me!lstpayment & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
Anyone have any ideas?
Click to expand...
Click to expand...
 
You would have to build you string item by item. Something along the lines of:

if isnull(Me!lststore)=false then
strFilter = strFilter & "[store_name] = '" & Me!lststore & "' AND "
end if
if isnull(Me!lstpayment )=false then
strFilter = strFilter & "[payment_type] = '" & Me!lstpayment & "' AND"
end if

And so for for each lst

Then finally trim of the last AND

strFilter = left(strFilter, len(strFilter)-5)

and then open using your code
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



Joe S. said:
Daniel - Thanks for your reply :D

I was just informed that i was getting too excited about quotation marks,
the correct syntax is

strFilter = "[store_name] = '" & Me!lststore & "' And [payment_type] = '" &
Me!lstpayment & "'"


One thing i just realized is, if a person doesn't want to include one of the
list boxes it will never return any results. (i.e. they only want to know
payment type and store name, but not buyer or keyword).

What syntax would i use to specify if nothing is selected, to not filter
based off that list box?


Daniel Pineault said:
Your bracketing appear off, try:

strFilter = "[store_name] = '" & Me!lststore & "' AND [payment_type] = '" &
Me!lstpayment & "'"
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



Joe S. said:
Access 2007 database and using a form to filter records and import the
results into a report.

There are 4 list boxes:
lstpayment
lststore
lstkeyword
lstbuyer

I can get what I want to work one at a time i.e.
strFilter = "[store_name] = '" & Me!lststore & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter

Works fine for the storename filter. But if I try to use an AND statement I
get a type mismatch error

strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = '"
& Me!lstpayment & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
Anyone have any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top