Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    138

    Exclamation Unanswered: Open a report via a form

    I am trying (for hours) to use a form to open a query/report. I have used the expression below and I can not figure out what I am missing. All I want to happen is when I open the query or report I want one box to pop up (the form) and ask me the start and end date in one box, rather than having to enter the begin date and then hit "enter" and the enter the end date and then hit "enter"

    >=[forms]![Report Date Range]![BeginDate] And <=[forms]![Report Date Range]![EndDate]

    Please help

  2. #2
    Join Date
    Apr 2005
    Posts
    6
    try using this:

    Between [forms]![Report Date Range]![BeginDate] And [forms]![Report Date Range]![EndDate]

    also make sure you have your report parameters set for each [forms]![Report Date Range]![BeginDate] and [forms]![Report Date Range]![EndDate] and that they are set as date/time so they will be properly formatted when the report brings them in (Query menu > Parameters...)

    hope this helps

  3. #3
    Join Date
    Dec 2003
    Posts
    138

    Open Report

    I want to be able to enter the parameters in the box when the form appears. I got the idea from a template on microsoft.com, I just can' figure out why it won't work for me.

    It opens report Product purchases by supplier with a the report date range form. I viewed the properties of the report to get the expression. I copied the form and changed the expression to my fields but it won't work

    I have attached it if you care to look at it
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2005
    Posts
    144
    can you upload your database so i can try helping you?
    Thanks

  5. #5
    Join Date
    Apr 2005
    Posts
    6
    hey sorry i couldnt open that db.. im only running AC2000 here.

    here is a brief overview of my setup though.

    i have a form that has a list of reports you can run. i use combo boxes to select start/end dates

    on_submit, i format the values -> format(cboStartYear.value & "-" & cboStartMonth.value, "yyyy-mm") <- and pass them to hidden text boxes also on the form.
    ie. txtBeginDate.value = 2005-01, and txtEndDate = 2006-01

    in my query (im using a chart, but it should work the same w/ a regular query) i have a date field where dates are in the format i just used (yyyy-mm). in the criteria section for my date field i have the 'between [form]![report_selector]![txtBeginDate] AND [form]![report_selector]![txtEndDate]

    Then i went into the Query > Parameter menu and entered the 2 parameters there

    [form]![report_selector]![txtBeginDate] | Date/Time
    [form]![report_selector]![txtEndDate] | Date/Time

    then as long as that form is currently open, and the hidden txt fields have my start/end dates in them, the data is passed correctly

  6. #6
    Join Date
    Dec 2003
    Posts
    138
    It is attached above

  7. #7
    Join Date
    Apr 2005
    Posts
    6
    hey i was able to track down a pc w/ office 2003 on it

    all i did was add the parameters to the query report and it seems to be working
    i tested on date range 06/06/2005 - 06/06/2006

    check out the query for the report and you'll see the difference

    j
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2003
    Posts
    138

    Help

    Yes I know that one works that is where I got the idea. I can not get that same philosophie to work in mine. I have attached it.

    When I click in Reports and click on View Reports

    When the report list appears I want to be able to enter the date range in the boxes to the right. I tried the expression that the first database is using in the query and it won't work in mine. I must be missing something.

    The expression is
    >=[forms]![Report Date Range]![BeginDate] And <=[forms]![Report Date Range]![EndDate]

    All I want is for the same thing that works in the first database to work in this one.
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    just going to take a look..... write back in a little moment

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  10. #10
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Talking

    Greets;

    Here's you DB back with an amendment.

    You have a new table (datecriteria) and also a new form to match. What you need to do in the query you are basing your reports on (where you want the date grouping) add in this new table then add this expression to the date field you are using (in criteria)

    >=[Start date] AND =<[End date]

    This should happily filter out records that fall between the two dates. The instead of opening your form form the switchboard, open the new form i created for you that allows the input of these two dates, add a button in as a link to your report and voila!

    Hope this helps;

    dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  11. #11
    Join Date
    Dec 2003
    Posts
    138
    We got this figured out!!! Thanks a bunch for your time, but it turned out that we were just missing the Utility Functions.

    Dendalee

Posting Permissions

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