Problems with Adding <all> as a parameter

  • Thread starter Thread starter Deb Smith
  • Start date Start date
D

Deb Smith

I have a form that is used to set the parameters for a report. On this form
I have 3 unbound combo boxes Using the following union query, I added all to
the combo box called Occassion

SELECT OccassionType.Occassion, OccassionType.OccassionID FROM OccassionType
UNION SELECT '<ALL>', '0' FROM OccassionType
ORDER BY OccassionType.Occassion;

In the query for my report I have been using
[Forms]![Form1]![Occassion]
to obtain the selected criteria from Form1. This does not work now that I
have added <ALL> to the choices.

How do I change my query so that it recognizes <ALL> when <ALL> is the
choice selected from an unbound combo box on a form and in turn create the
report showing all records?


Any help would be greatly appreciated.

Thanks
 
Assuming that the second column is the bound column for your combo box:

IIf([Forms]![Form1]![Occassion] = 0, "*", [Forms]![Form1]![Occassion])
 
Sorry - error in the post I just posted:

Like IIf([Forms]![Form1]![Occassion] = 0, "*", [Forms]![Form1]![Occassion])
 
Thanks so much for the response.

Unfortunately, I am still running into problems. As per your post,I added
the statement you furnished to the criteria in my query.When I select <ALL>
from my form and attempt to run the report, I get a message telling me no
records are found eventhough I know there is data that should be
available.Furthermore, no matter what I choose from the combo box, Iget the
same message...no records found.

If you have any alternative suggestions, or can tell me what I am doing, or
further explain the statement you furnished so I can attempt to resolve the
issue I would be very thankful.
Note: The second column in my combo box is the bound column and <ALL> has
been given a value of 0 through my union query....I think)

Thanks again for attempting to help me resolve this problem.


Ken Snell said:
Sorry - error in the post I just posted:

Like IIf([Forms]![Form1]![Occassion] = 0, "*", [Forms]![Form1]![Occassion])


--

Ken Snell
<MS ACCESS MVP>

Deb Smith said:
I have a form that is used to set the parameters for a report. On this form
I have 3 unbound combo boxes Using the following union query, I added
Click to expand...
all
to
the combo box called Occassion

SELECT OccassionType.Occassion, OccassionType.OccassionID FROM OccassionType
UNION SELECT '<ALL>', '0' FROM OccassionType
ORDER BY OccassionType.Occassion;

In the query for my report I have been using
[Forms]![Form1]![Occassion]
to obtain the selected criteria from Form1. This does not work now that I
have added <ALL> to the choices.

How do I change my query so that it recognizes <ALL> when <ALL> is the
choice selected from an unbound combo box on a form and in turn create the
report showing all records?


Any help would be greatly appreciated.

Thanks
Click to expand...
Click to expand...
 
I got the report to open properly using the criteria statment you provided.
I had made a silly typing mistake. Sorry.

I would however like to understand the criteria statement you furnished. Is
in essence the statement saying if [Forms]![Form1]![Occassion] = 0 then "*"
(show all records) or return the result of Forms]![Form1]![Occassion].

I am trying to understand what I am doing not just doing so any explanation
would be appreciated.

Thanks again for helping me solve my problem.



Deb Smith said:
Thanks so much for the response.

Unfortunately, I am still running into problems. As per your post,I added
the statement you furnished to the criteria in my query.When I select
Click to expand...
from my form and attempt to run the report, I get a message telling me no
records are found eventhough I know there is data that should be
available.Furthermore, no matter what I choose from the combo box, Iget the
same message...no records found.

If you have any alternative suggestions, or can tell me what I am doing, or
further explain the statement you furnished so I can attempt to resolve the
issue I would be very thankful.
Note: The second column in my combo box is the bound column and <ALL> has
been given a value of 0 through my union query....I think)

Thanks again for attempting to help me resolve this problem.


Ken Snell said:
Sorry - error in the post I just posted:

Like IIf([Forms]![Form1]![Occassion] = 0, "*", [Forms]![Form1]![Occassion])


--

Ken Snell
<MS ACCESS MVP>

Deb Smith said:
I have a form that is used to set the parameters for a report. On this form
I have 3 unbound combo boxes Using the following union query, I added
Click to expand...
all
to
the combo box called Occassion

SELECT OccassionType.Occassion, OccassionType.OccassionID FROM OccassionType
UNION SELECT '<ALL>', '0' FROM OccassionType
ORDER BY OccassionType.Occassion;

In the query for my report I have been using
[Forms]![Form1]![Occassion]
to obtain the selected criteria from Form1. This does not work now
Click to expand...
Click to expand...
that
I
Click to expand...
Click to expand...
 
Answers inline...

--

Ken Snell
<MS ACCESS MVP>


Deb Smith said:
I got the report to open properly using the criteria statment you provided.
I had made a silly typing mistake. Sorry.
Click to expand...

Ah...happens to all of us.
I would however like to understand the criteria statement you furnished. Is
in essence the statement saying if [Forms]![Form1]![Occassion] = 0 then "*"
(show all records) or return the result of Forms]![Form1]![Occassion].
Click to expand...

Yes. You are right. When the wildcard * is all by itself in the criterion
expression, it means return all records.
I am trying to understand what I am doing not just doing so any explanation
would be appreciated.
Click to expand...

Excellent approach to learning and using ACCESS!
Thanks again for helping me solve my problem.
Click to expand...

You're welcome.
Deb Smith said:
Thanks so much for the response.

Unfortunately, I am still running into problems. As per your post,I added
the statement you furnished to the criteria in my query.When I select
Click to expand...
from my form and attempt to run the report, I get a message telling me no
records are found eventhough I know there is data that should be
available.Furthermore, no matter what I choose from the combo box, Iget the
same message...no records found.

If you have any alternative suggestions, or can tell me what I am doing, or
further explain the statement you furnished so I can attempt to resolve the
issue I would be very thankful.
Note: The second column in my combo box is the bound column and <ALL> has
been given a value of 0 through my union query....I think)

Thanks again for attempting to help me resolve this problem.


Ken Snell said:
Sorry - error in the post I just posted:

Like IIf([Forms]![Form1]![Occassion] = 0, "*", [Forms]![Form1]![Occassion])


--

Ken Snell
<MS ACCESS MVP>

I have a form that is used to set the parameters for a report. On this
form
I have 3 unbound combo boxes Using the following union query, I
Click to expand...
added
all
to
the combo box called Occassion

SELECT OccassionType.Occassion, OccassionType.OccassionID FROM
OccassionType
UNION SELECT '<ALL>', '0' FROM OccassionType
ORDER BY OccassionType.Occassion;

In the query for my report I have been using
[Forms]![Form1]![Occassion]
to obtain the selected criteria from Form1. This does not work now
Click to expand...
that
I
have added <ALL> to the choices.

How do I change my query so that it recognizes <ALL> when <ALL> is the
choice selected from an unbound combo box on a form and in turn
Click to expand...
create
the
report showing all records?


Any help would be greatly appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top