Qry not generating correct results on form

[chrisjames25]

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

 

[Gasman]

Use = instead of like ?

 

[isladogs]

...and in case its not obvious, remove the * wildcards at the same time

 

[chrisjames25]

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

 

[Gasman]

Use the Null option as per this thread.

https://www.access-programmers.co.u...show-all-records-if-no-selection-made.313596/

 

[chrisjames25]

Hi Gasman I tried this but kept getting errors. Will try again later tonight once home. THanks for all your help so far

 

[plog]

If the ultimate aim is to filter a form, use VBA to do that, not a query:

Form.Filter property (Access)

Office VBA reference topic

docs.microsoft.com

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.