Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Default Values for Report Parameters (Access 2002 (XP))

    I'm stuck with the following mystery: A report textbox contains the following RecordSource property:

    ="Collect Between" & Format(IIf(IsNull([Begin of Period (day/month)]),"1/1",[Begin of Period (day/month)]),"d mmmm") & " and " & Format(IIf(IsNull([End of Period (day/month)]),"31/12",[End of Period (day/month)]),"d mmmm")

    The Query parameters [Begin of Period (day/month)] and [End of Period (day/month)] are defined in the appropriate Query Parameters window as Date/Time

    Date values are expressed in a continental way, in agreement with the System Properties. (I don't think this notation has anything to do with my problem)

    Now the mystery: The text appears correct on the report when:

    - Both parameters are entered e.g 2/2 and 3/11
    - Only one of the parameters is entered and the other one not e.g. 3/4 and OK button for [End of Period ...] or OK for [Begin of Period ...] and 5/10

    The report textbox produces an #error when BOTH parameters are OK'ed. Why???

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

    Re: Default Values for Report Parameters (Access 2002 (XP))

    There must be something else causing this, the expression itself it OK. How do you handle the parameters being empty in the query?

    I defined the parameters in a dummy query, and put a text box on a dummy report based on the query. I pasted the expression from your post into the record source of the text box. Clicking OK twice yields

    Collect Between1 January and 31 December

    (there is no space after 'Between' in the expression)

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

    Re: Default Values for Report Parameters (Access 2002 (XP))

    If you think about it, it's logical. If you just press OK in the parameter input box for [Begin of Period (day/month)], this parameter will be empty. You handle this in the criteria and use IIf to replace this empty value by 1/1, but the parameter [Begin of Period (day/month)] itself is still empty! The query passes this empty parameter to the report, where it causes the text box to lack a starting date.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Re: Default Values for Report Parameters (Access 2002 (XP))

    My erroneous understanding was that the expression was equivalent (in traditional programming languages) to:

    [Begin of Period (day/month)] = IIf(IsNull([Begin of Period (day/month)]),"1/1",[Begin of Period (day/month)])

    but you suggest that the correct understanding is somewhat more like the following:

    [Some Query internal variable] = IIf(IsNull([Begin of Period (day/month)]),"1/1",[Begin of Period (day/month)]) but the unmodified [Begin of Period (day/month)] is passed to the report in stead of the [Query internal variable]

    Could you agree?

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

    Re: Default Values for Report Parameters (Access 2002 (XP))

    What you're actually saying is between BeginDate and Null which doesn't make any sense, especailly since Null can't be used for comparisons. Between ... And is a shortcut that actually represents >=<value 1> AND <= <value2> and it requires both expressions or it fails.
    Charlotte

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

    Re: Default Values for Report Parameters (Access 2002 (XP))

    The point is that the code you posted (not your previous post) isn't making that kind of substitution. It is simply formatting the result of the parameter dialog. If you get a null in that parameter, you can't use it in a comparison.
    Charlotte

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

    Re: Default Values for Report Parameters (Access 2002 (XP))

    Your latter statement is correct, and I don't see how it could be any other way.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Re: Default Values for Report Parameters (Access 2002 (XP))

    Charlotte, I thought that IIF(IsNull(x),value1,x) would replace the NULL by value1. So I fail to see your point.

  9. #9
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Re: Default Values for Report Parameters (Access 2002 (XP))

    Hans, thanks for hinting me in the right direction: I didn't check for NULL values in the query itself. So, I modified the underlying query as follows:

    WHERE ..... And ((Format(qryOLO.DueDate,"mmdd")) Between Format(IIf(IsNull([Begin of Period (day/month)]),"1/1",[Begin of Period (day/month)]),"mmdd") And Format(IIf(IsNull([End of Period (day/month)]),"31/12",[End of Period (day/month)]),"mmdd"))

    while at the same time keeping the RecordSource of the Report textbox (except for the cosmetic space change) as

    ="Collect Between " & Format(IIf(IsNull([Begin of Period (day/month)]),"1/1",[Begin of Period (day/month)]),"d mmmm") & " and " & Format(IIf(IsNull([End of Period (day/month)]),"31/12",[End of Period (day/month)]),"d mmmm")

    So, both the query and the report were in total agreement and this did the job.
    ================================================== ===============

    After this change, i thought why double checking for NULLs? If the query does the job why should I repeat this for the reporting? I subsequently changed the textbox RecordSource to:

    ="Collect Between " & Format([Begin of Period (day/month)],"d mmmm") & " and " & Format([End of Period (day/month)],"d mmmm")

    while maintaining the NULLs checking in the query.

    To my surprise, the textbox showed:

    <font face="Comic Sans MS">Collect between and</font face=comic>

    No dates appeared!!

    So although my specific problem is solved I'm still puzzled by this behaviour. Sometimes the query seems to supply the values, sometimes the report seems to do it (see original question).

    Can you (or anybody) tell me when the parameters acquire their value? and what component does supply them?

Posting Permissions

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