Between This and That but All if Blank

J

JohnLute

I have a query where I'm setting up fields to find between values but return
all if left blank. The between values is easy. I'm stuck on the all if left
blank part!

What do I need to add to this to make that happen:

Between [Forms]![frmPKCGDimensionsRanges]![cbShortestIDLength] And
[Forms]![frmPKCGDimensionsRanges]![cbLongestIDLength]

Thanks!!!
 
R

Rick Brandt

JohnLute said:
I have a query where I'm setting up fields to find between values but return
all if left blank. The between values is easy. I'm stuck on the all if left
blank part!

What do I need to add to this to make that happen:

Between [Forms]![frmPKCGDimensionsRanges]![cbShortestIDLength] And
[Forms]![frmPKCGDimensionsRanges]![cbLongestIDLength]
Click to expand...

Try...

(Between [Forms]![frmPKCGDimensionsRanges]![cbShortestIDLength]
And [Forms]![frmPKCGDimensionsRanges]![cbLongestIDLength])
OR [Forms]![frmPKCGDimensionsRanges]![cbShortestIDLength] Is Null
 
J

John Spencer

**IF** your field always contains a value and you could use the NZ function
to modify the criteria.

Between NZ([Forms]![frmPKCGDimensionsRanges]![cbShortestIDLength],-999999)
And
NZ([Forms]![frmPKCGDimensionsRanges]![cbLongestIDLength],999999)

You just force a minimum value that is less than or equal to the smallest
value in the field and a maximum value that is greater than or equal to bht
largest value in the table.

If you field can contain nulls then you will need to use a bit more complex
code.
 
J

JohnLute

Thanks, Rick.

That's not returning the exact data. It needs to take into account if both
cbShortestIDLength and cbLongestIDLength are null.

I tried modifying the code but have made a mess!

--
www.Marzetti.com


Rick Brandt said:
JohnLute said:
I have a query where I'm setting up fields to find between values but return
all if left blank. The between values is easy. I'm stuck on the all if left
blank part!

What do I need to add to this to make that happen:

Between [Forms]![frmPKCGDimensionsRanges]![cbShortestIDLength] And
[Forms]![frmPKCGDimensionsRanges]![cbLongestIDLength]
Click to expand...

Try...

(Between [Forms]![frmPKCGDimensionsRanges]![cbShortestIDLength]
And [Forms]![frmPKCGDimensionsRanges]![cbLongestIDLength])
OR [Forms]![frmPKCGDimensionsRanges]![cbShortestIDLength] Is Null
Click to expand...
 
Top