Qry not generating correct results on form (1 Viewer)

chrisjames25

Registered User.
Local time
Today, 06:59
Joined
Dec 1, 2014
Messages
401
HI

I have a form with 2 comboboxes on it. I have a query that filters the results based on the 2 selections from the combobxes. However when i filter the results do not appear as expected. For example if i choose a categoryid = 1 product and a potsize with id 3 i get results for items that have a category id of 1 and a potsize of id 3 but also of id13. How do i tweak the qry to eliminate this.

Many thnaks
 

Attachments

  • Qry issue.PNG
    Qry issue.PNG
    23.9 KB · Views: 110

Gasman

Enthusiastic Amateur
Local time
Today, 06:59
Joined
Sep 21, 2011
Messages
14,048
Use = instead of like ?
 

isladogs

MVP / VIP
Local time
Today, 06:59
Joined
Jan 14, 2017
Messages
18,186
...and in case its not obvious, remove the * wildcards at the same time
 

chrisjames25

Registered User.
Local time
Today, 06:59
Joined
Dec 1, 2014
Messages
401
Hi Thanks for above. I have done your suggestion and it achieves what i am looking for but creates another issue on the qry. On this form i have a subform that shows all the categorys and potsizes in it. As i choose a category this subform filters down to the selected catagory using the qry i posted above which i have now tweaked. Problem is now the subform wont populate anything in it till i select my potsize.

Is there a way around this. All i can think of is to change the qry of the subform in an afterupdate of the cbo_potsize but guessing there may be a way in the qry design to handle an if null situation
 

chrisjames25

Registered User.
Local time
Today, 06:59
Joined
Dec 1, 2014
Messages
401
Hi Gasman I tried this but kept getting errors. Will try again later tonight once home. THanks for all your help so far
 

plog

Banishment Pending
Local time
Today, 01:59
Joined
May 11, 2011
Messages
11,613
If the ultimate aim is to filter a form, use VBA to do that, not a query:


Essentially the form shows everything. Then the user uses your inputs to select their criteria and clicks a button. Then you build a method that builds a criteria string and uses Form.Filter() to show just the results they want. This would be the better method than doing it in the query tied to the form.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:59
Joined
Feb 19, 2002
Messages
42,981
LIKE is used only for strings. It is never used for numbers or dates and it is only used when you have a partial value. So, If I wanted to search on the last 4 digits of SSN (this is a code, not a number and so even though it contains only numeric digits, it is a string) I would use:
Where SSN Like "*" & Forms!myform!Last4

BUT, if I wanted to search for the entire SSN, I would NEVER use LIKE,
Where SSN = Forms!myform!SSN

Like with a leading wildcard prevents Access from ever using an index to find matching records and so Access has to actually read each and every record in the table so although there are certainly reasons to use LIKE, make sure you are not just being sloppy and using it when you shouldn't.
 

Users who are viewing this thread

Top Bottom