Between This and That but All if Blank

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!!
 
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
 
**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.
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Concatenate with line breaks but ignore blank cells 3
query to pull between two dates 4
Between This and That and all If Null 6
Is field between date range on form 2
#error - Blank 3
Parameterised query to return all records or those Between two val 2
Null date parameters 2000 2
If statement that returns all entries or one 2

Back
Top