Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    New Zealand
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter Query with NULL parameter values (Access2002)

    Hi,
    I have a parameter query with a hand full of parameters. I use this query to run many times during a loop so having a parameter query vastly improves performance rather than assembling an SQL String each loop.
    The problem I have is that sometimes I need to specify the value of the parameter as "Is Null" or "Is Not Null" but this causes an error as the sql statement is in the format 'field = param' rather than 'field is null'.
    Can any body help me or give me any suggestions.

    Thanks in Advance
    Simon

  2. #2
    New Lounger
    Join Date
    Jan 2001
    Location
    New Zealand
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query with NULL parameter values (Access2002)

    For anybody interested, I did manage to find an answer to my own question.
    In the where clause of the SQL statement instead of

    field = prmValue

    which won't accept null values, try

    ((field is null AND prmValue is null) OR field = prmValue)

    so if the parameter is passed a null value it will return rows where the field is null otherwise it returns rows where the field contains the non null parameter value.

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

    Re: Parameter Query with NULL parameter values (Access2002)

    Thanks for posting your solution. We knew you could figure it out! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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