By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Problem with a Union of two criteria in a query

P: n/a
Hi,

I am writing a query that should prompt a user for the name or the ID
of an application. I put the same prompt text in the criteria column
of the Application ID and the "or" column of the Name column.

The SQL view shows the following text:

HAVING ((([Test Runs].[Application ID])=[Enter Application name or
ID])) OR (((Applications.Name)=[Enter Application name or ID]));

When I insert an Application number, it works fine, but when I enter
text in response to the prompt, I get the "The expression is typed
incorrectly or is too complex to evaluate..." error.

Having a criterion for only one of the fields works fine with either
the name or the ID. When I put the two together (OR them) the order
doesn't matter. It doesn't work with either.

Thanks
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Jimmy123" <yo*******@gmail.com> wrote in message
news:9a**************************@posting.google.c om...
Hi,

I am writing a query that should prompt a user for the name or the ID
of an application. I put the same prompt text in the criteria column
of the Application ID and the "or" column of the Name column.

The SQL view shows the following text:

HAVING ((([Test Runs].[Application ID])=[Enter Application name or
ID])) OR (((Applications.Name)=[Enter Application name or ID]));

When I insert an Application number, it works fine, but when I enter
text in response to the prompt, I get the "The expression is typed
incorrectly or is too complex to evaluate..." error.

Having a criterion for only one of the fields works fine with either
the name or the ID. When I put the two together (OR them) the order
doesn't matter. It doesn't work with either.


Whatever you enter is going to be applied to BOTH criteria. The following will
be properly parsed by Access...

HAVING ((([Test Runs].[Application ID])=123)) OR (((Applications.Name)=123));

....because it will implicitly convert the entry (1) to the string "1". When
given the following though...

HAVING ((([Test Runs].[Application ID])="SomeName")) OR
(((Applications.Name)="SomeName"));

....Access encounters a type mismatch error in the first part because you are
comparing a string to a numeric field.

Your needs are sufficiently complex that I recommend dropping the use of a
parameter query and instead providing a form for the user to specify the
criteria. Self-prompting parameter queries are only well suited to the simplest
of situations and are rarely used in a production quality application.

With a form you could have a ComboBox that displays the Application Name, but
actually evaluates to the Application ID so the user can always use the name,
but your criteria can always use the ID.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.