Between This and That and all If Null

[johnlute]

I'm filtering a query through two controls on a form but I've reached
the point of breakdown trying to resolve it so I figured I'd better
post here before I lose what's left of my mind.

The form is designed so that the user can enter two values into two
unbound controls. The query has a column with this:
Between [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]!
[MinTUS] And [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].
[Form]![MaxTUS]

I want this to be more flexible.

What if the user has no [MinTUS] value?
What if they have no [MaxTUS] value?
What if they have neither values and want to see all records?

I've been tinkering with this and just can't get it worked out to
answer all of the questions/conditions. I've tried the Like operator
several different ways but to no good end.

Any help would be greatly appreciated!

Thanks!!!

 

[vanderghast]

BETWEEN
Nz( [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS]
, -9999)
AND
Nz([Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS],
9999 )



assuming the values, in the table are necessary between -9999 and 9999.
Sure, in an application where you have no idead of these limits, you can
replace those ugly constants with a DMin and a DMax, but the basic idea is
the same; the formulation is more complex (and would obscure the basic
idea).

Indeed, if MinTUS is left empty, it is NULL and Nz will change that NULL to
the constant, so, the expression serves ***as if*** a 'by default' value
was supplied to the end user.



Vanderghast, Access MVP

 

[johnlute]

I never thought of using Nz like that! I actually should consider
changing a couple other queries I have to this more simple approach.

Thanks for the great idea!

BETWEEN
Nz( [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS]
, -9999)
AND
Nz([Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS],
9999 )

assuming the values, in the table are necessary between -9999 and 9999.
Sure, in an application where you have no idead of these limits, you can
replace those ugly constants with a DMin and a DMax, but the basic idea is
the same; the formulation is more complex (and would obscure the basic
idea).

Indeed, if MinTUS is left empty, it is NULL and Nz will change that NULL to
the constant, so, the expression serves ***as if*** a 'by default'  value
was supplied to the end user.

Vanderghast, Access MVP

I'm filtering a query through two controls on a form but I've reached
the point of breakdown trying to resolve it so I figured I'd better
post here before I lose what's left of my mind.

The form is designed so that the user can enter two values into two
unbound controls. The query has a column with this:
Between [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]!
[MinTUS] And [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].
[Form]![MaxTUS]

I want this to be more flexible.

What if the user has no [MinTUS] value?
What if they have no [MaxTUS] value?
What if they have neither values and want to see all records?

I've been tinkering with this and just can't get it worked out to
answer all of the questions/conditions. I've tried the Like operator
several different ways but to no good end.

Any help would be greatly appreciated!

Thanks!!!- Hide quoted text -

- Show quoted text -

 

[John Spencer]

In SQL view you would have a where clause that looks like:

WHERE (SomeField >=
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS] or
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS] Is Null)

And (SomeField <=
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS]
OR [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS] Is Null)

In query grid view you would end up with something more complex than that.
== You would have to add the reference to each control to a field "box"
== And then you would need to use four rows of criteria
MinTus MaxTus SomeField
Null Null <blank>
Null <blank> Mintus
<blank> Null Maxtus
<blank> <blank> Mintus and MaxTus




John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[johnlute]

Thanks, John. That's a nifty approach, too. I was close to that at one
point but couldn't quie get it. I prefer the Nz method above. I just
had to throw in a couple mor "9's" and it does the trick.

In SQL view you would have a where clause that looks like:

WHERE (SomeField >=
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS] or
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS] Is Null)

And (SomeField <=
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS]
OR [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS] Is Null)

In query grid view you would end up with something more complex than that..
== You would have to add the reference to each control to a field "box"
== And then you would need to use four rows of criteria
MinTus  MaxTus   SomeField
Null    Null      <blank>
Null    <blank>   Mintus
<blank>  Null     Maxtus
<blank>  <blank>  Mintus and MaxTus

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I'm filtering a query through two controls on a form but I've reached
the point of breakdown trying to resolve it so I figured I'd better
post here before I lose what's left of my mind.

