Post reply

5 paremeters but not correct results when null value present.

  • June 8, 2006 at 8:49 am

    #168553

    I have a question I hope someone can help me

    I have 5  parameter query that gets its value from 5 combo boxes from a form

     The

    following function is in the criteria grid for one of the fields:

    Like NZ([forms]![searchform]![combo69],"*")

    when I enter data for a combo box for one of the combo boxes then if there is a null value in another one of the parameters combo boxes then it does not show. so I tried this:

    Like NZ([forms]![reports_switch_main]![combo70],"*") or is null

     

    but then when i run the form and i put an value in one of the combo boxes if there is a null value in the results it shows also, i don't want that. My form has 10 columns in which some have data and some dont for all rows

    is there any way to do this? help!

     

     

  • June 9, 2006 at 4:27 am

    #642732

    You don't say exactly what you want. To use the like  + * syntax, you must be working with character or string fields in Access. I will assume you are.

    I presume you are aiming for something like this in Access

    SELECT field1,field2,field3,field4,field5,field6,field7,field8,field9,field10
    FROM mytable
    WHERE field1 like forms!searchform!Box1 & "*"
    OR field2 like forms!searchform!Box2 & "*"
    OR field3 like forms!searchform!Box3 & "*"
    OR field4 like forms!searchform!Box4 & "*"
    OR field5 like forms!searchform!Box5 & "*"

    I don't need NZ because using & means null  & "*" gives *. If you are working with non-string data then you will need to use NZ.

    OK you may get a few extra rows if the match is not exact.

    The OR means that it will match ANY of the conditions. Use AND if you wan to match ALL the conditions. I haven't put a parameters statement in because if you are working with string data then you don't need it.

     

  • June 9, 2006 at 4:49 am

    #642735

    I use something like

    COALESCE(Field1,'') LIKE COALESCE(Param1,'')+'%'

    Dick

  • June 9, 2006 at 4:51 am

    #642737

    Oops - forgot you were in Access.  More like

    Nz(Field1) Like forms!searchform!Box1 & "*"

    Dick

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply