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!My Form!MyOptionGr oup,Is Null,Is Not Null)
Where MyOptionGroup can have the value of 1 or 2.
Thanks!
12  2906 
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.Co m> wrote in message
news:v8******** ******@newsread 3.news.pas.eart hlink.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!My Form!MyOptionGr oup,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!My Form!MyOptionGr oup,Is Null,[MyField])
Steve
"Douglas J Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:e3******** *****@TK2MSFTNG P02.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.Co m> wrote in message
news:v8******** ******@newsread 3.news.pas.eart hlink.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!My Form!MyOptionGr oup,Is Null,Is Not Null)
Where MyOptionGroup can have the value of 1 or 2.
Thanks!
"PC Datasheet" <No****@Spam.Co m> wrote in message
news:7i******** ********@newsre ad2.news.pas.ea rthlink.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!My Form!MyOptionGr oup,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.Co m> wrote in message
news:7i******** ********@newsre ad2.news.pas.ea rthlink.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!My Form!MyOptionGr oup,Is Null,[MyField])
Steve
"Douglas J Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:e3******** *****@TK2MSFTNG P02.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.Co m> wrote in message
news:v8******** ******@newsread 3.news.pas.eart hlink.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!My Form!MyOptionGr oup,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.Co m>
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!M yForm!MyOptionG roup,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!My OptionGroup = 1)
OR (fieldname = [MyField] AND Forms!MyForm!My OptionGroup = 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_djsteel e@NOSPAM_canada .com> wrote in message
news:uX******** ******@TK2MSFTN GP05.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.Co m> wrote in message
news:7i******** ********@newsre ad2.news.pas.ea rthlink.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!My Form!MyOptionGr oup,Is Null,[MyField])
Steve
"Douglas J Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:e3******** *****@TK2MSFTNG P02.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.Co m> wrote in message
> news:v8******** ******@newsread 3.news.pas.eart hlink.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!My Form!MyOptionGr oup,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.gr eenlnk.net... "PC Datasheet" <No****@Spam.Co m> wrote in message
news:7i******** ********@newsre ad2.news.pas.ea rthlink.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!My Form!MyOptionGr oup,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!My Form!MyOptionGr oup,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_S PAM.WysardOfInf o.com> wrote in message
news:f8******** *************** *********@4ax.c om... On Mon, 15 May 2006 13:24:19 GMT, "PC Datasheet" <No****@Spam.Co m>
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!MyOption Group,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!My OptionGroup = 1)
OR (fieldname = [MyField] AND Forms!MyForm!My OptionGroup = 2)
John W. Vinson[MVP]
WHERE (InspectionDate IS NULL AND Forms!MyForm!My OptionGroup = 1)
OR (InspectionDate IS NOT NULL AND Forms!MyForm!My OptionGroup = 2)
OR (Forms!MyForm!M yOptionGroup = 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 ((InspectionDat e IS NULL AND Forms!MyForm!My OptionGroup = 1)
OR (InspectionDate IS NOT NULL AND Forms!MyForm!My OptionGroup = 2)
OR (Forms!MyForm!M yOptionGroup = 3))
AND Field1 = "X"
AND Field2 = 1
--
Doug Steele, Microsoft Access MVP http://I.Am/DougSteele
(no e-mails, please!)
"PC Datasheet" <No****@Spam.Co m> wrote in message
news:nE******** ********@newsre ad3.news.pas.ea rthlink.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_djsteel e@NOSPAM_canada .com> wrote in message
news:uX******** ******@TK2MSFTN GP05.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.Co m> wrote in message
news:7i******** ********@newsre ad2.news.pas.ea rthlink.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!My Form!MyOptionGr oup,Is Null,[MyField])
Steve
"Douglas J Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:e3******** *****@TK2MSFTNG P02.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.Co m> wrote in message
> news:v8******** ******@newsread 3.news.pas.eart hlink.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!My Form!MyOptionGr oup,Is Null,Is Not Null)
>> Where MyOptionGroup can have the value of 1 or 2.
>>
>> Thanks!
>>
>>
>
>
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' or 'Title', next is a text box where
they enter their 'Keyword' and next is the search button. For example the
user may choose 'Artist', type 'Bob Dylan', click the search button and be
presented with a page showing all the details of the Bob...
|
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 display '$10' or '£7' sort of thing!
I was planning on having a map on my opening screen with hotspots over the
countries. On the links for the hot spots I was planning to have something
like href="functions.php?FuncToExec=countrySelectUS"> in the...
|
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 lack of W3C support. All I
know is, that this Xsl document works with the current implementation of
MSXML V4.0 SP2 if transformed using e.g. VB6. I'm told by the compiler at
runtime, that "the expression should evaluate to a nodeset" (the...
|
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 downright dangerous. For these reasons, they should 'never' be used in VBA code. A couple of examples using IIf() will clearly illustrate this point which applies equally well to Choose() and Switch().
Example 1:
varValue = IIf(BooleanExpression,...
| | |
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 has a non-sequential serial number. Unfortunately, this creates multiple records for each piece of equipment. I want a query that consolidates the information onto a single line for each piece of equipment. The fields I need are:
Equipment_ID,...
|
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 May 2008
ext return date = 10 may 2008
|
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 style color chooser (looks to be the access
97 color palette and style). How do you get the 'choose color' function to
open the more modern / access 2007 color picker and dialog style? Here's the
declare function:
Private Declare Function...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
