Union Query for All

T

Tanya

Hi All,

I am running a report using the criteria selected from a combo box and i
have used the following union query to accomplish this:
Me.cmbCriteria.RowSource = "SELECT DISTINCT genus, genus FROM Plant UNION
ALL SELECT '<All>', '*' ORDER BY genus;"

I have set a parameter on the report called @genus to equal the selection in
the combo box eg
@Genus=[Forms]![Main]![frmReporting]![cmbCriteria]

When i run the form the combo box works fine and shows a list of the genus
names aswell as the <all> option. and then when i pressed the ok button to
view that report if one of those names are selected it works fine. However
when i select the <all> option it brings the report up with a #error insted
of showing all genus's.
Im sure im just missing something simple.

Thanks in advance
Tanya
 
K

Ken Snell \(MVP\)

Add another part to your report's parameter:

@Genus=[Forms]![Main]![frmReporting]![cmbCriteria] Or
[Forms]![Main]![frmReporting]![cmbCriteria]="<All>"
 
K

Ken Snell \(MVP\)

Post the SQL statement of the report's Record Source query.

--

Ken Snell
<MS ACCESS MVP>


Tanya said:
Thanks Ken, but now im getting the following error when i try to run it
using
<ALL>
Run-time error "8144":
Procedure or function rptGenus has too many arguments specified.

it seems to be falling over on the last line in the btnclick:
If Not IsNull(Me.cmbReporting) Then
DoCmd.OpenReport Me.cmbReporting, acViewPreview 'falling over on
this line
End If

Thanks again for your help :cool:

Ken Snell (MVP) said:
Add another part to your report's parameter:

@Genus=[Forms]![Main]![frmReporting]![cmbCriteria] Or
[Forms]![Main]![frmReporting]![cmbCriteria]="<All>"

--

Ken Snell
<MS ACCESS MVP>




Tanya said:
Hi All,

I am running a report using the criteria selected from a combo box and
i
have used the following union query to accomplish this:
Me.cmbCriteria.RowSource = "SELECT DISTINCT genus, genus FROM Plant
UNION
ALL SELECT '<All>', '*' ORDER BY genus;"

I have set a parameter on the report called @genus to equal the
selection
in
the combo box eg
@Genus=[Forms]![Main]![frmReporting]![cmbCriteria]

When i run the form the combo box works fine and shows a list of the
genus
names aswell as the <all> option. and then when i pressed the ok button
to
view that report if one of those names are selected it works fine.
However
when i select the <all> option it brings the report up with a #error
insted
of showing all genus's.
Im sure im just missing something simple.

Thanks in advance
Tanya
Click to expand...
Click to expand...
Click to expand...
 
T

Tanya

Hi Ken,

Thanks for looking at it for me. below is the query i am using for the
report, This is a Access .adp frontend to a sql server database backend:

SELECT dbo.Plant.ID, dbo.Plant.Genus, dbo.Plant.Species,
bo.Plant.CountryofOrigin, dbo.PlantAccession.Location,
dbo.PlantAccession.AccessionNo, bo.Plant.SubSpecies, dbo.Plant.Family,
dbo.Plant.Synonym, dbo.Plant.CommonName, dbo.Plant.PlantForm,
dbo.Plant.Hybrid, dbo.Plant.Cultivar, dbo.Plant.TradeName, dbo.Plant.Variaty,
dbo.PlantAccession.PlantStatus
FROM dbo.Plant
INNER JOIN dbo.PlantAccession ON dbo.Plant.ID = dbo.PlantAccession.ID
WHERE (dbo.PlantAccession.PlantStatus IS NULL) AND (dbo.Plant.Genus = @genus)
ORDER BY dbo.Plant.Genus, dbo.Plant.Species, dbo.PlantAccession.AccessionNo

Thanks for your help
Tanya

Ken Snell (MVP) said:
Post the SQL statement of the report's Record Source query.

--

Ken Snell
<MS ACCESS MVP>


Tanya said:
Thanks Ken, but now im getting the following error when i try to run it
using
<ALL>
Run-time error "8144":
Procedure or function rptGenus has too many arguments specified.

it seems to be falling over on the last line in the btnclick:
If Not IsNull(Me.cmbReporting) Then
DoCmd.OpenReport Me.cmbReporting, acViewPreview 'falling over on
this line
End If

Thanks again for your help :cool:

Ken Snell (MVP) said:
Add another part to your report's parameter:

