Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: parameter query accepting null values

    I have a parameter query that is launched from a form containing three unbound fields. The first two fields prompt for a date range and the third field prompts for a value [OfficeName]. The [OfficeName] field is a combo box that looks up values from [tblOfficelist]. I would like the query to accept either an [OfficeName] value or a Null value. If there is a Null value, then I would like ALL OfficeNames included.

    Based on information I received on the following web sitehttp://support.microsoft.com/default...81&Product=acc) , I have set the criteria in the [OfficeName] field to:

    Like [Forms]![HelpDeskDetailbyOfficeForm]![OfficeName] & "*"

    and then created an additional column in the query set to:
    [Forms]![HelpDeskDetailbyOfficeForm]![OfficeName] or Is Null

    The strange thing is that when I run the query without the form and leave the [OfficeName] field blank it works great! But with the form, it gets hung up when formatting the page and I have to press Ctrl-Break.

    Your help/ideas/suggestions is greatly appreciated.

    Thank you.

  2. #2
    Join Date
    Mar 2003
    Posts
    46
    set criteria to

    IIf(Len([Forms]![HelpDeskDetailbyOfficeForm]![OfficeName])=0 Or IsNull([Forms]![HelpDeskDetailbyOfficeForm]![OfficeName]),"*",[Forms]![HelpDeskDetailbyOfficeForm]![OfficeName])

    should do the trick


    CCC

  3. #3
    Join Date
    Mar 2003
    Posts
    46
    Hmmm...no...sorry, different solution:

    Create an unbound textbox called 'criteria' on your form (set it to Visible=No) with control source =IIf(Len([OfficeName])=0 Or IsNull([OfficeName]),"*",[OfficeName])

    then set your criteria to

    Like [Forms]![HelpDeskDetailbyOfficeForm]![criteria]


    That should do it..really! )

    C

  4. #4
    Join Date
    Feb 2004
    Posts
    142
    Heres a nifty trick

    In the criteria type
    Like nz([Forms]![HelpDeskDetailbyOfficeForm]![OfficeName],"*")

    This will work well with strings.
    KC

Posting Permissions

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