Results 1 to 14 of 14
  1. #1
    mrswarrick is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10

    Multiple Parameter queries reporting- using the same parameter- enter mulitple

    I have a simple database run off of an excel spreadsheet that generates dates that a particular job needs to be done. I run it once a week to tell me what needs to be done the next week. The data is added to as new work is added each week. The excel spreadsheet has fields that are calculated by the dates- ex. 11/12/11+3 would generate in the cell 11/15/11.
    I set up queries to look at each column of results (work task) and tell me which experiments are ready for that task. I then have one report that runs grabbing each query individually and prompting for a date range for the week. I have 6 of these so when I run the report I enter the same start date 6 seperate times and the same end date 6 seperate times.


    How do I enter the date only once for the whole report?
    I tried using the wizard to group them all together, but if they run together it knocks them out if it was a null for the previous column.
    Example of excel sheet
    Experiments Step 1Step 2Step 3Step 4Step 5Step 6Step 7Experiment 110/31/201111/6/201111/27/201112/2/201112/13/201112/23/20111/2/2012Experiment 210/31/201111/6/201111/27/201112/2/201112/13/201112/23/20111/2/2012Experiment 310/31/201111/6/201111/27/201112/2/201112/13/201112/23/20111/2/2012

    If anyone can help or point me in the direction of help, I will appreciate it.
    Thanks
    Lacey

  2. #2
    mrswarrick is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10
    That excel sheet didn't look like that when I put it in the field.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would have the user enter the two dates on a form, and have the queries look to the form for their criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    mrswarrick is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10

    How to:

    Thanks for your response. Can you point me to a good tutorial/instructions to create a form that lets me put in 2 dates and then set the queries to look at the form instead of my date range? I don't mind fumbling around on my own but if you already know a worthy source it would be a great help! I'm a novice access user, not very savy.
    Thanks

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't think of a tutorial offhand. Just create a form with a couple of textboxes and a button. The user enters dates in the textboxes, and the button starts your process, whatever it is. Instead of:

    [Enter date]

    in the query criteria you'd have

    Forms!FormName.TextboxName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    mrswarrick is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10

    try

    I will play around with it monday. Ill let you know if I'm successful.

  7. #7
    mrswarrick is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10

    almost there

    I set up the form with 2 text boxes to put in the two dates. I went in to each of the 6 queries and changed the line that had the between dates to point it to the form.
    It looks like this
    WHERE (((Calendar.R1) Between [forms]![PickDate]![date1] And [forms]![PickDate]![date2]));
    I have the form open, I input the two dates and then I open the form and magic happens and the report is generated.....but it's blank.
    What I am curious is happening may be what happened when I tried to generate a wizard report. It's like it ties them all in together.
    Any ideas?
    It was so nice to just press that button once, I feel so close to a resolution!!!!

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That looks correct, as Access often adds the brackets around Forms. Can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    mrswarrick is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10

    Too large

    It is apparently too large...

  10. #10
    mrswarrick is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10
    Tried making it smaller....can only get it down to 600 KB. I did the compact repair and deleted everything I felt wasn't necessary.

    Could my problem (not generating results) be stemming from my report being built with 6 smaller reports?

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can email it to

    removed for privacy
    Last edited by pbaldy; 01-09-2012 at 06:56 PM.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't test without data, but you need the names of the textboxes, not their labels. The names at present are Text5 and Text7, so try

    WHERE Calendar.R1 Between [forms]![PickDate]![Text5] And [forms]![PickDate]![Text7]

    And since you aren't firing off the report from the form, make sure to tab off the second textbox you enter a date into, or Access won't see the input. I've made the mistake of typing in a value and then opening a report or query from the Navigation Pane, and it doesn't work. Access doesn't "see" the value you've typed into a textbox until you've hit tab or enter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    mrswarrick is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10

    Woohooo!!!!!

    Thanks so much for your help! I knew it could be done!

    I changed the boxes names from Text5 to date1 and Text7 to date2...figured that would be easier than having to go back into each query and change the word.

    I ran it and it worked! If I wasn't at my desk I would squeal with delight!


  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    LOL!! Go ahead, squeal; let the other people be jealous.

    Glad we got it working for you. Welcome to the site by the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Enter Parameter Value
    By kwooten in forum Queries
    Replies: 13
    Last Post: 08-11-2011, 02:35 PM
  2. enter parameter value
    By alliandrina in forum Access
    Replies: 1
    Last Post: 06-05-2011, 01:02 AM
  3. handling nulls in mulitple parameter query
    By haggisns in forum Queries
    Replies: 5
    Last Post: 10-14-2010, 02:09 PM
  4. Enter Parameter Value
    By T001 in forum Access
    Replies: 1
    Last Post: 08-11-2010, 05:43 AM
  5. Enter Parameter Value
    By plesser in forum Access
    Replies: 1
    Last Post: 11-08-2008, 10:27 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums