Using text in a Form to run a Query

J

JWPosey

I run a query every day against our company database to pull in specific
information. I then store this information locally in my Access db. To save
on space, once I'm done reviewing a record, I delete some of the fields to
keep the db space low.

My problem is that in the future I may have to retrieve these fields. I
keep the ticket number in the Access db the same as our company database
(they both have the exact same format). I create a form so that tickets can
be inputted and then try to retrieve them from the company db, but it doesn't
work if I try to retrieve multiple ticket numbers. In the form I put an "OR"
between the tickets so that it is included in the query. Here is what my
query SQL looks like:

SELECT
.[Trouble_Ticket_#]
FROM

WHERE (((
.[Trouble_Ticket_#])=[Forms]![Retrieve Reviewed Records
Form]![Tickets]));
 
J

Jerry Whittle

I tried converting your SQL from = to IN but didn't have any luck even when
putting quotation marks and commas in the form's text field.

What you could try is putting multiple fields in the form, say 4 of them,
and run it. The SQL would look something like below.

SELECT T.[Trouble_Ticket_#]
FROM
as T
WHERE
T.[Trouble_Ticket_#] = [Forms]![Retrieve Reviewed Records
Form]![Tickets1]
OR T.[Trouble_Ticket_#] = [Forms]![Retrieve Reviewed Records Form]![Tickets2]
OR T.[Trouble_Ticket_#] = [Forms]![Retrieve Reviewed Records Form]![Tickets3]
OR T.[Trouble_Ticket_#] = [Forms]![Retrieve Reviewed Records Form]![Tickets4]
;
 
J

John W. Vinson

I run a query every day against our company database to pull in specific
information. I then store this information locally in my Access db. To save
on space, once I'm done reviewing a record, I delete some of the fields to
keep the db space low.

Two points here: unless you Compact the database, it won't free up any space
at all; and unless your database is approaching the 2 GByte limit, you really
don't need to do so. If you're actually *deleting the fields* from the table
design, you're REALLY on the wrong track - I hope you mean that you're just
setting the fields to NULL.
My problem is that in the future I may have to retrieve these fields.

Well... then don't delete them. Deleted is GONE and they can never be
retrieved.
I keep the ticket number in the Access db the same as our company database
(they both have the exact same format). I create a form so that tickets can
be inputted and then try to retrieve them from the company db, but it doesn't
work if I try to retrieve multiple ticket numbers. In the form I put an "OR"
between the tickets so that it is included in the query. Here is what my
query SQL looks like:

SELECT
.[Trouble_Ticket_#]
FROM

WHERE (((
.[Trouble_Ticket_#])=[Forms]![Retrieve Reviewed Records
Form]![Tickets]));


You cannot pass IN or OR or a comma or any other operator in a parameter -
e.g. putting

3, 65, 171, 225

or

3 OR 65 OR 171 OR 225

or

IN (3, 65, 171, 225)

will fail. I would suggest instead using the first option - just a list of
ticket numbers separated by commas - but instead of using a parameter query,
actually use VBA code to construct an entire query SQL statement.


John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top