 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005parameter query help
I am wanting to run a report for all 24 branches based off one report. The reason I need this one report is because all the data reporting will be the generally the same, just different branch info, team info, and detail. I want the user to be able to select the date range, a branch name, a team name, and lastly a detail type. This will generate the total budgets for the teams, but also broken down by branches that belong to that team, and the type or purchase weather it came from the advertising budget or PR budget ( these are the two types of detail). The table looks something like this. Date Field Branch Field Team Field Detail field Amount of Purchase 06-jul-05 Champaign Dominance Duo AD 34.99 04-jan-05 Watseka Dominance Duo PR 55.66 06-aug-05 Lincoln Lariats AD 44.77 05-Feb-05 Harrisburg Posse PR 33.56 This is a sample see why t needs to be just one parameter query? All the field names are the same just little different data in each. Their is a relationship between the teams and branches so the database knows what branch belongs to what team and so on. ANy help would be appreciated :) Ryan Create a form where the user can enter the values for the parameters. For example, if the form is named Form1, and it has an unbound combo named Branch for selecting the branch, you can enter this into the Criteria row under the Branch field in your query: [Forms].[Form1].[Branch] It is a good idea to delare your parameters. In query design view, choose Parameters on the Query menu, and enter them one per line, e.g.: [Forms].[Form1].[Branch] Number This is essential if your query is a crosstab. If you are not using a Totals query, you could just build the WhereCondition string when you click a button on your form to open the report. For an example see: Limiting a Report to a Date Range at: http://allenbrowne.com/casu-08.html  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >I am wanting to run a report for all 24 branches based off one report. The > reason I need this one report is because all the data reporting will be [quoted text clipped - 30 lines] > > Ryan Allen thank you for the help with the parameters... I have a question that goes along with what you wrote. I was able to write a parameter query that allows the user to input the date range (and used the parameters like you explained) between [startdate] and [enddate]. The next parameter question asks the user to input the branch name and then the detail type. If the user spells them incorrectly the query does not run correctly. Is their a way to make a drop down for the parameter so they can just click on it. I am also wondering how to do the parameter if the user wants to find out both AD and PR detail types in one query. If their a way to select both items and them run the query, or can you only select one at a time. Hope this makes sense as I am very new to Access. Ryan > Create a form where the user can enter the values for the parameters. > [quoted text clipped - 49 lines] > > > > Ryan No, you cannot put a combo box in a parameter. Parameters are really a fudge of an interface. If you use a form (as described in part 2 of that article), you can put a combo on your form, and refer to it as a parameter, i.e. the Criteria row in your query contains something like: [Forms].[Form1].[Combo1] SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen thank you for the help with the parameters... I have a question that > goes along with what you wrote. I was able to write a parameter query that [quoted text clipped - 71 lines] >> > branch >> > belongs to what team and so on. I created the form as described in part two, I am confused as how to run it... I do not have a report named rptsales but I do have a report named rptcoolrunning. I am confused as to how to link the form to the report and also how to keep the other parameters I need to make the report complete i.e. team info, branch info, and detail. > No, you cannot put a combo box in a parameter. Parameters are really a fudge > of an interface. [quoted text clipped - 79 lines] > >> > branch > >> > belongs to what team and so on. follow-up... when I click on the OK command button after putting the dates in nothing happens. Is it not supposed to launch the report or at least the rest of the parameter info in the report?? Do I need something else in the report to tie the form in ?? > No, you cannot put a combo box in a parameter. Parameters are really a fudge > of an interface. [quoted text clipped - 79 lines] > >> > branch > >> > belongs to what team and so on. Okay, we are talking Method 2 in this article: http://allenbrowne.com/casu-08.html At step 6, you set the On Click property of your button to: [Event procedure] and click the Build button (...) beside that. Access opens a code window. Paste the lines of code into that window. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > follow-up... when I click on the OK command button after putting the dates > in [quoted text clipped - 105 lines] >> >> > branch >> >> > belongs to what team and so on. I did all that, I even changed the name of the strreport = rptcoolrunning and the strfield = Date. I am still unable to pull up any report after the dates are entered and I click on the ok button. If I click the cancel button It does close so that works. Is their anything else that I am missing. Gosh Access is a pain hahah. Ryan > Okay, we are talking Method 2 in this article: > http://allenbrowne.com/casu-08.html [quoted text clipped - 114 lines] > >> >> > branch > >> >> > belongs to what team and so on. You have a field named Date? That's not going to work reliably, because Date is a reserved word in VBA (for the system date.) Rename the field, and change any queries, forms, and reports that are affected by the name change. Before you do, make sure the Name AutoCorrect boxes are unchecked under Tools | Options | General, or Access will get thoroughly confused about what you are referring to. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >I did all that, I even changed the name of the strreport = rptcoolrunning >and [quoted text clipped - 146 lines] >> >> >> > branch >> >> >> > belongs to what team and so on. I re-named the field to mydate. I did change the strdate =mydate. I put the start date and endate in again and clicked ok and the report dd not come up?? A new question I have is related to running sums on a data access page. I have an amount of purchase field that keeps track of the total amount of purchases for the branches. I need the dap to have a section where when they run the parameter query where it is set for the user to enter a date range, team name. The results show the team, detail type, vendor name, and so on. The last box shows the amount of purchase. Is their a way to have the runninf total show on the footer. Where the branch budget for that year is 25,000$- amount of purchase??? and this has to be runningtotal every month so I do not have to do any random configuring of my own every month. Ryan > You have a field named Date? > [quoted text clipped - 156 lines] > >> >> >> > branch > >> >> >> > belongs to what team and so on. If nothing happens when you click the button, add this line to the top of the procedure: Stop When you click the button, it should now stop at this line, and you can press F8 to execute one line at a time to see what is happening. Please start a new thread for the new question. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >I re-named the field to mydate. I did change the strdate =mydate. I put the > start date and endate in again and clicked ok and the report dd not come [quoted text clipped - 16 lines] > > Ryan |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.