Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Ontario, Canada
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Capture parameter value

    I have an Access 97 Report that runs from a parameter query that provides a start and end date. I was able to display the dates entered by the user in the header of the report with a text box that repeated the parameter (ie. a text box that held [Please enter start date]. I am now trying to put in a text box that will calculate the number of days between the start and end dates for each report run. I can't even put in a text box that refers to the value of the dates, I keep getting #Name? instead. Any help would be very much appreciated.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture parameter value

    I have a report that is run from a parameter query where I have the dates displayed in the Report Header. I just used the following in a text box to display the difference between the dates - =DateDiff("d",[start date],[end date]).

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Ontario, Canada
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture parameter value

    Yes, I have also tried the DateDiff function, with the same result. I have check my field names and they are correct.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture parameter value

    Be sure to use the exact wording that is used in the parameter.
    Something like this:

    =[Enter The Start Date]-[Enter The End Date]

    Hope this helps.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture parameter value

    Correction! My earlier post was wronng.

    =max([DateField])-min([DateField])

    Next time I'll think before posting!

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    Ontario, Canada
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture parameter value

    Here are some of the things I've tried:

    [start] - displays 1/1/01, no problem

    [end] - displays 1/31/01, again no problem

    =[end]-[start] - displays #Name?

    =Max([end])-Min([start]) - displays #Error

    =DateDiff("d",[start],[end]) - displays #Name?

    I have played with a test parameter query that is prompting for a number instead of a date, and can manipulate it using the parameter value that was input. I seem to be having trouble with this because it is a date. Thanks for taking the time to try to help me.

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture parameter value

    Hi Gloria,

    This may not be a direct solution to your problem, but an alternative is to use a Form for parameter input. It is quite easy to pass the value from the form to the query and also from the form to the report.

    You could have 2 textboxes on the form (one for start and one for end). Then set the criteria in your query to:<pre>Between [Forms]![frmYourFormName]![txtStart] And [Forms]![frmYourFormName]![txtEnd]</pre>

    Similarly, you can do the same kind of thing in your Report Header. The advantage of using the form is that you can also add a calendar or date picker to make date entry easier on the user.

    One more thing - you may want to try the CDate() function on your current problem. Something like: =DateDiff("d",CDate([start]),CDate([end]))

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture parameter value

    I tried a test on one of my db's and was able to produce a report based on a parameter query with the expression Between [start] And [end] in the criteria row of the date column. I then created 3 textboxes and named them txtStart, txtEnd and txtDiff. In the first 2 I referenced the parameters [start] and [end] in the control source. In the 3rd I created the following expression using the expression builder for the control source of the textbox:

    =DateDiff("d",[txtStart],[txtEnd])

    When I enterd 1/1/00 for [start] and 12/31/00 for [end] txtDiff showed 365. Your date field is set to date/time data type?

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Capture parameter value

    Open up the parameter query, click on Query. Then click on Parameter. Under Parameter put the 2 names (Please enter start date etc) and under Data Type select Date/Time from the drop down list. Maybe this will help....

Posting Permissions

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