Choose Function

[Steve]

Can the Choose function be used to set the criteria for a field in a query
to either "Is Null" or "Is Not Null" based on the value of an option Group
on a form? Such as:
Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
Where MyOptionGroup can have the value of 1 or 2.

Thanks!

 

[Guest]

You can use query by form to set the criteria of an access query by using the
something like

Forms![NameOfForm]![NameOfField] Or Forms![NameOfForm]![NameOfField] Is Null

For more info see this link
http://support.microsoft.com/kb/304428/en-us
Or to set the criteria in sql see
http://support.microsoft.com/kb/286828/en-us

 

[Steve]

Wayne,

Thanks for responding!

I am not looking to query for a specific value. Suppose the field in the
query is InspectionDate. When the option group on the QBF form has a value
of 1, I want to return all records where InspectionDate is null. When the
option group on the QBF form has a value of 2, I want to return all records
where InspectionDate is not null. And actually in my problem, when the
option group on the QBF form has a value of 3, I want to return all records.

Steve

You can use query by form to set the criteria of an access query by using
the
something like

Forms![NameOfForm]![NameOfField] Or Forms![NameOfForm]![NameOfField] Is
Null

For more info see this link
http://support.microsoft.com/kb/304428/en-us
Or to set the criteria in sql see
http://support.microsoft.com/kb/286828/en-us



--
Wayne

If you feel this answer has been useful please check the "was this post
helpful" so I get some feedback. Thanks.

Can the Choose function be used to set the criteria for a field in a
query
to either "Is Null" or "Is Not Null" based on the value of an option
Group
on a form? Such as:
Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
Where MyOptionGroup can have the value of 1 or 2.

Thanks!

 

[Douglas J Steele]

No it can't. You'll have to set your query up another way.

 

[PC Datasheet]

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve

 

[Keith Wilby]

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

There is no "Is Not Null part in the below", only "Is Null". What exactly
are you trying to achieve? Are you trying to return Null when MyOptionGroup
= 1 and whatever MyField is when MyOptionGroup = 2?

Keith.
www.keithwilby.com

 

[Douglas J Steele]

Explain what you're trying to do in words, rather with a partial code
snippet...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve

No it can't. You'll have to set your query up another way.

 

[John Vinson]

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve

Steve, any function - IIF, Switch, Choose, etc. - can return a
*VALUE*. However it cannot return an *operator* such as IS NULL.

AFAIK the only way to get this to work would be to dispense with the
function altogether with a syntax like

WHERE (fieldname IS NULL AND Forms!MyForm!MyOptionGroup = 1)
OR (fieldname = [MyField] AND Forms!MyForm!MyOptionGroup = 2)


John W. Vinson[MVP]

 

[PC Datasheet]

I am not looking to query for a specific value. Suppose the field in the
query is InspectionDate. When the option group on the QBF form, MyForm, has
a value
of 1, I want to return all records where InspectionDate is null. When the
option group on the QBF form, MyForm, has a value of 2, I want to return all
records
where InspectionDate is not null. And actually in my problem, when the
option group on the QBF form, MyForm, has a value of 3, I want to return all
records.

Steve

Explain what you're trying to do in words, rather with a partial code
snippet...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve

No it can't. You'll have to set your query up another way.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Can the Choose function be used to set the criteria for a field in a
query
to either "Is Null" or "Is Not Null" based on the value of an option
Group
on a form? Such as:
Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
Where MyOptionGroup can have the value of 1 or 2.

Thanks!

 

[PC Datasheet]

The "Is Not Null part in the below" is [MyField], the third parameter in the
Choose function. If the Option Group value is 2, [MyField] in the third
parameter will cause the query to return all records where [MyField] is not
null.

For a full explanation of my problem, see my reply back to Doug Steele.

Steve

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

There is no "Is Not Null part in the below", only "Is Null". What exactly
are you trying to achieve? Are you trying to return Null when
MyOptionGroup = 1 and whatever MyField is when MyOptionGroup = 2?

Keith.
www.keithwilby.com

 

[PC Datasheet]

John,

Thank you for responding!

I am not looking to query for a specific value. Suppose the field in the
query is InspectionDate. When the option group on the QBF form, MyForm, has
a value of 1, I want to return all records where InspectionDate is null.
When the option group on the QBF form, MyForm, has a value of 2, I want to
return all records where InspectionDate is not null. And actually in my
problem, when the option group on the QBF form, MyForm, has a value of 3, I
want to return all records.

Using the following expression in the criteria of the InspectionDate field
in the query,
Choose(Forms!MyForm!MyOptionGroup,Is Null,[InspectionDate])
when the Option Group value is 2, [InspectionDate] in the third parameter
will cause the query to return all records where [InspectionDate] is not
null. That's half the solution. I am looking for something to put in the
second parameter of the Choose function so when the Option Group value is 1,
it will cause the query to return all records where [InspectionDate] is
null.

Steve

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve

Steve, any function - IIF, Switch, Choose, etc. - can return a
*VALUE*. However it cannot return an *operator* such as IS NULL.

AFAIK the only way to get this to work would be to dispense with the
function altogether with a syntax like

WHERE (fieldname IS NULL AND Forms!MyForm!MyOptionGroup = 1)
OR (fieldname = [MyField] AND Forms!MyForm!MyOptionGroup = 2)


John W. Vinson[MVP]

 

[Douglas J Steele]

WHERE (InspectionDate IS NULL AND Forms!MyForm!MyOptionGroup = 1)
OR (InspectionDate IS NOT NULL AND Forms!MyForm!MyOptionGroup = 2)
OR (Forms!MyForm!MyOptionGroup = 3)

