Choose Function

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!

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Steve" <NoSpam@Spam.Com> wrote in message
news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink .net...[color=blue]
> 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!
>
>[/color]

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

"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...[color=blue]
> No it can't. You'll have to set your query up another way.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Steve" <NoSpam@Spam.Com> wrote in message
> news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink .net...[color=green]
>> 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!
>>
>>[/color]
>
>[/color]

"PC Datasheet" <NoSpam@Spam.Com> wrote in message
news:7i%9g.2721$y4.1391@newsread2.news.pas.earthli nk.net...[color=blue]
> 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])
>[/color]

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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"PC Datasheet" <NoSpam@Spam.Com> wrote in message
news:7i%9g.2721$y4.1391@newsread2.news.pas.earthli nk.net...[color=blue]
> 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
>
>
> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...[color=green]
> > No it can't. You'll have to set your query up another way.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Steve" <NoSpam@Spam.Com> wrote in message
> > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink .net...[color=darkred]
> >> 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!
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <NoSpam@Spam.Com>
wrote:
[color=blue]
>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[/color]

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]

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

"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:uXkdLODeGHA.4040@TK2MSFTNGP05.phx.gbl...[color=blue]
> Explain what you're trying to do in words, rather with a partial code
> snippet...
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "PC Datasheet" <NoSpam@Spam.Com> wrote in message
> news:7i%9g.2721$y4.1391@newsread2.news.pas.earthli nk.net...[color=green]
>> 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
>>
>>
>> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>> news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...[color=darkred]
>> > No it can't. You'll have to set your query up another way.
>> >
>> > --
>> > Doug Steele, Microsoft Access MVP
>> > http://I.Am/DougSteele
>> > (no e-mails, please!)
>> >
>> >
>> > "Steve" <NoSpam@Spam.Com> wrote in message
>> > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink .net...
>> >> 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!
>> >>
>> >>
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

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

"Keith Wilby" <here@there.com> wrote in message
news:44688624$1_1@glkas0286.greenlnk.net...[color=blue]
> "PC Datasheet" <NoSpam@Spam.Com> wrote in message
> news:7i%9g.2721$y4.1391@newsread2.news.pas.earthli nk.net...[color=green]
>> 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])
>>[/color]
>
> 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
>[/color]

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

"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:f89h62t2b7o9vut1b2rrubvn8ai04pqcq9@4ax.com...[color=blue]
> On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <NoSpam@Spam.Com>
> wrote:
>[color=green]
>>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[/color]
>
> 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][/color]

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
http://I.Am/DougSteele
(no e-mails, please!)

"PC Datasheet" <NoSpam@Spam.Com> wrote in message
news:nE2ag.2539$x4.2457@newsread3.news.pas.earthli nk.net...[color=blue]
> 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,[/color]
has[color=blue]
> 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[/color]
all[color=blue]
> 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[/color]
all[color=blue]
> records.
>
> Steve
>
> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:uXkdLODeGHA.4040@TK2MSFTNGP05.phx.gbl...[color=green]
> > Explain what you're trying to do in words, rather with a partial code
> > snippet...
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "PC Datasheet" <NoSpam@Spam.Com> wrote in message
> > news:7i%9g.2721$y4.1391@newsread2.news.pas.earthli nk.net...[color=darkred]
> >> 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
> >>
> >>
> >> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> >> news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...
> >> > No it can't. You'll have to set your query up another way.
> >> >
> >> > --
> >> > Doug Steele, Microsoft Access MVP
> >> > http://I.Am/DougSteele
> >> > (no e-mails, please!)
> >> >
> >> >
> >> > "Steve" <NoSpam@Spam.Com> wrote in message
> >> > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink .net...
> >> >> 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!
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

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

"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:f89h62t2b7o9vut1b2rrubvn8ai04pqcq9@4ax.com...[color=blue]
> On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <NoSpam@Spam.Com>
> wrote:
>[color=green]
>>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[/color]
>
> 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][/color]

Doug,

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

Steve

"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:OIl89rEeGHA.4892@TK2MSFTNGP02.phx.gbl...[color=blue]
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "PC Datasheet" <NoSpam@Spam.Com> wrote in message
> news:nE2ag.2539$x4.2457@newsread3.news.pas.earthli nk.net...[color=green]
>> 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,[/color]
> has[color=green]
>> 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[/color]
> all[color=green]
>> 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[/color]
> all[color=green]
>> records.
>>
>> Steve
>>
>> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>> news:uXkdLODeGHA.4040@TK2MSFTNGP05.phx.gbl...[color=darkred]
>> > Explain what you're trying to do in words, rather with a partial code
>> > snippet...
>> >
>> > --
>> > Doug Steele, Microsoft Access MVP
>> > http://I.Am/DougSteele
>> > (no e-mails, please!)
>> >
>> >
>> > "PC Datasheet" <NoSpam@Spam.Com> wrote in message
>> > news:7i%9g.2721$y4.1391@newsread2.news.pas.earthli nk.net...
>> >> 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
>> >>
>> >>
>> >> "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
>> >> message
>> >> news:e3MFlFBeGHA.564@TK2MSFTNGP02.phx.gbl...
>> >> > No it can't. You'll have to set your query up another way.
>> >> >
>> >> > --
>> >> > Doug Steele, Microsoft Access MVP
>> >> > http://I.Am/DougSteele
>> >> > (no e-mails, please!)
>> >> >
>> >> >
>> >> > "Steve" <NoSpam@Spam.Com> wrote in message
>> >> > news:v8S9g.2362$x4.41@newsread3.news.pas.earthlink .net...
>> >> >> 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!
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

Hi,

alternatively:

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

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

Vanderghast, Access MVP

"PC Datasheet" <NoSpam@Spam.Com> wrote in message
news:hw8ag.5021$u4.4497@newsread1.news.pas.earthli nk.net...[color=blue]
> 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
>
>
> "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
> news:f89h62t2b7o9vut1b2rrubvn8ai04pqcq9@4ax.com...[color=green]
>> On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <NoSpam@Spam.Com>
>> wrote:
>>[color=darkred]
>>>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[/color]
>>
>> 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][/color]
>
>[/color]