Promt query

[Thea]

I would like to create a prompt query where the user can type the information
in a text box and then select what column they would like to search and
output the unique ID. For example. You may want to search for a serial number
in the S/N field of the main table and the output would be the table ID
number.

 

[KARL DEWEY]

Use a form with an unbound text box and an Option Group frame.
The SQL will look like this --
SELECT ProjectData.ProjectID, ProjectData.ProjectName,
ProjectData.ProjectType, [Forms]![YourForm]![TextBox] AS Expr1,
[Forms]![YourForm]![Frame45] AS Expr2
FROM ProjectData
WHERE (((ProjectData.ProjectID)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![Frame45])=1)) OR
(((ProjectData.ProjectName)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![Frame45])=2)) OR
(((ProjectData.ProjectType)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![Frame45])=3)) OR ((([Forms]![YourForm]![Frame45])=4));

You would enter data in the textbox and then select the option This query
has option 4 as all records. You also need to have an event on the option
selector to run the query.

 

[Thea]

Thanks Karl, I got bit stuck on the SQL

My table name is Tplant

Field name Are: Rego SerialNo PrevRego
039JZY 15987 AJ34HG
061DIB 36549 702BFI
I have the form setup and saved as selectform

Use a form with an unbound text box and an Option Group frame.
The SQL will look like this --
SELECT ProjectData.ProjectID, ProjectData.ProjectName,
ProjectData.ProjectType, [Forms]![YourForm]![TextBox] AS Expr1,
[Forms]![YourForm]![Frame45] AS Expr2
FROM ProjectData
WHERE (((ProjectData.ProjectID)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![Frame45])=1)) OR
(((ProjectData.ProjectName)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![Frame45])=2)) OR
(((ProjectData.ProjectType)=[Forms]![YourForm]![TextBox]) AND
(([Forms]![YourForm]![Frame45])=3)) OR ((([Forms]![YourForm]![Frame45])=4));

You would enter data in the textbox and then select the option This query
has option 4 as all records. You also need to have an event on the option
selector to run the query.
--
KARL DEWEY
Build a little - Test a little

I would like to create a prompt query where the user can type the information
in a text box and then select what column they would like to search and
output the unique ID. For example. You may want to search for a serial number
in the S/N field of the main table and the output would be the table ID
number.