Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Bay Area, California, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Semi-Automatic Parameter Query (2000 SR1)

    I have a report based on a parameterized query, with the following a criteria on a date field:
    "Between [Enter Begin date?] And [Enter End date?]". No problem.

    I am scheduling the report to be run automatically at a certain time a day, so I changed the parameter to
    "Between datBDate() And Date()"
    where datBDate is a function that calculates a specific Begin Date. Again no problem

    But sometimes, the users still want to run the report interactively, and be able to enter any date range. How can I change the query, so it accepts both forms?
    I tried to change the criteria to
    IIf(bolAutoRun()=True,(([tblReceipt].[RecvDate]) Between datBDate() And Date()),(([tblReceipt].[RecvDate]) Between [Enter: From what date?] And [Enter: To what date?])).

    The bolAutoRun() function returns "True" if the report is run automatically, and "False" if the report is run interactively, but it doesn't work at all.

    I also tried to use a function that returns a string based on bolAutoRun, either "Between [Enter: From what date?] And [Enter: To what date?]" or "Between datBDate() And Date()", and just place that function into the criteria field, but it didn't work (I didn't expect it to, either, makes no sense, since it returns a string, not a date).

    I saw a partial solution on The Access Web (http://home.att.net/~dashish/), to pass a parameter to a query via code (partial code snippet included):

    Dim qdfParmQry As QueryDef 'the actual query object
    Set qdfParmQry = db.QueryDefs("Qry1")
    qdfParmQry("Please Enter Code:") = 3

    ' or try this alternate method to pass the parameter

    qdfParmQry![Please Enter City:] = "New York"
    Set rs = qdfParmQry.OpenRecordset()

    But I don't know how to use this with my "Between" parameter, and I don't know how to use this with a report, which is based on my query.

    Any ideas? I'm all fresh out and sure could use some help.

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

    Re: Semi-Automatic Parameter Query (2000 SR1)

    You aren't going to be able to do it both ways from the query itself. You would be best advised to go with your original criteria ("[Enter Begin date?]", etc.) and then use code to pass the result of your function to the parameter when you need to run the query from code. The tricky part is that if you use the parameter string in your code to reference the parameter, you have to use *exactly* the same string as you do in the parameter in your query. Even a space that's different will cause it to fail.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Bay Area, California, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Semi-Automatic Parameter Query (2000 SR1)

    Charlotte,
    thanks for replying.

    When I run the report programmatically, I intend to output the report to a snapshot file using the following code (unless there is a better way):

    DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatSNP, "MyReport.snp", False

    The report's RecordSource is query "qryMyQuery", and the query has the "Between [Enter Begin date:] And [Enter End date:]" parameter on a date field.

    Could you please give me an example on how to rewrite the above DoCmd, so it feeds the two dates to the "between" parameters, assuming I use my datBDate() function and the Date() function for the two dates?

    I really appreciate your help in this.

    Klaus

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

    Re: Semi-Automatic Parameter Query (2000 SR1)

    OutputTo doesn't accept parameters, so you're doing something entirely different from what the code in your previous post addressed. You can't pass it a parameter when you use OutputTo, so you have to do it one of two ways: Either use a report with a recordsource that references a form's controls and get the user input from the form, or put the parameters in both the criteria of the query and also in its parameters, which should cause the parameter dialogs to pop up.
    Charlotte

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Semi-Automatic Parameter Query (2000 SR1)

    ... why not try using a strInput box or other input prior to the code. The user would then enter a parameter and based on the results, run the query one way or the other? This is not automated but may solve the problem.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Semi-Automatic Parameter Query (2000 SR1)

    Unfortunately, that won't help with an OutputTo command. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Bay Area, California, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Semi-Automatic Parameter Query (2000 SR1)

    Thanks all for helping.

    I think I will go a route I initially rejected, which is a form with two date fields. The dates will still be populated automatically, but can be changed for manual operation.

    I will modify the query to get the dates from the form. This should work in both manual operation, as well as in automated mode when I launch the app in the wee hours <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>.

    Let's hope the customer goes for it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Klaus <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

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