Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    standalone reports

    Hi,

    Working in Access 97. Not exactly a beginner but would like an explanation to know if what I want to do just can't be done.

    I had a report whose record source was a query. The query would check for records between a span of dates, which were provided on a switchboard (ie, the criteria for the date field in the query was something like
    Between [Forms]![Switchboard]![startdate] AND ... [enddate]
    I may not have the brackets and ! exactly right here since I'm going by memory. But whatever it needed to be was OK and the report generated the expected info.

    The dates were always the "start of this month thru the end of next month" so I used Datepart and Dateadd to calculate the needed controls; these displayed ok also.

    I decided I didn't want to have a separate query in case the users would start exploring the queries and changing them. So I changed the record source of the report to be a SELECT stmt. In fact, to start with, I just copied the SQL of the "external" query into the record source for the report. No problem.

    Then I took the next step. Rather than have the dates in the switchboard, I tried putting them on the report itself. I just copied the source of the controls from the switchboard to the corresponding report controls - no typing involved. I changed the criteria in the SELECT statement to refer to the names of the controls on the report, rather than the switchboard. Boom. The report no longer worked. I got an invalid expression. If I deleted the BETWEEN from the criteria, the report ran but showed all records.

    Tried the next step. Created the criteria using the Datepart and Dateadd functions (again cutting and pasting). No good.

    Tried one last thing. Added an extra column to the SELECT for the report (really the query created by it) to calculate the start date and gave it a name. Did the same for the end date. Then used the expression names in the criteria. Still no good. So I gave up and for now am using the switchboard dates.

    Can I use the dates as I want or not? If so, how? Thks.

    Fred

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: standalone reports

    If I follow correctly, you want the user to input a start date and end date for selecting data, and then use the resulting data in a report? If this is the correct interpretation, I have something that just might work for you.

    For a very similar situation, I set up a separate table to hold the desired report dates (and other user-selectable report criteria). The query included both the source data table and the select data table, and returned only the records that matched. A form was set up to access this table, allowing the one record to be edited, but without allowing additions or deletions. After entering the dates in the form, clicking on a button to "Continue Processing" opens the report, automatically pulling the data through the query.

    In order to get a match on a date range to a single date field in the data table, I did not use a relationship to select the data. Instead, I set up a criteria for the date field, referencing back to the select data table:

    >=[SelectData.StartDate] AND <=[SelectData.EndDate]

    This would match up the single date field stored in the main data table to both of the dates specified.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: standalone reports

    You can't enter criteria into a report, although you can accept user input into the parameters of the query behind the report. Instead of having a reference to Forms!Switchboard!StartDate or Forms!Switchboard!EndDate, put a parameter into the critiera line of the query in the date column:

    Between [Enter StartDate] and [Enter EndDate]

    That should return the dates you're looking for in your report.
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: standalone reports

    Charlotte, Dave,

    Thks for the quick replies. Perhaps one thing wasn't clear from my post - that being what the start and end date are.

    the report shows those candidates that are starting a job during the current and next months. Records would have been entered previously for each candidate with his/her starting date at the new job.

    So the dates are always known and constant relative to today (as noted by the PC). That is, the start is the 1st of the month that we are currently in and the end is 2 months after that (ie, end of next month).

    Given that these are "fixed", there's no need to prompt. If the user wants a variable span, then I could go that route. They haven't asked for that (yet).

    I may try Dave's solution but not sure of that either. Thks. If any of the above changes answers, I'd appreciate another round.

    Fred

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: standalone reports

    To have the report use the first of the current month enter the following into a calculated textbox: = DateSerial(Year(Date()),Month(Date()),1)

    For the end of the month 2 months later enter this into a calculated textbox: = DateSerial(Year(Date()),Month(Date())+3,0)

    No prompts are necessary.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: standalone reports

    Since you are working with dates relative to when the report is actually being run, use the following formulas to get your two dates.
    <hr>StartDate: DateSerial(Year(Now()),Month(Now()),1)
    EndDate: DateSerial(Year(Now()),Month(Now()+2),1)-1
    <hr>
    This gives a starting date equal to the first day of the current month, and an ending date equal to the last day of the following month. These formulas can easily be built right into your query as criteria, using >= StartDate AND <= EndDate

Posting Permissions

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