 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2007Multiple date parameters
I have three queries: MonthlyAll, MonthlyShows, and MonthlySpots. My main form is built off of MonthlyAll. It's main purpose is to provide a list of cities in a single form view, so that I can scroll through the cities and view the corresponding data. The main form contains two subforms: MonthlyShows and MonthlySpots. The subforms are linked to the main form by city and display the respective shows and spots by date. My question seems simple enough: Upon opening the form, I would like to type in a single date range ("Between [] And []"); and view only those shows and spots in each city. When I add "Between [] And []" criteria to MonthlyShows and MonthlySpots, it prompts me for the date range twice. Then, once the main form is opened, if I try to scoll to the next city (ie the next record), it prompts me for the date criteria two more times, once for each subform. How can I simplify this so that I only enter the date criteria one time when I open the form? Is this possible? (I tried using the solution in the thread "Multiple Date Parameters... Simplify" but I could not get it to work.) My only other option is setting the date criteria on both MonthlyShows and MonthlySpots to 1/1/07 to 1/31/07 and naming the main form "January". Then creating a form for each month thereafter. But that seems like a lot of unnecessary work. Thanks for your help! Add two textbox controls to your form. Have one be for "FromDate" and the other "ToDate". Modify the queries underlying your two subforms. In the Selection Criterion "cell" under the date field, use: Between Forms!YourMainForm!FromDate And Forms!YourMainForm!ToDate (your actual names/syntax will vary). Regards Jeff Boyce Microsoft Office/Access MVP >I have three queries: MonthlyAll, MonthlyShows, and MonthlySpots. > [quoted text clipped - 37 lines] > > Thanks for your help! Thanks for all your help. I'm still relatively new to this, so I'm requesting come clarification. (And probably asking some stupid questions along the way)... When you say add two textbox controls, do I need to add them to my originial MontlyAll table... then to my MonthlyAll query... and then they'll show up in my field list and I can add them to my form? > Add two textbox controls to your form. Have one be for "FromDate" and the > other "ToDate". [quoted text clipped - 52 lines] > > > > Thanks for your help! A bit of Access terminology (or jargon ... <g>). In Access, the things in tables are fields (the SQL folks call them columns). The things in forms (and reports) that show what's in the tables (and other stuff) are called controls. So my earlier suggestion was to open your form in design view and add the two new controls, NEITHER of which connect to any table fields. They will be temporary buckets into which you'll add data parameters. Regards Jeff Boyce Microsoft Office/Access MVP > Thanks for all your help. I'm still relatively new to this, so I'm > requesting [quoted text clipped - 71 lines] >> > >> > Thanks for your help! I hate that I'm a newbie and just don't seem to be getting this... I really do appreciate your patience. I tried adding two text controls (thanks for the jargon). But when I open the MonthlyAll form, it does not prompt for dates. There is, in fact, no data at all in the subforms. The two text controls show up as "#Name?". When I open MonthlySpots and MonthlyShows independently, it prompts me "Forms!MonthlyAll!FromDate", so it seems like I did at least one step correctly. Thanks again for your help. > A bit of Access terminology (or jargon ... <g>). In Access, the things in > tables are fields (the SQL folks call them columns). The things in forms [quoted text clipped - 85 lines] > >> > > >> > Thanks for your help! When "#Name" shows, Access is telling you that something is spelled differently that it expects. Or that the control is bound to a table field that it doesn't recognize. Click on one of the new controls. Select properties. If there's anything in the Control Source property, delete it... the two controls need to be unbound. Next, I hadn't realized that you wanted the FORM to prompt you for a date. The approach I offered requires you to enter dates in the two fields (if you wish to), before running the query. The queries will not "find" the date values unless the form is open and dates are in the From and To controls. And if I left it out last time, add a command button to your form that runs the report (based on the query, based on the form's From/To values). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP >I hate that I'm a newbie and just don't seem to be getting this... I really > do appreciate your patience. [quoted text clipped - 112 lines] >> >> > >> >> > Thanks for your help! |
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
|
©2009 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.