How can I enter several lines of text as a criterion and return several lines from my table?

[KitKat]

Right now at work, I get forms e-mailed to me in an Excel chart with a
serial number in one column and a new expiration date for a device's
trial period in another column.


For a while, when people only sent forms with one or two lines, I
didn't mind making a simple parameter query that searched for a serial
number (ESN) and showed me the relevant line of my office's tracking
database so I could change the date, etc.


Here's how it looks:
http://klg.cps.com/temp/googlegroups/accesscurrent1.gif
http://klg.cps.com/temp/googlegroups/accesscurrent2.gif


However, when I get 6-20 requests on the same form, it would be really
nice to just hilight the serial number column from the Excel form I
received...
http://klg.cps.com/temp/googlegroups/extensionrequest.gif
....paste it into a form or something in Access, and have a query pop up

that shows the relevant lines of my database for all of those serial
numbers.


I have searched every keyword on Google Groups and Google that I can
think of. I've looked in reference manuals. I'm just plain stuck and
can't figure out how to do this.


If I'm asking in the wrong place, please redirect me. Thank you.


-KitKat


P.S. Question cross-posted; also on comp.databases.ms-access. I
apologize if this is against your netiquette.

 

[John Vinson]

Right now at work, I get forms e-mailed to me in an Excel chart with a
serial number in one column and a new expiration date for a device's
trial period in another column.


For a while, when people only sent forms with one or two lines, I
didn't mind making a simple parameter query that searched for a serial
number (ESN) and showed me the relevant line of my office's tracking
database so I could change the date, etc.


Here's how it looks:
http://klg.cps.com/temp/googlegroups/accesscurrent1.gif
http://klg.cps.com/temp/googlegroups/accesscurrent2.gif


However, when I get 6-20 requests on the same form, it would be really
nice to just hilight the serial number column from the Excel form I
received...
http://klg.cps.com/temp/googlegroups/extensionrequest.gif
...paste it into a form or something in Access, and have a query pop up

that shows the relevant lines of my database for all of those serial
numbers.

I suggest creating a link to the spreadsheet (using File... Get
External Data... Link, and selecting Excel from "Files Of Type"); you
can then create a Query joining your table's ESN field to the ESN
column in the spreadsheet. That'll get you the same results as a query
criterion with much less hassle!


John W. Vinson[MVP]

 

[KitKat]

I don't think that will work. ESNs aren't the only thing in that Excel
column (see the screenshot I posted), so I can't seem to get that link
wizard to work. Also, I have dozens of different Excel files I receive
a day, so it seems I would have to click through all the link wizard
screens all over again.

 

[John Vinson]

I don't think that will work. ESNs aren't the only thing in that Excel
column (see the screenshot I posted), so I can't seem to get that link
wizard to work. Also, I have dozens of different Excel files I receive
a day, so it seems I would have to click through all the link wizard
screens all over again.

Well, then the only thing I can think of is to use VBA code to, first,
allow the user to select the spreadsheet, and link it using the
TransferSpreadsheet() method; and then select the ESN values (somehow,
if the column has a mix of values I have no idea how you would do so)
and build a SQL statement with an IN clause:

SELECT <whatever> FROM tablename
WHERE ESN IN("blah", "blah", "blah"...)


John W. Vinson[MVP]