UNION SELECT "(ALL)" Query Not Working

[nouveauricheinvestments]

hi,

I have entered the following expression in the criteria field of a
query I am using in a form/subform.

IIf([Forms]![Current Unapproved Tickets]![Combo3]=0,"*",[Forms]!
[Current Unapproved Tickets![Combo3])

In my combo box record source I have the following SQL query:

SELECT ID, Trade_Specialist FROM Trade_Specialists
UNION SELECT 0,"(ALL)" FROM Trade_Specialists
ORDER BY Trade_Specialist;

every option in my combo box works fine except the all optionn. it
tells me the following when I select it.

The expression is typed incorrectly, or is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.

 

[Marshall Barton]

I have entered the following expression in the criteria field of a
query I am using in a form/subform.

IIf([Forms]![Current Unapproved Tickets]![Combo3]=0,"*",[Forms]!
[Current Unapproved Tickets![Combo3])

In my combo box record source I have the following SQL query:

SELECT ID, Trade_Specialist FROM Trade_Specialists
UNION SELECT 0,"(ALL)" FROM Trade_Specialists
ORDER BY Trade_Specialist;

every option in my combo box works fine except the all optionn. it
tells me the following when I select it.

The expression is typed incorrectly, or is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.

Try it this way:

=[Forms]![Current Unapproved Tickets]![Combo3] OR
[Forms]![Current Unapproved Tickets]![Combo3]=0

 

[Golfinray]

Try union select *

 

[Duane Hookom]

Could you share the full SQL of your query? You might need to enter the data
type of the combo3 into the query parameters.

I have a feeling the field you are comparing to is numeric and you might be
comparing it to "*".

 

[nouveauricheinvestments]

Could you share the full SQL of your query? You might need to enter the data
type of the combo3 into the query parameters.

I have a feeling the field you are comparing to is numeric and you might be
comparing it to "*".
--
Duane Hookom
Microsoft Access MVP

hi,

I have entered the following expression in the criteria field of a
query I am using in a form/subform.

IIf([Forms]![Current Unapproved Tickets]![Combo3]=0,"*",[Forms]!
[Current Unapproved Tickets![Combo3])

In my combo box record source I have the following SQL query:

SELECT ID, Trade_Specialist FROM Trade_Specialists
UNION SELECT 0,"(ALL)" FROM Trade_Specialists
ORDER BY Trade_Specialist;

every option in my combo box works fine except the all optionn. it
tells me the following when I select it.

The expression is typed incorrectly, or is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.

I just tried what Marshall suggested and it worked perfectly. Thank
you Marshall and everyone else for your suggestions. While we are all
looking at this, might anyone know what is wrong with this control
source? I have it right above my combo box on the same form. It is
supposed to be counting the records visible below in my subform (or
the related query, whichever I'm not sure) but it just gives me an
error.

=Count([Current Tickets Not Approved By Supervisor]![Account])

'Current Tickets Not Approved By Supervisor' is the name of my
subform.

 

[Marshall Barton]

Count (and all the other aggregate function: Sum, Avg, etc)
only operate on record source fields, they are unaware of
controls on a form, much less in a subform.

The subform can calculate its own count using a text box's
control source expression =Count(*) and a main form text
box can display the count by using an expression like:
=subformcontrolname.Form.thecounttextbox

A different mechanism to get the number of records in a
subform is to use an expression like:
=subformcontrolname.Form.Recordset.RecordCount
but that will not provide the total number of subfotm
records unless the subform's recordsource is a table, you
have navigated to the last record, or the subform's Load
event has the line of code:
Me.RecordsetClone.MoveLast

 

[nouveauricheinvestments]

Count (and all the other aggregate function: Sum, Avg, etc)
only operate on record source fields, they are unaware of
controls on a form, much less in a subform.

The subform can calculate its own count using a text box's
control source expression =Count(*) and a main form text
box can display the count by using an expression like:
=subformcontrolname.Form.thecounttextbox

A different mechanism to get the number of records in a
subform is to use an expression like:
=subformcontrolname.Form.Recordset.RecordCount
but that will not provide the total number of subfotm
records unless the subform's recordsource is a table, you
have navigated to the last record, or the subform's Load
event has the line of code:
Me.RecordsetClone.MoveLast

Thank you...I added a textbox to the subform that is not visible and
set the value of a textbox on my form to the value of that. It seems
like kind of a work around, but it works nonetheless..