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

    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 site , 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
    set criteria to

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

    should do the trick


  3. #3
    Join Date
    Mar 2003
    46, 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! )


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

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

    This will work well with strings.

Posting Permissions

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