Obviously if you've got other conditions, you'd have to wrap all of that in
parentheses, and then have the rest of the conditions:

WHERE ((InspectionDate IS NULL AND Forms!MyForm!MyOptionGroup = 1)
OR (InspectionDate IS NOT NULL AND Forms!MyForm!MyOptionGroup = 2)
OR (Forms!MyForm!MyOptionGroup = 3))
AND Field1 = "X"
AND Field2 = 1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am not looking to query for a specific value. Suppose the field in the
query is InspectionDate. When the option group on the QBF form, MyForm, has
a value
of 1, I want to return all records where InspectionDate is null. When the
option group on the QBF form, MyForm, has a value of 2, I want to return all
records
where InspectionDate is not null. And actually in my problem, when the
option group on the QBF form, MyForm, has a value of 3, I want to return all
records.

Steve

Explain what you're trying to do in words, rather with a partial code
snippet...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve


No it can't. You'll have to set your query up another way.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Can the Choose function be used to set the criteria for a field in a
query
to either "Is Null" or "Is Not Null" based on the value of an option
Group
on a form? Such as:
Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
Where MyOptionGroup can have the value of 1 or 2.

Thanks!

 

[PC Datasheet]

John,

Got it to work; thought you might be interested ---

1. Added following field to query:
InspDate: NZ([InspectionDate],"InspectionDateIsNull")

2. Added following criteria to InspDate:
Choose([Forms]![PFrmFindOrderToCallToScheduleInspection]![InspectionDateSet],"InspectionDateIsNull",[InspectionDate],NZ([InspectionDate],"InspectionDateIsNull"))

** [InspectionDateSet] is the name of the option group.

When [InspectionDateSet] is 1, the query returns all records where
[InspectionDate] is null.

When [InspectionDateSet] is 2, the query returns all records where
[InspectionDate] is not null.

When [InspectionDateSet] is 3, the query returns all records.

Steve

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve

Steve, any function - IIF, Switch, Choose, etc. - can return a
*VALUE*. However it cannot return an *operator* such as IS NULL.

AFAIK the only way to get this to work would be to dispense with the
function altogether with a syntax like

WHERE (fieldname IS NULL AND Forms!MyForm!MyOptionGroup = 1)
OR (fieldname = [MyField] AND Forms!MyForm!MyOptionGroup = 2)


John W. Vinson[MVP]

 

[PC Datasheet]

Doug,

Got it to work; thought you might be interested. See my reply back to John
Vinson for the solution.

Steve

WHERE (InspectionDate IS NULL AND Forms!MyForm!MyOptionGroup = 1)
OR (InspectionDate IS NOT NULL AND Forms!MyForm!MyOptionGroup = 2)
OR (Forms!MyForm!MyOptionGroup = 3)

Obviously if you've got other conditions, you'd have to wrap all of that
in
parentheses, and then have the rest of the conditions:

WHERE ((InspectionDate IS NULL AND Forms!MyForm!MyOptionGroup = 1)
OR (InspectionDate IS NOT NULL AND Forms!MyForm!MyOptionGroup = 2)
OR (Forms!MyForm!MyOptionGroup = 3))
AND Field1 = "X"
AND Field2 = 1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am not looking to query for a specific value. Suppose the field in the
query is InspectionDate. When the option group on the QBF form, MyForm, has
a value
of 1, I want to return all records where InspectionDate is null. When the
option group on the QBF form, MyForm, has a value of 2, I want to return all
records
where InspectionDate is not null. And actually in my problem, when the
option group on the QBF form, MyForm, has a value of 3, I want to return all
records.

Steve

Explain what you're trying to do in words, rather with a partial code
snippet...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is
Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve


message
No it can't. You'll have to set your query up another way.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Can the Choose function be used to set the criteria for a field in
a
query
to either "Is Null" or "Is Not Null" based on the value of an
option
Group
on a form? Such as:
Choose(Forms!MyForm!MyOptionGroup,Is Null,Is Not Null)
Where MyOptionGroup can have the value of 1 or 2.

Thanks!

 

[Michel Walsh]

Hi,



alternatively:

WHERE CHOOSE(
FORMS!pfrmFindOrderToVallToScheduleInspection!inspectionDateSet,
InspDate Is Null,
Not InspDate Is Null,
true)


the idea being to get the whole expression evaluated inside the choose.


Vanderghast, Access MVP

John,

Got it to work; thought you might be interested ---

1. Added following field to query:
InspDate: NZ([InspectionDate],"InspectionDateIsNull")

2. Added following criteria to InspDate:
Choose([Forms]![PFrmFindOrderToCallToScheduleInspection]![InspectionDateSet],"InspectionDateIsNull",[InspectionDate],NZ([InspectionDate],"InspectionDateIsNull"))

** [InspectionDateSet] is the name of the option group.

When [InspectionDateSet] is 1, the query returns all records where
[InspectionDate] is null.

When [InspectionDateSet] is 2, the query returns all records where
[InspectionDate] is not null.

When [InspectionDateSet] is 3, the query returns all records.

Steve

Thanks, Doug!

I can get the Is Not Null part in the below but can not get the Is Null
part. Do you have any ideas?

Choose(Forms!MyForm!MyOptionGroup,Is Null,[MyField])

Steve

Steve, any function - IIF, Switch, Choose, etc. - can return a
*VALUE*. However it cannot return an *operator* such as IS NULL.

AFAIK the only way to get this to work would be to dispense with the
function altogether with a syntax like

WHERE (fieldname IS NULL AND Forms!MyForm!MyOptionGroup = 1)
OR (fieldname = [MyField] AND Forms!MyForm!MyOptionGroup = 2)


John W. Vinson[MVP]