User Input for a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mario - 09 Aug 2007 20:28 GMT

How can I get a user input for a query. The users input will be used in the
"where" field.

For the example below, user will type the name of the student.

Select Rank, Score, School From StudentRecord Where Name={{User inputs the
name when the query is run}}

Thanks

Reply to this Message

Ofer Cohen - 09 Aug 2007 20:32 GMT

Use

Select Rank, Score, School From StudentRecord Where [Name]=[ Please enter
name]

Also you can use
Select Rank, Score, School From StudentRecord Where [Name]=[ Please enter
name] Or [ Please enter name] Is Null

to display all the records if the user didn't write a name

Signature

Good Luck
BS"D

> How can I get a user input for a query. The users input will be used in the
> "where" field.
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

Jamie Collins - 10 Aug 2007 10:54 GMT

> Use
>
[quoted text clipped - 4 lines]
> Select Rank, Score, School From StudentRecord Where [Name]=[ Please enter
> name] Or [ Please enter name] Is Null

You can also use (aircode):

SELECT Rank, Score, School
FROM StudentRecord
WHERE [NAME] =
IFF([Please enter name] IS NULL, [NAME], [Please enter name]);

SELECT Rank, Score, School
FROM StudentRecord
WHERE [NAME] = Nz([Please enter name], [NAME]);

SELECT Rank, Score, School
FROM StudentRecord
WHERE IIF([NAME] IS NULL, '{{NONE}}', [NAME]) =
IIF([Please enter name] IS NULL, IIF([NAME] IS NULL, '{{NONE}}', [NAME]),
[Please enter name]);

SELECT Rank, Score, School
FROM StudentRecord
WHERE Nz([NAME], '{{NONE}}') =
Nz([Please enter name], Nz([NAME], '{{NONE}}'));

PARAMETERS [Please enter name] TEXT (35);
SELECT Rank, Score, School
FROM StudentRecord
WHERE [NAME] = Nz([Please enter name], [NAME]);

CREATE PROCEDURE
(
[Please enter NAME] VARCHAR(35) = NULL
)
AS
SELECT Rank, Score, School
FROM StudentRecord
WHERE [NAME] =
IFF([Please enter name] IS NULL, [NAME], [Please enter name]);

and combinations thereof, not to mention using SWITCH and other variations...

Jamie.

--

Reply to this Message

rich - 10 Aug 2007 17:32 GMT

Hello All,

I am doing something similar. On my (employee) form, I need the user to
enter either an employeeID or the employee last and first names. In my case,
clicking a "perform search"/"Submit" button should:
1. Execute the select query
2. Unhide all the previously hidden fields on the employee form and poplate
them with the data returned from the query. In my application, some fields
will remain hidden of disabled because only a few of the fields will then be
updated.
3. After updating the relevant fields, perfom an update query when the
submit button is clicked.

What other things need to be considered in such a search then update
scenerio such as this? How can I check for success or failure of the query,
that do some error trapping?

TIA,

Rich

> > Use
> >
[quoted text clipped - 47 lines]
>
> --

Reply to this Message