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!
12  2886 
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" <No****@Spam.Com> wrote in message
news:v8**************@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!
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:e3*************@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" <No****@Spam.Com> wrote in message
news:v8**************@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!
"PC Datasheet" <No****@Spam.Com> wrote in message
news:7i****************@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])
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" <No****@Spam.Com> wrote in message
news:7i****************@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:e3*************@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" <No****@Spam.Com> wrote in message
news:v8**************@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!
On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <No****@Spam.Com>
wrote: 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]
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:uX**************@TK2MSFTNGP05.phx.gbl... 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" <No****@Spam.Com> wrote in message
news:7i****************@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:e3*************@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" <No****@Spam.Com> wrote in message
> news:v8**************@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!
>>
>>
>
>
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" <he**@there.com> wrote in message
news:44**********@glkas0286.greenlnk.net... "PC Datasheet" <No****@Spam.Com> wrote in message
news:7i****************@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])
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
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:f8********************************@4ax.com... On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <No****@Spam.Com>
wrote:
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]
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" <No****@Spam.Com> wrote in message
news:nE****************@newsread3.news.pas.earthli nk.net... 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:uX**************@TK2MSFTNGP05.phx.gbl... 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" <No****@Spam.Com> wrote in message
news:7i****************@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:e3*************@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" <No****@Spam.Com> wrote in message
> news:v8**************@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!
>>
>>
>
>
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:f8********************************@4ax.com... On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <No****@Spam.Com>
wrote:
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]
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:OI**************@TK2MSFTNGP02.phx.gbl... 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" <No****@Spam.Com> wrote in message
news:nE****************@newsread3.news.pas.earthli nk.net... 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:uX**************@TK2MSFTNGP05.phx.gbl... > 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" <No****@Spam.Com> wrote in message
> news:7i****************@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:e3*************@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" <No****@Spam.Com> wrote in message
>> > news:v8**************@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!
>> >>
>> >>
>> >
>> >
>>
>>
>
>
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" <No****@Spam.Com> wrote in message
news:hw****************@newsread1.news.pas.earthli nk.net... 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:f8********************************@4ax.com... On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <No****@Spam.Com>
wrote:
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]
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: kingofkolt |
last post by:
I have a directory of images, called "random". In it are the following
files:
1.gif
2.gif
3.gif
4.gif
I use this script to choose a random image and display it:
|
by: Penny |
last post by:
Hi all,
I've built a simple search <Form> on a web page that is intended to allow
the user to search a record store database. There is a drop down box where
the user can choose either 'Artist'...
|
by: Nicole |
last post by:
Hi
I need to be able to change the currency and some form fields displayed on
my website depending on where the visitor is based. I don't need a fully
functional cart, just a kinda either...
|
by: Anders Borum |
last post by:
Hello!
I've come across a strange error that occurs, when you try to return a
nodelist from a variable with a choose/where/otherwise statement.
I'm not quite sure whether it's a bug or simply...
|
by: ADezii |
last post by:
The tendency of VBA code to evaluate all expressions, whether or not they need to be evaluated from a logical standpoint, makes the use of the IIf(), Switch(), and Choose() Functions inefficient and...
|
by: Coastie |
last post by:
Good afternoon-
There is a table with the following fields:
PK*, Equipment_ID, Battery_Serial
*= arbitrary primary key
Each piece of equipment has between 0 and 3 batteries, and each battery...
|
by: alhomam |
last post by:
hi all,
i have a table that has the two fileds:
Return Date, Extension Time for Return Date
i need a query to choose the max date between these two fields
eg: if return date = 01...
|
by: ARC |
last post by:
Hello all,
In my access 2007 app, I'm using the code for fonts/colors that I believe I
found years ago on the accessweb.net site. Problem is, calling the color
picker is opening the very old...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
