"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