@Genus=[Forms]![Main]![frmReporting]![cmbCriteria] Or
[Forms]![Main]![frmReporting]![cmbCriteria]="<All>"

--

Ken Snell
<MS ACCESS MVP>




Hi All,

I am running a report using the criteria selected from a combo box and
i
have used the following union query to accomplish this:
Me.cmbCriteria.RowSource = "SELECT DISTINCT genus, genus FROM Plant
UNION
ALL SELECT '<All>', '*' ORDER BY genus;"

I have set a parameter on the report called @genus to equal the
selection
in
the combo box eg
@Genus=[Forms]![Main]![frmReporting]![cmbCriteria]

When i run the form the combo box works fine and shows a list of the
genus
names aswell as the <all> option. and then when i pressed the ok button
to
view that report if one of those names are selected it works fine.
However
when i select the <all> option it brings the report up with a #error
insted
of showing all genus's.
Im sure im just missing something simple.

Thanks in advance
Tanya
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Try this:

SELECT dbo.Plant.ID, dbo.Plant.Genus, dbo.Plant.Species,
bo.Plant.CountryofOrigin, dbo.PlantAccession.Location,
dbo.PlantAccession.AccessionNo, bo.Plant.SubSpecies, dbo.Plant.Family,
dbo.Plant.Synonym, dbo.Plant.CommonName, dbo.Plant.PlantForm,
dbo.Plant.Hybrid, dbo.Plant.Cultivar, dbo.Plant.TradeName,
dbo.Plant.Variaty,
dbo.PlantAccession.PlantStatus
FROM dbo.Plant
INNER JOIN dbo.PlantAccession ON dbo.Plant.ID = dbo.PlantAccession.ID
WHERE (dbo.PlantAccession.PlantStatus IS NULL) AND
((dbo.Plant.Genus = @genus) OR (@genus = "<All>"))
ORDER BY dbo.Plant.Genus, dbo.Plant.Species, dbo.PlantAccession.AccessionNo


--

Ken Snell
<MS ACCESS MVP>



Tanya said:
Hi Ken,

Thanks for looking at it for me. below is the query i am using for the
report, This is a Access .adp frontend to a sql server database backend:

SELECT dbo.Plant.ID, dbo.Plant.Genus, dbo.Plant.Species,
bo.Plant.CountryofOrigin, dbo.PlantAccession.Location,
dbo.PlantAccession.AccessionNo, bo.Plant.SubSpecies, dbo.Plant.Family,
dbo.Plant.Synonym, dbo.Plant.CommonName, dbo.Plant.PlantForm,
dbo.Plant.Hybrid, dbo.Plant.Cultivar, dbo.Plant.TradeName,
dbo.Plant.Variaty,
dbo.PlantAccession.PlantStatus
FROM dbo.Plant
INNER JOIN dbo.PlantAccession ON dbo.Plant.ID = dbo.PlantAccession.ID
WHERE (dbo.PlantAccession.PlantStatus IS NULL) AND (dbo.Plant.Genus =
@genus)
ORDER BY dbo.Plant.Genus, dbo.Plant.Species,
dbo.PlantAccession.AccessionNo

Thanks for your help
Tanya

Ken Snell (MVP) said:
Post the SQL statement of the report's Record Source query.

--

Ken Snell
<MS ACCESS MVP>


Tanya said:
Thanks Ken, but now im getting the following error when i try to run it
using
<ALL>
Run-time error "8144":
Procedure or function rptGenus has too many arguments specified.

it seems to be falling over on the last line in the btnclick:
If Not IsNull(Me.cmbReporting) Then
DoCmd.OpenReport Me.cmbReporting, acViewPreview 'falling over on
this line
End If

Thanks again for your help :cool:

:

Add another part to your report's parameter:

@Genus=[Forms]![Main]![frmReporting]![cmbCriteria] Or
[Forms]![Main]![frmReporting]![cmbCriteria]="<All>"

--

Ken Snell
<MS ACCESS MVP>




Hi All,

I am running a report using the criteria selected from a combo box
and
i
have used the following union query to accomplish this:
Me.cmbCriteria.RowSource = "SELECT DISTINCT genus, genus FROM Plant
UNION
ALL SELECT '<All>', '*' ORDER BY genus;"

I have set a parameter on the report called @genus to equal the
selection
in
the combo box eg
@Genus=[Forms]![Main]![frmReporting]![cmbCriteria]

