Union Query for All

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

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" wrote in message
...
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

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 8-)

"Ken Snell (MVP)" wrote:

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" wrote in message
...
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

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

--

Ken Snell
MS ACCESS MVP


"Tanya" wrote in message
...
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 8-)

"Ken Snell (MVP)" wrote:

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" wrote in message
...
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

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)" wrote:

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

--

Ken Snell
MS ACCESS MVP


"Tanya" wrote in message
...
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 8-)

"Ken Snell (MVP)" wrote:

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" wrote in message
...
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

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" wrote in message
...
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)" wrote:

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

--

Ken Snell
MS ACCESS MVP


"Tanya" wrote in message
...
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 8-)

"Ken Snell (MVP)" wrote:

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" wrote in message
...
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

Hi Ken,

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

Thanks
Tanya


"Ken Snell (MVP)" wrote:

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" wrote in message
...
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)" wrote:

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

--

Ken Snell
MS ACCESS MVP


"Tanya" wrote in message
...
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 8-)

"Ken Snell (MVP)" wrote:

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" wrote in message
...
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