Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2007
    Posts
    8

    Unanswered: Between dates issue in a report

    Hi,

    I have a report based on a query with employees, dates and hours that also has a date parameter - Between[enter start date]and[enter end date.

    I need to put the dates into the report so the user can see what period has been requested. I have tried putting two text boxes in the header with -

    =Reports!EmpHours![Enter Start Date] and
    =Reports!EmpHours![Enter End Date]

    This actually works, but it is very annoying to have to enter the parameter dates twice for the start date and twice for the end date.

    Is there a way around this?

    Thanks

    Sid

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    [QUOTE=Sisoiy]Hi,

    Hi Sisoiy,

    Here is what I use and never have to enter it twice:

    Code:
    ="From    " & [EnterBeginningDate] & "   Through   " & [EnterEndingDate]
    Give it a whirl and see if you get it as simple as I do.

    have a nice one,
    BUD

  3. #3
    Join Date
    Oct 2007
    Posts
    8
    Hi Bud,

    Thanks for the response. It didn't work for me!

    It's like it is asking for the start/end dates for the query and for the process of putting the date on the report.

    Sid

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Sisoiy
    Hi Bud,

    Thanks for the response. It didn't work for me!

    It's like it is asking for the start/end dates for the query and for the process of putting the date on the report.

    Sid
    Hi again,

    Here is what I have in the actual query itself:

    Between [EnterBeginningDate] And [EnterEndingDate]

    The other that I posted before was what is placed in the Report Header inside a TextBox. If that's not working I'm not sure what's going on.

    Bud

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Sid,
    If you consider [Enter Beginning Date] and [Enter Ending Date] as fields in the query, then they can also be used in the Between ... And ... portion. Then, because they are fields in the query, they will be available for the report. I have never heard of a query that only has them in the criteria area and then them being available for the report.

    In order to make these actual data fields in the query, put them in the top row of the query as well as within the Between ... And ... area. this way the Report can read these two fields from the first record in the query. These two fields will have the same data in every record of the query.

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by GolferGuy
    Sid,
    If you consider [Enter Beginning Date] and [Enter Ending Date] as fields in the query, then they can also be used in the Between ... And ... portion. Then, because they are fields in the query, they will be available for the report. I have never heard of a query that only has them in the criteria area and then them being available for the report.

    In order to make these actual data fields in the query, put them in the top row of the query as well as within the Between ... And ... area. this way the Report can read these two fields from the first record in the query. These two fields will have the same data in every record of the query.
    That's strange, because in mine I have them set only as Parameters in my Query in the Criteria area. And as I stated above when I made that TextBox in my Report it was pulled over accordingly and with only entering the dates that one time. I have it right here and it still works well. But I didn't create any such Fields in the Tables/Query/Report.

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    OK Bud, now I'm going to show you! Ops, I tried it and I can not get it to "break." I have even gone so far as doing the report on a table, when the parameters in the report for the two dates, and having the report ask for those two dates, then I changed from using the table to using a query of the table. In the query I put the Between ... And ... using the same parameters as I had used in the report. I was only asked for the parameters once, and the parameters were used in the query and showed up in the report as you said.

    In other words, I stand corrected! Thank you for teaching me something. It's always good to learn. I even went so far as to have query1 ask for the parameters. Then query two uses query1 as it's input. Then the report uses query 2 as it's RecordSource. Running the report and I only get one request for the paramenters. I truly do not see any reason for Access doing such a nice thing, but it sure looks like it does.

    So, back to Sid's original request.

    ="From " & [EnterBeginningDate] & " Through " & [EnterEndingDate]
    =Reports!EmpHours![Enter Start Date] and
    =Reports!EmpHours![Enter End Date]
    The first quote is from Bud, as what he has used, and the second quote is from your first post. I finally see the problem. Because Bud just used the parameters as they are defined in the query, it works. But Sid, you have referenced the report itself and IT'S parameters by specifying "Reports!EmpHours..." If you would take out the "Reports!EmpHours." and just leave the =[Endter Start Date] (and End Date) I think it will work.

    Again, thanks Bud for the education.

  8. #8
    Join Date
    Oct 2007
    Posts
    8
    Thanks guys,

    The db is on my work pc, will check it out tomorrow.

    Sid

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's an example of what I do for dates in a query-report: http://www.dbforums.com/showpost.php...2&postcount=36

    GolferGuy - change the wording by changing 1 character in the criteria in your 2nd query and see if it prompts you a 2nd time. I believe that as long as the wording in the criteria (absent any field names) is exactly the same, you don't get prompted twice.
    Last edited by pkstormy; 10-27-07 at 18:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally, whenever I have to use user-defined parameters for a query/report, I create a small (often global, i.e. used for all my Q&R's) form with a few textboxes and asubmit button. Saves messing about with all this, that's for sure!
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Quote Originally Posted by GolferGuy

    Again, thanks Bud for the education.
    [/LEFT]
    Hey GolferGuy, it's all good. Just had to say something when I knew that lil ol me could make it work.....hehehe. After all, I learned it right here in THIS forum a pretty good while back.

    GeorgeV, I am totally interested in your way of accomplishing this. Sounds like it's simpler and neater. Do share please sir.

    have a nice one all,
    BUD

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Bud,
    I have attached a very simple DB that has a simple table with three fields, an ID field, a date field, and a name field. Two forms, a Print Report form and a Get Dates form, and then a simple report that will print the records between the selected dates that you enter using the Get Dates form. In all, there are three buttons between the two forms, and then a query the report uses to select the records to be printed. I believe it will be very easy to follow the logic through the two forms.

    One piece of explanation. The Get Dates form has both a Continue button and a Cancel button. This is how I handle all my reports. That is, any parameters that need to be received from the user, the form that handles those parameters will have a Continue button and a Cancel button on the form. And, all reports will be opened by a button on some other form. That way, I have a procedure, written in VBA that controls getting the parameters, and then controls opening/running the report. I do not allow the parameter getting form to be in control of running the report. That way, I can use the Get Dates form for ANY report that needs From and To dates.

    Any questions, please ask.
    Attached Files Attached Files

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This example will do the same thing: http://www.dbforums.com/showpost.php...2&postcount=36

    With a nice popup calendar option (except there's really no reason to open the form up as a dialog.)

    GolferGuy - I kept getting an error when trying to use your example. I changed the me.ButtonPressed = "Cancel" to Me!ButtonPressed = "Cancel" and me.ButtonPressed = "Continue" to me!ButtonPressed = "Continue" and it worked fine then.
    Last edited by pkstormy; 10-29-07 at 11:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Paul,
    The link is broken. Could you find it and re-post? Thanks.

  15. #15
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Paul,
    I'm using Access 2002, with default DB format as 2000. Did Microsoft change the ! to allow the . between 2000 and 2002? The . worked fine for me, but I've been using 2002 for over two years now. And, of course when I'm entering the VBA code, after typing the Me. Access shows the field names as available entries along with the properties and methods.

    You also said, about the popup calendar option, there's really no reason to open the form up as a dialog. Are you speaking about the calendar form, or the Get Dates form?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •