Access VBA Form - Filter Multiple and/or

Hi,

I'm using Access 2010 and I have a form which I'd like to filter data.
I have two fields: ACCOUNT + CPYCODE

And I'd like to filter the data so we end up with:
CPYCODE = 12 - 14
ACCOUNT = Not like: A00090, A00091, A00092, A00093, A00094

I doubt a string of 'ACCOUNT not like' is the best way to do it, and I don't think my use of ( ) is correct either:

DoCmd.ApplyFilter , "(CpyCode = '12') or (CpyCode = '13') or (CpyCode = '14') and Account not like 'A00090'"

Open in new window


Can anyone help me achieve what I'm trying to do?

Many thanks,
Jack MarleyAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
Try:
Me.Filter = "CPYCODE like ""1[2-4]"" AND ACCOUNT NOT LIKE ""A0009[1-4]"""
Me.FilterOn = True

Open in new window

0
 
Rgonzo1971Commented:
HI,

pls try
DoCmd.ApplyFilter , "((CpyCode = '12') or (CpyCode = '13') or (CpyCode = '14')) and Account not like 'A00090'"

Open in new window

Regards
0
 
Dale FyeCommented:
How about something like:

CPYCODE = 12 - 14
ACCOUNT = Not like: A00090, A00091, A00092, A00093, A00094

strFilter = "[CPYCODE] IN ("12", "13", "14") AND NOT ([Account] IN ("A00090", "A00091", "A00092", "A00093", "A00094")

This assumes that [CPYCODE] is a string, as indicated by the single quotes surrounding '12' in your sample above.

HTH
Dale
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rgonzo1971Commented:
corrected code
DoCmd.ApplyFilter , "((CpyCode = '12') or (CpyCode = '13') or (CpyCode = '14')) and Account not like 'A00090*'"

Open in new window

0
 
John TsioumprisSoftware & Systems EngineerCommented:
I'd go this way
Me.Filter = "( CPYCODE BETWEEN 12 AND 14) AND (Account NOT IN ( 'A00090', 'A00091', 'A00092', 'A00093', 'A00094'))"
Me.FilterOn=True

Open in new window

0
 
Jack MarleyAuthor Commented:
Thanks all, I went for hnasr's solution in the end but they all work well.
Cheers.
0
 
hnasrCommented:
Welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.