Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
18  13364 
WU10 wrote:
Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
[Forms]![Form_1]![Combo_1]
....and then under that on the next criteria row...
[Forms]![Form_1]![Combo_1] = "All"
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
On Jun 2, 1:01 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
WU10 wrote:
Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
[Forms]![Form_1]![Combo_1]
...and then under that on the next criteria row...
[Forms]![Form_1]![Combo_1] = "All"
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -
- Show quoted text -
Rick,
Should I forget the IIF statement and use the next criteria as you
suggest?
WU10 wrote:
On Jun 2, 1:01 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
WU10 wrote:
Built a form based on a select query with a combo box (3 choices)
in the form header which will supply a value to the query. In the
query's criteria I added the following expression -
[Forms]![Form_1]! [Combo_1] and this works like a charm. However
one of my users wanted to see all of the values before choosing
one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried
"Like "*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
[Forms]![Form_1]![Combo_1]
...and then under that on the next criteria row...
[Forms]![Form_1]![Combo_1] = "All"
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -
- Show quoted text -
Rick,
Should I forget the IIF statement and use the next criteria as you
suggest?
Yes.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
On Jun 2, 1:20 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
WU10 wrote:
On Jun 2, 1:01 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
WU10 wrote:
Built a form based on a select query with a combo box (3 choices)
in the form header which will supply a value to the query. In the
query's criteria I added the following expression -
[Forms]![Form_1]! [Combo_1] and this works like a charm. However
one of my users wanted to see all of the values before choosing
one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried
"Like "*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
[Forms]![Form_1]![Combo_1]
...and then under that on the next criteria row...
[Forms]![Form_1]![Combo_1] = "All"
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -
- Show quoted text -
Rick,
Should I forget the IIF statement and use the next criteria as you
suggest?
Yes.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -
- Show quoted text -
This does not seem to work. But thanks for the reply.
WU10 wrote:
This does not seem to work. But thanks for the reply.
Post the SQL of your query because it has to work if you do it properly.
Think about what it does. It says "Return records where a field value matches
the value of a control on a form, OR where the control on the form is equal to
'All'".
So...when the control's value is "All" the second part of the criteria will be
true for 100% of your records and you will therefore get all of them returned.
If you already have the part working when the control is set to something
besides "All" then there really is no reason for the second part to not work.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1] Is Null)
If you do not make a selection in the combobox, you will see all the
records.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications re******@pcdatasheet.com
"WU10" <cl**********@hotmail.comwrote in message
news:11**********************@q69g2000hsb.googlegr oups.com...
Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
"Steve" <so***@private.emailaddresswrote in
news:Rr*******************@newsread2.news.pas.eart hlink.net:
Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1] Is
Null)
If you do not make a selection in the combobox, you will see
all the records.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And
Word Applications
re******@pcdatasheet.com
Wrong answer again Steve. The O.P. doesn't want to handle null, he
wants to handle "ALL" as in
[Forms]![Form_1]![Combo_1] Or [Forms]![Form_1]![Combo_1]= "ALL"
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Your MRP apps must really be something! They probably have a lot of
inaccurate records where you store calculated values in tables and other
messed up things because you do not know how Access works as you demonstarte
here.
Read my lips ---
<< If you do not make a selection in the combobox, you will see all the
records. >>
When no selection is made in the combobox, the combobox will have a value of
Null and the sewcond part of this criteria will return all records.
Try it you arrogant $$Arse; it works!! Quit criticizing things you don't
understand!!!!
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"Steve" <so***@private.emailaddresswrote in
news:Rr*******************@newsread2.news.pas.eart hlink.net:
>Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1] Is
Null)
If you do not make a selection in the combobox, you will see
all the records.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel And
Word Applications
re******@pcdatasheet.com
Wrong answer again Steve. The O.P. doesn't want to handle null, he
wants to handle "ALL" as in
[Forms]![Form_1]![Combo_1] Or [Forms]![Form_1]![Combo_1]= "ALL"
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
On Sat, 02 Jun 2007 04:32:55 -0700, WU10 <cl**********@hotmail.comwrote:
>Built a form based on a select query with a combo box (3 choices) in
the form header which will supply a value to the query. In the
query's criteria I added the following expression - [Forms]![Form_1]!
[Combo_1] and this works like a charm. However one of my users wanted
to see all of the values before choosing one of the three
selections.
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
iff([Forms]![Form_1]![Combo_1]="All",Like "*",[Forms]![Form_1]!
[Combo_1])
My original 3 choices still work but not my "All". I have tried "Like
"*"", "Is Not Null" and "*" but nothing seems to work.
Any suggestions?
In the properties for the combo box, set List Rows to 3.
When the drop down arrow in the combo box is clicked, the window will expand
downward to show the three values.
Just a wizard prodder
Chuck
--
"Steve" <so***@private.emailaddresswrote in
news:81******************@newsread1.news.pas.earth link.net:
Your MRP apps must really be something! They probably have a
lot of inaccurate records where you store calculated values in
tables and other messed up things because you do not know how
Access works as you demonstarte here.
I know when and how to normalize a database, and when to store a
denormalized value such as putting the UnitPrice in a
Transactions table, instead of relating it from the Items table,
>
Read my lips ---
<< If you do not make a selection in the combobox, you will
see all the records. >>
When no selection is made in the combobox, the combobox will
have a value of Null and the sewcond part of this criteria
will return all records.
With your code, maybe it works. But it's not what was wanted, an
"ALL" row in the combobox is part of the OP's specification. And
it will not work if there is a dsefault value set for the
combobox.
Try it you arrogant $$Arse; it works!! Quit criticizing things
you don't understand!!!!
I'm not arrogant. I'm pedantic.
I will thank you for having dropped the advertizing.
>
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
>"Steve" <so***@private.emailaddresswrote in
news:Rr*******************@newsread2.news.pas.ear thlink.net:
>>Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1]
Is Null)
If you do not make a selection in the combobox, you will see
all the records.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel
And Word Applications
re******@pcdatasheet.com
Wrong answer again Steve. The O.P. doesn't want to handle
null, he wants to handle "ALL" as in
[Forms]![Form_1]![Combo_1] Or [Forms]![Form_1]![Combo_1]=
"ALL"
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"Steve" <so***@private.emailaddresswrote in
news:81******************@newsread1.news.pas.earth link.net:
>Your MRP apps must really be something! They probably have a
lot of inaccurate records where you store calculated values in
tables and other messed up things because you do not know how
Access works as you demonstarte here.
>I know when and how to normalize a database, and when to store a
denormalized value >>
You clearly demonstrated previously that you do not!! An OP had Length,
Width and Height fields in a table and you advised that it is okay to also
have a description field that has the value of concatenating the Length,
Width and Height fields. Most newbies know that that is wrong!!
such as putting the UnitPrice in a
Transactions table, instead of relating it from the Items table,
>>
Read my lips ---
<< If you do not make a selection in the combobox, you will
see all the records. >>
When no selection is made in the combobox, the combobox will
have a value of Null and the sewcond part of this criteria
will return all records.
With your code, maybe it works. But it's not what was wanted, an
"ALL" row in the combobox is part of the OP's specification. And
it will not work if there is a dsefault value set for the
combobox.
Per the OP --
<<one of my users wanted to see all of the values before choosing one of the
three selections.>>
There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from the
same place you learned to put calculated values in a table?
>
>Try it you arrogant $$Arse; it works!! Quit criticizing things
you don't understand!!!!
I'm not arrogant. I'm pedantic.
You hit the nail on the head!! You are certainly characterized by or given
to pretentious or conspicuous show in an attempt to impress others with your
ignorance.
I will thank you for having dropped the advertizing.
>>
"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
>>"Steve" <so***@private.emailaddresswrote in
news:Rr*******************@newsread2.news.pas.ea rthlink.net:
Put the following in the vriteria:
[Forms]![Form_1]![Combo_1] Or ( [Forms]![Form_1]![Combo_1]
Is Null)
If you do not make a selection in the combobox, you will see
all the records.
PC Datasheet
Providing Customers A Resource For Help With Access, Excel
And Word Applications
re******@pcdatasheet.com
Wrong answer again Steve. The O.P. doesn't want to handle
null, he wants to handle "ALL" as in
[Forms]![Form_1]![Combo_1] Or [Forms]![Form_1]![Combo_1]=
"ALL"
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
>With your code, maybe it works. But it's not what was wanted, an
"ALL" row in the combobox is part of the OP's specification. And
it will not work if there is a dsefault value set for the
combobox.
Per the OP --
<<one of my users wanted to see all of the values before choosing one of
the three selections.>>
There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from
the same place you learned to put calculated values in a table?
You are mistaken. I suggest you go back and read the original post. The
"All" choice is precisely what the OP requested.
"Arch" <no*****@thank.youwrote in message
news:uO*******************@newssvr13.news.prodigy. net...
>
>>With your code, maybe it works. But it's not what was wanted, an
"ALL" row in the combobox is part of the OP's specification. And
it will not work if there is a dsefault value set for the
combobox.
Per the OP --
<<one of my users wanted to see all of the values before choosing one of
the three selections.>>
There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from
the same place you learned to put calculated values in a table?
You are mistaken. I suggest you go back and read the original post. The
"All" choice is precisely what the OP requested.
There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from the
same place as Mr. Quintal?
"Bob Quintal" <rq******@sPAmpatico.caschreef in bericht news:Xn**********************@66.150.105.47...
I'm not arrogant. I'm pedantic.
I will thank you for having dropped the advertizing.
Hi Bob,
Why do you think he dropped the advertising ??
You mean: there was no sigline ??
Well, sometimes when he is angry he forgets to 'sign'...
He is playing the 'nicest' of all posters at the moment, especially in the ms-groups.
I am sure he will *really* advertise within a week or so again.
Correction: Just saw him doing *exactly that* at microsoft.public.access.tablesdbdesign ...
Hmmm, here we go again: http://home.tiscali.nl/arracom/whoissteve.html
Arno R
Quotes from the original posting:
I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
My original 3 choices still work but not my "All". I have tried "Like
Reply from PC Datasheet, "The Application Resource":
There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from
the same place as Mr. Quintal?
"I" and "My" are not the user!!!
I repeat ----
Did you learn to read from the same place as Mr. Quintal?
Maybe you are pedantic too!!!
You are certainly characterized by or given to pretentious or conspicuous
show in an attempt to impress others with your ignorance.
"Arch" <no*****@thank.youwrote in message
news:tW*****************@newssvr27.news.prodigy.ne t...
Quotes from the original posting:
>I added the choice "All" to my combo dropdown list and tried the
following iif statement as the criteria:
>My original 3 choices still work but not my "All". I have tried "Like
Reply from PC Datasheet, "The Application Resource":
>There is nothing saying the user wanted to see "All" as a choice. Rather,
the user wanted a way to see all the values. Did you learn to read from
the same place as Mr. Quintal?
"Steve" <so***@private.emailaddresswrote
"I" and "My" are not the user!!!
They are used by the original poster in this thread, so it seems they refer
to that original poster. If the original poster had meant someone else, "I"
and "My" would have been inappropriate pronouns.
I'd be very appreciative if you would restrain your language. It is simple
to disagree without vulgarity, and, as well, your disagreement here is
silly.
Larry Linson
"Arch" <no*****@thank.youwrote in
news:tW*****************@newssvr27.news.prodigy.ne t:
Quotes from the original posting:
>I added the choice "All" to my combo dropdown list and tried
the following iif statement as the criteria:
>My original 3 choices still work but not my "All". I have
tried "Like
Reply from PC Datasheet, "The Application Resource":
>There is nothing saying the user wanted to see "All" as a
choice. Rather, the user wanted a way to see all the values.
Did you learn to read from the same place as Mr. Quintal?
I'd plonk PCD except somebody needs to correct the misinformation
he supplies to people with problems.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Reggie |
last post by:
Hi and TIA, I Have a query that uses a function for setting the Criteria.
When I view the return value of the finction it appears to be exactly what I
want for my criteria. The problem is if I...
|
by: Reggie |
last post by:
Hi and TIA, I have the criteria for one of my fields set to the return
value of this function. When I view the result in the immediate window it
appears to be the exact value I would use if I...
|
by: Steve |
last post by:
I need help with a function to be used as the criteria for a query. The field in
the query is InventoryStatus: - . The function is built
around a Select Case set of about twelve cases.
Function...
|
by: Andy |
last post by:
Hello,
I am having many problems with setting up a parameter query that
searches by the criteria entered or returns all records if nothing is
entered.
I have designed an unbound form with 3...
|
by: Andy |
last post by:
Hello,
I have a question regarding how to format a date in VB so that I can
call it from a query and get results. I'm calling functions in the
query because that was the only way I found I could...
|
by: MLH |
last post by:
Am repeating question with different subject heading, perhaps
stating more clearly my problem...
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the...
|
by: martlaco1 |
last post by:
Trying to fix a query that (I thought) had worked once upon a time, and
I keep getting a Data Type Mismatch error whenever I enter any criteria
for an expression using a Mid function. Without the...
|
by: Phil Latio |
last post by:
Probably not the best way to do this, but I'd appreciate it if someone
could tell me why the following DOESN'T work;
I have a function (code below) which returns a string based upon a
supplied...
|
by: ChasW |
last post by:
I just read these 2 pages. These are most helpful, but leave me with
a question.
http://www.mvps.org/access/queries/qry0005.htm
http://www.mvps.org/access/forms/frm0007.htm
I have a Multi...
|
by: Jaime Leivers |
last post by:
Here's a median function in access that you can call in any query. You
could change this to any excel function you wanted. Most people can
find the windows help file that says how to call an excel...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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: 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,...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
