Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date parameters (Access 97 SR-1)

    I have a report that summarizes sales by rep mtd and a subreport for sales by rep ytd. I tried setting the date field on my query "Between [Beginning Date] and [Ending Date]. This works fine for the mtd query but I would like the ytd query to pick up the mtd ending date parameter. I want the user to only have to input dates once. But I cannot get this to work. If I set my date parameters in my ytd query, my report works fine but then I have to go back and change my ending date each month. Does any of this make sense? I have even tried a dialog box form and referencing that in my ytd query but I cannot get that to work. Can anyone help me?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date parameters (Access 97 SR-1)

    Leef: In cases where the dates need to be used more than one time, I use a calendar control on a form to select the date and in the criteria line of the query use something like: Between [forms]![frm_availability]![beginningdate] And [forms]![frm_availability]![endingdate]. As long as the form remains open, the dates can be used. HTH.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date parameters (Access 97 SR-1)

    Referring to a text box on a form is the standard solution for this kind of problem. Say that you have a form named frmParameters with a text box named txtEndDate. You can use

    [Forms]![frmParameters]![txtEndDate]

    in criteria expressions in your queries. If the parameter is not recognized, you can declare it explicitly:
    - Open the query in design view.
    - Select Query | Parameters...
    - Under Parameter, enter the parameter exactly as in the criteria line
    - Under Data Type, select Date/Time.
    You can define multiple parameters referring to controls on a form, and use them like this (for example):

    Between [Forms]![frmParameters]![txtStartDate] and [Forms]![frmParameters]![txtEndDate]

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date parameters (Access 97 SR-1)

    Actually I was on this track but I am having a problem with my parameter form. It pops up and I enter the date criteria but my mtd/ytd information is not loading. I assume it is an event procedure problem but I don't know where. I used the dialog box form in the Northwind.mdb as a guideline.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date parameters (Access 97 SR-1)

    You will have to tell us more about how you are using the form parameters.

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date parameters (Access 97 SR-1)

    I have a main form that has an event procedure to open the dialog box form on open. My main form's control source is a query with date criteria that references the dialog box form beginning date and ending date. I also have a subform on the main form to summarize the ytd sales between #01/01/04# And [Forms]![Dialog Box Form]![Ending Date]. When I open my main form the dialog box form pops up and I enter the date criteria. But when I click OK the main form is seen but there is no information on it and my database window is displayed.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date parameters (Access 97 SR-1)

    Your original question was about a report, now it seems to be about a form and subform. Which is it?

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date parameters (Access 97 SR-1)

    Sorry, it should have said report and subreport.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date parameters (Access 97 SR-1)

    Do you open the dialog box form with the acDialog argument, as in the Northwind example? And does the OK button on the dialog form make the form invisible, as in the example?

    If you can't make it work, you can post a stripped down copy of the database:

    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]That would allow other Loungers to investigate the problem directly.

  10. #10
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date parameters (Access 97 SR-1)

    I am opening the dialog box with acDialog as in Northwind but clicking the OK does not make it disappear. I don't know if I will be able to zip the file to send it but I will see what I can do.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date parameters (Access 97 SR-1)

    Look at the code behind the dialog form in the Northwind database. The main part of the code for the On Click event of the OK button is

    Me.Visible = False

    This means that the form will be hidden, but still loaded in memory, so the query can still get at the value of text boxes on the form. The form is closed when the report is closed.

  12. #12
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date parameters (Access 97 SR-1)

    Hans,

    I appreciate all the time you are taking with this. I now have the correct code behind the OK on_click but when I try to run the report the dialog box pops up but when I enter my criteria and click OK I get the error message "To use this form, you must preview or print the mtd/ytd sales test report from the Database window or Design view". Here is my on_click code:

    Private Sub OK_Button_Click()
    On Error GoTo Err_OK_Click

    Dim strMsg As String, strTitle As String
    Dim intStyle As Integer

    ' If mtd/ytd sales test report is not being opened for previewing or printing,
    ' cause an error. (blnOpening variable is true only when report's Open event
    ' is being executed.)
    If Not Reports![mtd/ytd sales test].blnOpening Then Err.Raise 0

    ' Hide form.
    Me.Visible = False

    Exit_OK_Click:
    Exit Sub

    Err_OK_Click:
    strMsg = "To use this form, you must preview or print the mtd/ytd sales test report from the Database window or Design view."
    intStyle = vbOKOnly
    strTitle = "Open from Report"

    MsgBox strMsg, intStyle, strTitle
    Resume Exit_OK_Click

    End Sub

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date parameters (Access 97 SR-1)

    The code in the Report_Open event of the report should set blnOpening to True before opening the popup form, and reset it to False afterwards. See the Northwind database for the details. If you don't set blnOpening to True there, you'll get the error message you mention.

  14. #14
    Lounger
    Join Date
    Jan 2001
    Location
    New Orleans, Louisiana
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date parameters (Access 97 SR-1)

    Below is the code for the Report_Open. It appears that I did have it correct but let me know if it is not.

    Private Sub Report_Open(Cancel As Integer)
    ' Open MTD Dialog Box form.
    ' IsLoaded function (defined in IsLoaded module) determines
    ' if specified form is open.

    Dim blnOpening As Boolean
    Dim strFrmName As String

    strFrmName = "MTD Dialog Box"
    ' Set public variable to True so MTD Dialog Box knows that report
    ' is in its Open event.
    blnOpening = True

    ' Open form.
    DoCmd.OpenForm strFrmName, , , , , acDialog

    ' If MTD Dialog Box form isn't loaded, don't preview or print report.
    ' (User clicked Cancel button on form.)
    If IsLoaded(strFrmName) = False Then Cancel = True

    'Set public variable to False, signifying that Open event is finished.
    blnOpening = False
    End Sub

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date parameters (Access 97 SR-1)

    Since the blnOpening variable is used in the code for the form and for the report, you should NOT have a line

    Dim blnOpening As Boolean

    in the Report_Open procedure. Instead, there should be a line

    Public blnOpening As Boolean

    Public specifies that the variable can be used in all code in the database.

Page 1 of 2 12 LastLast

Posting Permissions

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