The form is designed so that the user can enter two values into two
unbound controls. The query has a column with this:
Between [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]!
[MinTUS] And [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].
[Form]![MaxTUS]

I want this to be more flexible.

What if the user has no [MinTUS] value?
What if they have no [MaxTUS] value?
What if they have neither values and want to see all records?

I've been tinkering with this and just can't get it worked out to
answer all of the questions/conditions. I've tried the Like operator
several different ways but to no good end.

Any help would be greatly appreciated!

Thanks!!!- Hide quoted text -

- Show quoted text -

 

[John Spencer]

That works as long as your field does not contain nulls. If it does you will
filter out the records that have null in the field.

The approach I posted will return records with null in the field if the
criteria are null.

I prefer the NZ and like approaches in many cases - depends on whether or not
the field is required or alternatively if I don't care about getting records
with null values.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John. That's a nifty approach, too. I was close to that at one
point but couldn't quie get it. I prefer the Nz method above. I just
had to throw in a couple mor "9's" and it does the trick.

In SQL view you would have a where clause that looks like:

WHERE (SomeField >=
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS] or
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS] Is Null)

And (SomeField <=
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS]
OR [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS] Is Null)

In query grid view you would end up with something more complex than that.
== You would have to add the reference to each control to a field "box"
== And then you would need to use four rows of criteria
MinTus MaxTus SomeField
Null Null <blank>
Null <blank> Mintus
<blank> Null Maxtus
<blank> <blank> Mintus and MaxTus

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I'm filtering a query through two controls on a form but I've reached
the point of breakdown trying to resolve it so I figured I'd better
post here before I lose what's left of my mind.
The form is designed so that the user can enter two values into two
unbound controls. The query has a column with this:
Between [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]!
[MinTUS] And [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].
[Form]![MaxTUS]
I want this to be more flexible.
What if the user has no [MinTUS] value?
What if they have no [MaxTUS] value?
What if they have neither values and want to see all records?
I've been tinkering with this and just can't get it worked out to
answer all of the questions/conditions. I've tried the Like operator
several different ways but to no good end.
Any help would be greatly appreciated!
Thanks!!!- Hide quoted text -

- Show quoted text -

 

[johnlute]

Thanks for the clarification, John. In this instance we don't want to
see Nulls so the Nz is the "leaner" approach.

That works as long as your field does not contain nulls.  If it does you will
filter out the records that have null in the field.

The approach I posted will return records with null in the field if the
criteria are null.

I prefer the NZ and like approaches in many cases - depends on whether ornot
the field is required or alternatively if I don't care about getting records
with null values.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks, John. That's a nifty approach, too. I was close to that at one
point but couldn't quie get it. I prefer the Nz method above. I just
had to throw in a couple mor "9's" and it does the trick.

In SQL view you would have a where clause that looks like:
WHERE (SomeField >=
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS] or
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MinTUS] Is Null)
And (SomeField <=
[Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS]
OR [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]![MaxTUS] Is Null)
In query grid view you would end up with something more complex than that.
== You would have to add the reference to each control to a field "box"
== And then you would need to use four rows of criteria
MinTus  MaxTus   SomeField
Null    Null      <blank>
Null    <blank>   Mintus
<blank>  Null     Maxtus
<blank>  <blank>  Mintus and MaxTus
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
johnlute wrote:
I'm filtering a query through two controls on a form but I've reached
the point of breakdown trying to resolve it so I figured I'd better
post here before I lose what's left of my mind.
The form is designed so that the user can enter two values into two
unbound controls. The query has a column with this:
Between [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].[Form]!
[MinTUS] And [Forms]![frmFinishedGoods]![sfrmFGPhysicalAttributes].
[Form]![MaxTUS]
I want this to be more flexible.
What if the user has no [MinTUS] value?
What if they have no [MaxTUS] value?
What if they have neither values and want to see all records?
I've been tinkering with this and just can't get it worked out to
answer all of the questions/conditions. I've tried the Like operator
several different ways but to no good end.
Any help would be greatly appreciated!
Thanks!!!- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -