help! query based on form - no search results.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to run a query based on values in a form. If the checkbox
[Sponsorship] on the form is Yes, I want the query to return all values other
than "None"

If the checkbox is No, I want it to return values that are "None"

I have tried several variations of the following but all return no values
for Yes. However, it does work when the checkbox is No.

IIf([Forms]![SearchForm]![Sponsorship]=Yes,Not "None","None")

Anyone see where I'm going wrong?
 
kdagostino said:
I am trying to run a query based on values in a form. If the checkbox
[Sponsorship] on the form is Yes, I want the query to return all values other
than "None"

If the checkbox is No, I want it to return values that are "None"

I have tried several variations of the following but all return no values
for Yes. However, it does work when the checkbox is No.

IIf([Forms]![SearchForm]![Sponsorship]=Yes,Not "None","None")

Anyone see where I'm going wrong?
Click to expand...

Something like this might do it. Not sure I've got the = and the <> the
right way around, but hopefully you can see the general idea:

SELECT * FROM some_table WHERE (some_field = "None") <>
[Forms]![SearchForm]![Sponsorship]
 
Hi.
I am trying to run a query based on values in a form. If the checkbox
[Sponsorship] on the form is Yes, I want the query to return all values other
than "None"

If the checkbox is No, I want it to return values that are "None"
Click to expand...

I'm not entirely sure I understand what you're asking for, but I'll give it
a whirl. If the IIF( ) statement is in the WHERE clause of your query, then
you'll need to compare the "None" or Not "None" to an actual field name and
take into account when the field may be NULL. For example:

