Help needed on Search Query. (1 Viewer)

kmhoyt14

New member
Local time
Today, 10:24
Joined
Jan 28, 2020
Messages
8
I'm trying to create a query that's based on unbound text and combo boxes (6 boxes total) on a form. On this form I also want the option for a box to be left blank and the query only use the boxes that are filled in. I thought I had the correct criteria for the query but what I didn't notice until after a few runs is that each time I ran the query the criteria in design mode changed. What am I doing wrong and what's the best route for this?
 

Attachments

  • Capture.PNG
    Capture.PNG
    39.1 KB · Views: 85
  • Capture2.PNG
    Capture2.PNG
    121.5 KB · Views: 84

AccessBlaster

Registered User.
Local time
Today, 08:24
Joined
May 22, 2010
Messages
5,830
I'm surprised you didn't get a "Query is too complex error." I would try and simplify the criteria to one combo box, when you get that to work reliable then tackle the next.
 

plog

Banishment Pending
Local time
Today, 10:24
Joined
May 11, 2011
Messages
11,613
What am I doing wrong and what's the best route for this?

You might get a few masochists who are going to dig through that to find out where it fails, but probably not. The best route for this is to not do it in the query, but to build a Report and open the report with a filter and control the output that way.

My suggestion is you clear out all the criteria from your query, build a report based on the query and then use VBA on your form to open the report via DoCmd.OpenReport method (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport) using the filter parameter built off the inputs of your form that have been used.
 

kmhoyt14

New member
Local time
Today, 10:24
Joined
Jan 28, 2020
Messages
8
So just to make sure that I understand. When the user fills out the form and pushes a search button to run the query, the results from the query should then be filtered into a report. If this is correct then for each search will the user need to close the report and query before starting a new one? Also, if there's an attachment (ie a picture) on one of the results, will the user be able to open that attachment on the report or will they need to do that a different way?
 

plog

Banishment Pending
Local time
Today, 10:24
Joined
May 11, 2011
Messages
11,613
Yes they will have to close and open the report for each new search (this is the same as with queries alone).
Images can be embeded into reports (they cannot be viewed from the query).
 

Users who are viewing this thread

Top Bottom