Multiple parameter query and empty parameter

[vbaInet]

=DMin("ResultDate","DeadRecords")
in the Control Source of txtAltStart

=DMax("ResultDate","DeadRecords")
in the Control Source of txtAltEnd

exactly as above.

And I'm hoping that DeadRecords is not the name of the query?

 

[thewrightdesign]

=DMin("ResultDate","DeadRecords")
in the Control Source of txtAltStart

=DMax("ResultDate","DeadRecords")
in the Control Source of txtAltEnd

exactly as above.

And I'm hoping that DeadRecords is not the name of the query?

Ok putting that in now. DeadRecords is the name of the Table

 

[thewrightdesign]

=DMin("ResultDate","DeadRecords")
in the Control Source of txtAltStart

=DMax("ResultDate","DeadRecords")
in the Control Source of txtAltEnd

exactly as above.

And I'm hoping that DeadRecords is not the name of the query?

Success!! This has been the most informative experience I've had with this yet. Thank you so very much. This worked like a charm and I think it has shown me how to solve the issues in the bigger database I was building. I'm excited to try this out to see.

You have been incredibly helpful and not just in getting it to work but in helping me understand why it works (I think LOL). Thank you!!

 

[vbaInet]

Glad to hear that you learnt something today.

It's part of your 5-a-day

Happy developing!

 

[thewrightdesign]

Last question, how do I now tie this to a report? I had a report based on the query, but is it possible to send the results from the form to a report? LOL sorry, thought I was done

 

[vbaInet]

Make the same query the Record Source of the report and remember to keep the form open.

 

[thewrightdesign]

Make the same query the Record Source of the report and remember to keep the form open.

That was it, didn't have the form still open after running the query. Left it open this time and clicked the report and it was all there perfectly. Thanks again so much, you've been incredibly helpful!

 

[vbaInet]

Happy to help!

 

[thewrightdesign]

=DMin("ResultDate","DeadRecords")
in the Control Source of txtAltStart

=DMax("ResultDate","DeadRecords")
in the Control Source of txtAltEnd

exactly as above.

And I'm hoping that DeadRecords is not the name of the query?

I've moved on to the next portion of building this database. Now that my search works on all paramters as needed, we need to add in a few more. This time searching on three fields all still in the main table. The fields are R, M, and L. Values are numeric with one decimal. We want to add this to the form you helped me build.

We'll need to search each of the three with one of three searches. the three possible searches are going to be 8.0 and higher, 10.0 and higher, and less than 8.0.

Am I right in thinking we need alt text boxes like we have for the start and end dates and that in those boxes there would be code similar to the DMin/DMax and then in the query it would use the Nz again?

 

[vbaInet]

You only need Nz() when you're doing a BETWEEN. Follow the other Is Null example and make sure you put it on one line.

 

[thewrightdesign]

You only need Nz() when you're doing a BETWEEN. Follow the other Is Null example and make sure you put it on one line.

Thanks for explaining that. I get the putting it on one line just like the institution and pass lines are with their nulls.

How do I incorporate the conditional statements?

 

[vbaInet]

Where is the 8.0 and 10.0 coming from?

Are you saying all three conditions will need to be applied to the field R, M and L? Or do you mean > 8.0 for R, > 10.0 for M and < 8.0 for L?

 

[thewrightdesign]

Where is the 8.0 and 10.0 coming from?

Are you saying all three conditions will need to be applied to the field R, M and L? Or do you mean > 8.0 for R, > 10.0 for M and < 8.0 for L?

R, M, and L each contain a value (grade level). EACH of the three fields we need to be able to search to find the records where a student is below 8.0 grade level, at or above 8.0 grade level, and at or above 10.0 grade level.

So all three values need to be able to be searched on all three fields. This goes on the same form as the other search, so I'm guessing I'll need input boxes for each (unless there is some way to have one input box for the number and it searches all three fields but I can't imagine how that would work).

 

[vbaInet]

You're talking about three criteria but based on two values. So you need two textboxes.

Plus your criteria isn't logical either.

Why have > 10 if you already have > 8
And why have > 8 and < 8. Meaning, all records except 8.

 

[thewrightdesign]

You're talking about three criteria but based on two values. So you need two textboxes.

Plus your criteria isn't logical either.

Why have > 10 if you already have > 8
And why have > 8 and < 8. Meaning, all records except 8.

This isn't one search each time. I need to be able to pull all records where a student scored 8.0 in any of the three fields (R, M, L). I also need to be able to find all records where a student has a grade point level below 8.0 in any of the three fields. I also need to be able to see which students have a grade point level of 10.0 in any of the three fields. Those three fields are areas of study: Reading, Math, Language. Students can have a different grade level for all three of those fields. The grade levels range from a low of maybe 6.0 (6th grade) to a high of 12.9 (end of 12th grade).

Let me try to clarify... We currently have a policy that states students can only take the GED classes if they have a grade point level of 10.0. We have data showing that those with a grade point level of 8.0 also pass the GED test which asks the question do we need to lower that policy standard. Therefore, we will need to do a search on all students that passed the GED test and had a grade point level of 10.0 or above. But we will also need to search all students that passed the GED but had a grade point level of 8.0 or above (different search entirely). We also need to search for all records where the grade point level was below 8.0. (another search) Yes I can search 8.0 and up and get the records for 10.0 and up in there, but then I'd have to further filter that search if I wanted to pull out just those that were 10.0 or higher.

We won't be searching all three criteria at the same time. These are all separate searches depending in the information we are trying to retrieve at a given time.

 

[vbaInet]

If you want to search using two different operators then you need to create two queries for each operator (for simplicity).

One for > and another for <

If this was my project I would write code for to do both but you don't know code so stick to the queries option.

 

[thewrightdesign]

If you want to search using two different operators then you need to create two queries for each operator (for simplicity).

One for > and another for <

If this was my project I would write code for to do both but you don't know code so stick to the queries option.

Is that going to take a separate form then? or at least a command button for each query that I have to write? Thinking this through... that would mean the form as it is now, but with three more input boxes, one for R, M, and L and two more buttons to run two more queries, one for < and one for >? It can't all be written into one query using the format we did for the other search?

 

[vbaInet]

You can't have a conditional operator in a query because it will be read as text.

One button to run the query
Three textboxes - one for > and <, one for first criteria and the other for the second criteria.

 

[thewrightdesign]

You can't have a conditional operator in a query because it will be read as text.

One button to run the query
Three textboxes - one for > and <, one for first criteria and the other for the second criteria.

I'm sorry, I guess I am just really not understanding. You said to create two queries for each operator. Operator being the conditional > and < correct? And I understand what you just said to be that I can create one textbox where the user inputs either > or <. Then the next text boxes (criteria) I'm assuming to mean for the value of 8.0 or whatever. Since I'm not needing to do a between then what is the second criteria box for? And will this one instance of text boxes search on all three fields?

Either I'm really not understanding what you're saying to do or I haven't made what I need clear enough for you to be able to help... not sure which. Sorry about that, picturing all this in my head and just not making the right connections to make it make sense yet.

 

[vbaInet]

Based on the selected operator, you open the appropriate query. Makes sense now?

Look into the IF statement.