SELECT *
FROM tblMyTable
WHERE (IIF(([Forms]![SearchForm]![Sponsorship]=Yes, ((SomeValue <> "None")
OR ISNULL(SomeValue)), (SomeValue = "None"))) ;

.... where SomeValue is the name of the field that may or may not contain the
string, "None," and tblMyTable is the name of the table. This example query
returns all fields, not just certain ones, of all records that meet the
criteria according to the checkbox on the form.

If you've designed your query differently, and/or the above example doesn't
shed any light, then please post your SQL so we can help you get what you
want.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


kdagostino said:
I am trying to run a query based on values in a form. If the checkbox
[Sponsorship] on the form is Yes, I want the query to return all values other
than "None"

If the checkbox is No, I want it to return values that are "None"

I have tried several variations of the following but all return no values
for Yes. However, it does work when the checkbox is No.

IIf([Forms]![SearchForm]![Sponsorship]=Yes,Not "None","None")

Anyone see where I'm going wrong?
Click to expand...
 
Hi. Thanks for all your help. This query I'm running is extremely
complicated because my search form has many different fields to search by
(such as Conference, Month, Year, PracticeArea, Sponsorship, Advertisement,
Whether or not we exhibited - spoke - attended, FirstName, LastName, among
others).

Each field has a textbox, combo box, or checkbox on the SearchForm for the
value to search by. The intent is to allow the user to decide what to search
by (e.g. Month, and PracticeArea to return all Conferences in the specified
month that a specific practice area went to) press a button, and print a
report.

The following code works, but is only allows me to search Sponsorship by the
level of sponsorship we did (i.e. Gold Level, Silver Level, Platinum Level,
None, etc.) The SearchForm has a combo box of all the sponsorship levels.
What I want is a checkbox on the SearchForm. If that checkbox is checked,
then I want it to return all the Conferences we sponsored regardlesss of the
level. If it is not checked, I want to find all the Conferences in which we
did not sponsor.

The is no Null value, because the default value for the field [Sponsorship]
is "None" and it is a required field.

If I can get this to work, I intend to do the same thing With
AdvertisementWhere.

I am much better in the design view since I am a novice at Access. I have
tried your SQL in a simple query and it works, but I don't know how to add it
to my complicated query.

Here is the SQL for my complicated query.

SELECT MainTable.ConferenceName, MainTable.MonthOfConference,
MainTable.YearOfConference, MainTable.PracticeArea, MainTable.Sponsorship,
MainTable.AdvertisementWhere, MainTable.Attend, MainTable.Speaking,
MainTable.Exhibit, MainTable.Attendee1First, MainTable.Attendee1Last,
MainTable.Attendee2First, MainTable.Attendee2Last, MainTable.Attendee3First,
MainTable.Attendee3Last, MainTable.Attendee4First, MainTable.Attendee4Last,
MainTable.Attendee5First, MainTable.Attendee5Last, MainTable.Attendee6First,
MainTable.Attendee6Last,
[MainTable]![RegistrationFee]+[MainTable]![SponsorshipCost]+[MainTable]![AdvertisementCost]+[MainTable]![BoothCost]+[MainTable]![CostShipping]+[MainTable]![CostWarehousing]+[MainTable]![CostSupplies]+[MainTable]![CostHotel]+[MainTable]![CostFlight]+[MainTable]![CostRentalCar]+[MainTable]![CostMeals]+[MainTable]![PreConfMailOut1PostageFee]+[MainTable]![PreConfMailOut1PrintingFee]+[MainTable]![PreConfMailOut2PostageFee]+[MainTable]![PreConfMailOut2PrintingFee]+[MainTable]![PreConfMailOut3PostageFee]+[MainTable]![PreConfMailOut3PrintingFee]+[MainTable]![PostConfMailOut1PostageFee]+[MainTable]![PostConfMailOut1PrintingFee]+[MainTable]![PostConfMailOut2PostageFee]+[MainTable]![PostConfMailOut2PrintingFee]+[MainTable]![PostConfMailOut3PostageFee]+[MainTable]![PostConfMailOut3PrintingFee] AS TotalCost
FROM MainTable
WHERE (((MainTable.ConferenceName) Like
[Forms]![SearchForm]![SearchFormConference] & "*") AND
((MainTable.MonthOfConference) Like [Forms]![SearchForm]![SearchFormMonth] &
"*") AND ((MainTable.YearOfConference) Like
[Forms]![SearchForm]![SearchFormYear] & "*") AND ((MainTable.PracticeArea)
Like [Forms]![SearchForm]![SearchFormPracticeArea] & "*") AND
((MainTable.Sponsorship) Like [Forms]![SearchForm]![SearchFormSponsorship] &
"*") AND ((MainTable.AdvertisementWhere) Like
[Forms]![SearchForm]![SearchFormAdvertisement] & "*") AND ((MainTable.Attend)
Like [Forms]![SearchForm]![SearchFormExhibting] & "*") AND
((MainTable.Speaking) Like [Forms]![SearchForm]![SearchFormSpeaking] & "*")
AND ((MainTable.Exhibit) Like [Forms]![SearchForm]![SearchFormExhibting] &
"*") AND ((MainTable.Attendee1First) Like
[Forms]![SearchForm]![SearchFormFirst] & "*") AND ((MainTable.Attendee1Last)
Like [Forms]![SearchForm]![SearchFormLast] & "*")) OR
(((MainTable.ConferenceName) Like [Forms]![SearchForm]![SearchFormConference]
& "*") AND ((MainTable.MonthOfConference) Like
[Forms]![SearchForm]![SearchFormMonth] & "*") AND
((MainTable.YearOfConference) Like [Forms]![SearchForm]![SearchFormYear] &
"*") AND ((MainTable.PracticeArea) Like
[Forms]![SearchForm]![SearchFormPracticeArea] & "*") AND
((MainTable.Sponsorship) Like [Forms]![SearchForm]![SearchFormSponsorship] &
"*") AND ((MainTable.AdvertisementWhere) Like
[Forms]![SearchForm]![SearchFormAdvertisement] & "*") AND ((MainTable.Attend)
Like [Forms]![SearchForm]![SearchFormExhibting] & "*") AND
((MainTable.Speaking) Like [Forms]![SearchForm]![SearchFormSpeaking] & "*")
AND ((MainTable.Exhibit) Like [Forms]![SearchForm]![SearchFormExhibting] &
"*") AND ((MainTable.Attendee2First) Like
[Forms]![SearchForm]![SearchFormFirst] & "*") AND ((MainTable.Attendee2Last)
Like [Forms]![SearchForm]![SearchFormLast] & "*")) OR
(((MainTable.ConferenceName) Like [Forms]![SearchForm]![SearchFormConference]
& "*") AND ((MainTable.MonthOfConference) Like
[Forms]![SearchForm]![SearchFormMonth] & "*") AND
((MainTable.YearOfConference) Like [Forms]![SearchForm]![SearchFormYear] &
"*") AND ((MainTable.PracticeArea) Like
[Forms]![SearchForm]![SearchFormPracticeArea] & "*") AND
((MainTable.Sponsorship) Like [Forms]![SearchForm]![SearchFormSponsorship] &
"*") AND ((MainTable.AdvertisementWhere) Like
[Forms]![SearchForm]![SearchFormAdvertisement] & "*") AND ((MainTable.Attend)
Like [Forms]![SearchForm]![SearchFormExhibting] & "*") AND
((MainTable.Speaking) Like [Forms]![SearchForm]![SearchFormSpeaking] & "*")
AND ((MainTable.Exhibit) Like [Forms]![SearchForm]![SearchFormExhibting] &
"*") AND ((MainTable.Attendee3First) Like
[Forms]![SearchForm]![SearchFormFirst] & "*") AND ((MainTable.Attendee3Last)
Like [Forms]![SearchForm]![SearchFormLast] & "*")) OR
(((MainTable.ConferenceName) Like [Forms]![SearchForm]![SearchFormConference]
& "*") AND ((MainTable.MonthOfConference) Like
[Forms]![SearchForm]![SearchFormMonth] & "*") AND
((MainTable.YearOfConference) Like [Forms]![SearchForm]![SearchFormYear] &
"*") AND ((MainTable.PracticeArea) Like
[Forms]![SearchForm]![SearchFormPracticeArea] & "*") AND
((MainTable.Sponsorship) Like [Forms]![SearchForm]![SearchFormSponsorship] &
"*") AND ((MainTable.AdvertisementWhere) Like
[Forms]![SearchForm]![SearchFormAdvertisement] & "*") AND ((MainTable.Attend)
Like [Forms]![SearchForm]![SearchFormExhibting] & "*") AND
((MainTable.Speaking) Like [Forms]![SearchForm]![SearchFormSpeaking] & "*")
AND ((MainTable.Exhibit) Like [Forms]![SearchForm]![SearchFormExhibting] &
"*") AND ((MainTable.Attendee4First) Like
[Forms]![SearchForm]![SearchFormFirst] & "*") AND ((MainTable.Attendee4Last)
Like [Forms]![SearchForm]![SearchFormLast] & "*")) OR
(((MainTable.ConferenceName) Like [Forms]![SearchForm]![SearchFormConference]
& "*") AND ((MainTable.MonthOfConference) Like
[Forms]![SearchForm]![SearchFormMonth] & "*") AND
((MainTable.YearOfConference) Like [Forms]![SearchForm]![SearchFormYear] &
"*") AND ((MainTable.PracticeArea) Like
[Forms]![SearchForm]![SearchFormPracticeArea] & "*") AND
((MainTable.Sponsorship) Like [Forms]![SearchForm]![SearchFormSponsorship] &
"*") AND ((MainTable.AdvertisementWhere) Like
[Forms]![SearchForm]![SearchFormAdvertisement] & "*") AND ((MainTable.Attend)
Like [Forms]![SearchForm]![SearchFormExhibting] & "*") AND
((MainTable.Speaking) Like [Forms]![SearchForm]![SearchFormSpeaking] & "*")
AND ((MainTable.Exhibit) Like [Forms]![SearchForm]![SearchFormExhibting] &
"*") AND ((MainTable.Attendee5First) Like
[Forms]![SearchForm]![SearchFormFirst] & "*") AND ((MainTable.Attendee5Last)
Like [Forms]![SearchForm]![SearchFormLast] & "*")) OR
(((MainTable.ConferenceName) Like [Forms]![SearchForm]![SearchFormConference]
& "*") AND ((MainTable.MonthOfConference) Like
[Forms]![SearchForm]![SearchFormMonth] & "*") AND
((MainTable.YearOfConference) Like [Forms]![SearchForm]![SearchFormYear] &
"*") AND ((MainTable.PracticeArea) Like
[Forms]![SearchForm]![SearchFormPracticeArea] & "*") AND
((MainTable.Sponsorship) Like [Forms]![SearchForm]![SearchFormSponsorship] &
"*") AND ((MainTable.AdvertisementWhere) Like
[Forms]![SearchForm]![SearchFormAdvertisement] & "*") AND ((MainTable.Attend)
Like [Forms]![SearchForm]![SearchFormExhibting] & "*") AND
((MainTable.Speaking) Like [Forms]![SearchForm]![SearchFormSpeaking] & "*")
AND ((MainTable.Exhibit) Like [Forms]![SearchForm]![SearchFormExhibting] &
"*") AND ((MainTable.Attendee6First) Like
[Forms]![SearchForm]![SearchFormFirst] & "*") AND ((MainTable.Attendee6Last)
Like [Forms]![SearchForm]![SearchFormLast] & "*"));


'69 Camaro said:
Hi.
I am trying to run a query based on values in a form. If the checkbox
[Sponsorship] on the form is Yes, I want the query to return all values other
than "None"

If the checkbox is No, I want it to return values that are "None"
Click to expand...

I'm not entirely sure I understand what you're asking for, but I'll give it
a whirl. If the IIF( ) statement is in the WHERE clause of your query, then
you'll need to compare the "None" or Not "None" to an actual field name and
take into account when the field may be NULL. For example:

SELECT *
FROM tblMyTable
WHERE (IIF(([Forms]![SearchForm]![Sponsorship]=Yes, ((SomeValue <> "None")
OR ISNULL(SomeValue)), (SomeValue = "None"))) ;

... where SomeValue is the name of the field that may or may not contain the
string, "None," and tblMyTable is the name of the table. This example query
returns all fields, not just certain ones, of all records that meet the
criteria according to the checkbox on the form.

If you've designed your query differently, and/or the above example doesn't
shed any light, then please post your SQL so we can help you get what you
want.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


kdagostino said:
I am trying to run a query based on values in a form. If the checkbox
[Sponsorship] on the form is Yes, I want the query to return all values other
than "None"

If the checkbox is No, I want it to return values that are "None"

I have tried several variations of the following but all return no values
for Yes. However, it does work when the checkbox is No.

IIf([Forms]![SearchForm]![Sponsorship]=Yes,Not "None","None")

Anyone see where I'm going wrong?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Parameter using multiple options 0
Option Buttons to Pass Criteria to Query 6
Query based on Check Box 1
multiple criteria queries HELP needed 8
Extend date after checkbox ticked 2
Query criteria based on a Check box 8
Combo Box Criteria Question 1
Radio Button Value In Query 3

Back
Top