Query Parameters

[Appollobsg75]

Hello, I have a database, i have a few queries and the way the queries are setup is they pull Degree, Branch and Graduation date. Right now in the criteria of the Graduation Date column in my query I have a parameter between [enter start date] and [enter end date], but i want to also pull the Branch associated with the grad date, i then want to put this into a chart. at this time when i try and put into my or criteria under branch i'm able to pull the branch and grad date, but when i go to put them into the chart it tells me that Microsoft does not recognize [enter start date]. If possible can anyone give me a good way of pulling the information and of putting it into a chart?

[Gicu]

Create a form with unbound textboxes where you enter the Start and End dates and an unbound combo to select the branch. Use those in your query. You might need to open the query in design view, click on Parameters on the Design Ribbon and add the two\three parameters and set their data types.

Cheers,

[ranman256]

use the full path to the text box ,usu:
forms!fMyForm!txtStartDate

the BUILDER will always spell it right for you.

[Appollobsg75]

I will give it a try. Thank you very much.

[Gicu]

Have a look at this, should help you understand the concept:https://docs.microsoft.com/en-us/off...-qbf-technique

Cheers,

[Appollobsg75]

Thanks for you time and information. The suggestion was a good, but I think I'm looking for something else. I mean I might be not too sure. You guys are the experts and I'm trying to relay what I can using my own verbiage so I'm sorry if I might be wrong or off the reservation. I have included my DB this time, cause I was hoping that you would take a look. I have a form called CAHS Graduates Charts and On that form I have 4 charts that each access a different query, what I'm trying to do is update all of them by using a date range, i tried to put a parameter on each query to update but that does not work on form. Is there anything else I might be able to try?CAHS Graduates Statistical Test.zip

[Welshgasman]

You could use tempvars in all the queries, and just set the tempvars to relevant values

[Gicu]

Please have a look at the updated file. When using crosstab queries with parameters you need to explicitly set their data type in query design in the Parameters window.

Cheers,

[Appollobsg75]

Mr. Cucinschi, that works great. Can I create a form with unbound txt box that allows the user to enter in the branch they want to query along with the date range and then display that in a chart form? I have tried to put in the query column for the branch something like "ARMY*" and then under graduation date i put in the or section of that column the between date range, but it will not pull both accordingly. it only pulls the first criteria which is ARMY. Can I use more than one Criteria?

[Gicu]

Here you are, select branch or leave combo empty for all.

Cheers,

[Appollobsg75]

Thanks Mr. Cucinschi that is perfect for what I need. I ran into something else, i noticed when I ran certain date ranges it brought up no chart. so when i checked my data i found that in that date range for that branch there were not degrees. Is there a way to show a msgbox stating "no graduate found"? For example i did a date range form 10/01/2021 to 11/01/2021 and select Navy, but no chart came up. I tried to add is null into the select statement for the query and then a msgbox but that did not work either. Can i not added an is null then msgbox "no graduates found" to my existing query?

[Gicu]

You can use dCount to check if the source query returns records and display your message if not (needs to go before opening the form):

If dCount("*","qryBranchGrads")=0 Then
    Msgbox "No graduates found!"
    Exit Sub
End If

[Appollobsg75]

That works perfectly, Thank you very much.

[Gicu]