Multiple parameter query and empty parameter

[Brianwarnock]

Sorry to jump in here as i have only read the last few posts, but sometimes vbaInet is too clever for us mere mortals , I would go back to his suggestion in post #76 and have 2 queries each run by a different command button on the form, suitably annotated to show => or < , and one textbox for the criteria value.

Brian

 

[thewrightdesign]

Thanks Brian

What I ended up doing is keeping my initial search form as is but added in six text boxes to do between values for each of the R M and L fields. Also since that only works for criteria we want to see across all three fields, I built a separate query (more than one actually) to run off that main query to further filter down to criteria that can be in any of those three. It's been really confusing and not sure I explained that right at all, and I'm sure this is more cumbersome than I want it to be, but at least it's working and returning exactly the results we need to see. That will suffice for the moment while I continue to try out suggestions from here and streamline this process.

Since we are pulling this data in a couple different ways, I've had to build queries for each way we are doing it. One set for collective data, and one set to be run monthly (those queries run off results from the main form after between dates have been entered). I know there's an easier way, or at least a more efficient way Maybe once I have this to a good stopping point I'll put in dummy data and upload it in case someone can see a better way of doing it. Might be a ton easier than me trying to explain it all.

 

[vbaInet]

Sorry to jump in here as i have only read the last few posts, but sometimes vbaInet is too clever for us mere mortals ,

You've been at the game much longer than myself Brian.

--} bows {--

I would go back to his suggestion in post #76 and have 2 queries each run by a different command button on the form, suitably annotated to show => or < , and one textbox for the criteria value.

Brian

Ah, yes one textbox for the criteria value will suffice. And yes, two buttons will make it simpler for the OP.

@thewrightdesign: What you need is a form for entering criteria and selecting operators.

 

[thewrightdesign]

Ah, yes one textbox for the criteria value will suffice. And yes, two buttons will make it simpler for the OP.

@thewrightdesign: What you need is a form for entering criteria and selecting operators.

One textbox that would enter the criteria (ie: 8.0, 10.0, etc?) and buttons that open queries... one for > and one for <? And that would enter the criteria across all three fields then? The R M and L? So it does what my current between boxes do or would it only enter criteria for one field? The issue we keep running into is that we need to determine if they had a level of <whatever number> in ANY of the three fields, not ALL of the three. My query on a query is making that happen but taking more steps than boss would like. She's a big fan of the fewest steps possible on her end to get the info she wants.

 

[Brianwarnock]

You've been at the game much longer than myself Brian.

--} bows {--



.

Yes I've forgotten more than I currently know, and the difference is increasing, however I do remember that when dealing with people who are struggling Kiss applies.
"Keep it simple stupid"
not that you are stupid, far from it.

Got to go cook the evening meal now, have a good weekend.

Brian

 

[Brianwarnock]

When you say not all 3 is that not allowed ie can you not use or?

eg a<8 or b< 8 or c<8

Brian

 

[vbaInet]

@Brianwarnock: Have a good weekend too.

 

[thewrightdesign]

When you say not all 3 is that not allowed ie can you not use or?

eg a<8 or b< 8 or c<8

Brian

I can use or and did in my queries, but when I tried to code the initial form with OR statments it wouldn't ever return any results. As long as I have it coded for AND statements it will return results but all boxes have to have input to return any and there are times we don't need to see that and want to leave them blank but still return results from the other boxes on the form. When we do query those three fields, we need to know if there is <whatever value> in any of those three fields, and sometimes we want to know if it's in all of those three.

Confused yet? I am! LOL

 

[thewrightdesign]

Yes I've forgotten more than I currently know, and the difference is increasing, however I do remember that when dealing with people who are struggling Kiss applies.
"Keep it simple stupid"
not that you are stupid, far from it.

Got to go cook the evening meal now, have a good weekend.

Brian

KISS is a very good rule to live by I am definitely struggling although decidedly less so since vbaInet helped me solve my initial search problems... and simple would be great since I have to explain it all to my boss and not only how to use it but how it works in case she decides to change things later (heaven save me from that possibility!).

 

[Brianwarnock]

I can use or and did in my queries, but when I tried to code the initial form with OR statments it wouldn't ever return any results. As long as I have it coded for AND statements it will return results but all boxes have to have input to return any and there are times we don't need to see that and want to leave them blank but still return results from the other boxes on the form. When we do query those three fields, we need to know if there is <whatever value> in any of those three fields, and sometimes we want to know if it's in all of those three.

Confused yet? I am! LOL

I am confused , it would appear that we are back to where we where four score posts ago!
Do you not need a query that has criteria of the type
Field>criteria or criteria isnull and ditto

Then the user enters one or more criteria, if none entered then only those entered are checked. Use SQL not the design grid.

One other point regarding the running of queries based on queries, aka cascading queries, the user only runs the last, the system takes care of the rest. If temp tables are involved then this is not possible but a simple solution to run multiple queries without the better solution of VBA is to write a macro running the queries and have the command button run that.

Brian

 

[Brianwarnock]

Had a bit of time today but may not have for a few days so have attached a simple example of what i understand you to be doing, I could be wrong of course!

I used the wizard to setup the command buttons but did go into the code and add the Docmd.close in case you boss modifies the criteria and reruns the query whilst it is still open. They have a habit of blaming you if the system that you didn't write doesn't work as they expect.

Brian