Promt query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thea - 22 May 2008 06:59 GMT

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.

Reply to this Message

KARL DEWEY - 22 May 2008 19:25 GMT

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.

Signature

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.

Reply to this Message

Thea - 23 May 2008 05:35 GMT

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 --
[quoted text clipped - 18 lines]
> > in the S/N field of the main table and the output would be the table ID
> > number.

Reply to this Message