iif is null

[jasn_78]

hey guys i have a query where i need to say if txtPLUFROM is null select all records but if it isnt select all records between txtPLUFROMand txtPLUTO

my theory on it was as follows

IIf((Nz([Forms]![subfrmPLUFILTERS]![txtPLUFROM],0)=0),<>0,(>=[Forms]![subfrmPLUFILTERS]![txtPLUFROM] And <=[Forms]![subfrmPLUFILTERS]![txtPLUTO]))

but doesnt seem to work for when the text boxes are null it shows nothing but if they do have something in them it works on that filter

help please

 

[ajetrumpet]

IIf((Nz([Forms]![subfrmPLUFILTERS]![txtPLUFROM],0)=0),<>0,(>=[Forms]![subfrmPLUFILTERS]![txtPLUFROM] And <=[Forms]![subfrmPLUFILTERS]![txtPLUTO]))

but doesnt seem to work for when the text boxes are null it shows nothing but if they do have something in them it works on that filter

your statement says this: If PLUFROM = 0, I want values not equal to 0. If that's not true, I want values greater than PLUFROM and less than PLUTO.

If that's what you meant say, the form references are probably incorrect...Is it a subform? Looks like it,...

 

[jasn_78]

aj that is basically what i am saying. as for the subform part i posted a question on subform references on the forms thread but no1 got back to me what my question was was how do i reference the same subform in various forms?

 

[ajetrumpet]

how do i reference the same subform in various forms?

to reference a subform from outside the that subform's house (ie - the main form), you can use the same syntax as if you were inside the main form:

forms!MainformName!SubformContainername.Form!ControlNameonSubForm

 

[jasn_78]

AJ thanks that i am fine with it is more if i need to reference the same subform from different forms with the same query that i am having the problem