When i run the form the combo box works fine and shows a list of the
genus
names aswell as the <all> option. and then when i pressed the ok
button
to
view that report if one of those names are selected it works fine.
However
when i select the <all> option it brings the report up with a #error
insted
of showing all genus's.
Im sure im just missing something simple.

Thanks in advance
Tanya
Click to expand...
Click to expand...
Click to expand...
 
T

Tanya

Hi Ken,

Fantastic thank you so much for your help it works a treat.

Thanks
Tanya


Ken Snell (MVP) said:
Try this:

SELECT dbo.Plant.ID, dbo.Plant.Genus, dbo.Plant.Species,
bo.Plant.CountryofOrigin, dbo.PlantAccession.Location,
dbo.PlantAccession.AccessionNo, bo.Plant.SubSpecies, dbo.Plant.Family,
dbo.Plant.Synonym, dbo.Plant.CommonName, dbo.Plant.PlantForm,
dbo.Plant.Hybrid, dbo.Plant.Cultivar, dbo.Plant.TradeName,
dbo.Plant.Variaty,
dbo.PlantAccession.PlantStatus
FROM dbo.Plant
INNER JOIN dbo.PlantAccession ON dbo.Plant.ID = dbo.PlantAccession.ID
WHERE (dbo.PlantAccession.PlantStatus IS NULL) AND
((dbo.Plant.Genus = @genus) OR (@genus = "<All>"))
ORDER BY dbo.Plant.Genus, dbo.Plant.Species, dbo.PlantAccession.AccessionNo


--

Ken Snell
<MS ACCESS MVP>



Tanya said:
Hi Ken,

Thanks for looking at it for me. below is the query i am using for the
report, This is a Access .adp frontend to a sql server database backend:

SELECT dbo.Plant.ID, dbo.Plant.Genus, dbo.Plant.Species,
bo.Plant.CountryofOrigin, dbo.PlantAccession.Location,
dbo.PlantAccession.AccessionNo, bo.Plant.SubSpecies, dbo.Plant.Family,
dbo.Plant.Synonym, dbo.Plant.CommonName, dbo.Plant.PlantForm,
dbo.Plant.Hybrid, dbo.Plant.Cultivar, dbo.Plant.TradeName,
dbo.Plant.Variaty,
dbo.PlantAccession.PlantStatus
FROM dbo.Plant
INNER JOIN dbo.PlantAccession ON dbo.Plant.ID = dbo.PlantAccession.ID
WHERE (dbo.PlantAccession.PlantStatus IS NULL) AND (dbo.Plant.Genus =
@genus)
ORDER BY dbo.Plant.Genus, dbo.Plant.Species,
dbo.PlantAccession.AccessionNo

Thanks for your help
Tanya

Ken Snell (MVP) said:
Post the SQL statement of the report's Record Source query.

--

Ken Snell
<MS ACCESS MVP>


Thanks Ken, but now im getting the following error when i try to run it
using
<ALL>
Run-time error "8144":
Procedure or function rptGenus has too many arguments specified.

it seems to be falling over on the last line in the btnclick:
If Not IsNull(Me.cmbReporting) Then
DoCmd.OpenReport Me.cmbReporting, acViewPreview 'falling over on
this line
End If

Thanks again for your help :cool:

:

Add another part to your report's parameter:

@Genus=[Forms]![Main]![frmReporting]![cmbCriteria] Or
[Forms]![Main]![frmReporting]![cmbCriteria]="<All>"

--

Ken Snell
<MS ACCESS MVP>




Hi All,

I am running a report using the criteria selected from a combo box
and
i
have used the following union query to accomplish this:
Me.cmbCriteria.RowSource = "SELECT DISTINCT genus, genus FROM Plant
UNION
ALL SELECT '<All>', '*' ORDER BY genus;"

I have set a parameter on the report called @genus to equal the
selection
in
the combo box eg
@Genus=[Forms]![Main]![frmReporting]![cmbCriteria]

When i run the form the combo box works fine and shows a list of the
genus
names aswell as the <all> option. and then when i pressed the ok
button
to
view that report if one of those names are selected it works fine.
However
when i select the <all> option it brings the report up with a #error
insted
of showing all genus's.
Im sure im just missing something simple.

Thanks in advance
Tanya
Click to expand...
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

SQL ADP Reports not working due to Authentication 4
wildcard in a query form 2
Dynamic Naming of Form Button 3
Filter by selection is inappropriately "sticky" 6
Filter by selection is inappropriately "sticky" 0
union query for report 4
Report using union query 2
sorting in a normalizing union query 8

Top