Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32

    Unanswered: Enter Dates on Report

    Report prompts for start date and end date(I'm using between statement in the query). Would like to include the two dates in my report. Any ideas?

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Where do you call the report? From a form?

  3. #3
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    From a query

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You mean the RecordSource of the Report is a query. But you should have a button on a form to call or open your report. haven't you?

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    In this case you can put two text box [txt_date1 & txt_date2] on your form, filling them with the dates you need. In your query [Criteria section] put between Forms("YourFormName")!txt_date1 & " AND " & Forms("YourFormName")!txt_date2

  6. #6
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    I currently have in the criteria in the date column between [Enter start date]and [Enter stop date]. My report pulls from the information from the query.

  7. #7
    Join Date
    Nov 2005
    Location
    Live Oak, California
    Posts
    32
    I'm using switchboard to open the report. I enter the criteria I'm looking for: client name, start date, end date.

  8. #8
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44
    Unless there has been a change in the most recent version, you cannot reference parameters in a query. This is the reason for the suggestion above about putting the date fields on a form. You can reference the form fields from the query and on a report.
    This limitation is also why I personally never use the switchboard wizard in Access. It is just too limiting and there is always something Im wanting to do that the switchboard cannot accommodate.

  9. #9
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I would do as Hammbakka suggested. You can still use the switchboard, but you need to add a parameter form to your database and when you click on the item in the Switchboard have it open this parameter form.

    The unbound parameter form will need to have a text box for each criteria item. I suggest using a combo box rather than a text box to enter the client's name. Otherwise if the user spells the client's name wrong, they won't get any results.

    For the combo box, create a query that pulls all your client's names and sorts them alphabetically. I don't know if you're combining the client's first and last name in one field or if you're using a field for Last Name and a field for First Name. It will make a difference in how you setup the combo box.

    In the combo box on the parameter form you need to select Table/Query in the Row Source Type property and then select your query in the Row Source property.

    Then there needs to be a query for the report so you can reference the controls on your parameter query.

    In the report's query in the criteria line of the client's name you reference the control name on the parameter form like below.

    [forms]![ParameterForm]![ControlName]

    This let's the query know to look at this control for the criteria it needs for the client name. You do the same for StartDate and EndDate.

    The Control Name for each control is in the properites window in the All tab.

    Don't forget to set your report's Record Source as the query you created.

    Then in your report add two text boxes and in the Control Source of each text box you would enter:

    =Forms![ParameterForm]!ControlName

    If you decide to try this, let me know if anything is unclear. I hope I didn't leave anything out.

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Another approach that strictly uses queries would be:

    1. Select all the fields you need plus two other fields (StartDate, EndDate) (call this query Temp001 or something more appropriate).

    SELECT Orders.*, [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate
    FROM Orders;


    2. Then create a second query that uses the StartDate and EndDate to filter the records.

    SELECT Temp001.*
    FROM Temp001
    WHERE (Temp001.OrderDate Between [StartDate] And [EndDate]);


    3. For each record in the result you will have the StartDate and EndDate so you can reference these fields in any part of the report and it will be accurate.

Posting Permissions

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