Query based on multiple parameters - Access 2003

I would like to create a query where the user can input 2 or more parameters 
(part numbers), separated by comma and/or space and return information about 
that part.  I already have a query that accepts one parameter.  Is there a 
way to do this with more than one?  

I have read some posts that say to use a table to serve up the paraments and 
the user chooses one or more.  However, a particular part or parts may not 
yet be in the table as they are new or unknown.  

Is there any way to do this without using an existing table but just accept 
input from the user on the fly?  

Thanks!
0
Utf
5/24/2010 6:14:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1260 Views

Similar Articles

[PageSpeed] 35

Pam wrote:
> I would like to create a query where the user can input 2 or more
> parameters (part numbers), separated by comma and/or space and return
> information about that part.  I already have a query that accepts one
> parameter.  Is there a way to do this with more than one?
>
> I have read some posts that say to use a table to serve up the
> paraments and the user chooses one or more.  However, a particular
> part or parts may not yet be in the table as they are new or unknown.
>
> Is there any way to do this without using an existing table but just
> accept input from the user on the fly?
>

Here is a compilation of posts about how to deal with this issue:
There are two solutions for this problem listed in the following KB
article
(Q210530 - ACC2000: How to Create a Parameter In() Statement), found by
searching for the keywords "parameter list query" (no quotes) at
http://support.microsoft.com.

http://support.microsoft.com/suppor...s/Q210/5/30.ASP

The first solution uses Instr() to test the field values against the
list in
the parameter. The second involves dynamically creating a SQL statement
in
code.

Thanks to Paul Overway, here is a third solution, using the Eval
function:

WHERE (((Eval([Table]![Field] & " In(" &
[Forms]![Formname]![textboxname] &
")"))=True))

or, using a prompted parameter:

WHERE (((Eval([Table]![Field] & " In(" & [Enter List] & ")"))=True))


Thanks to Jeffrey A. Williams, here's a 4th solution:

If you don't mind adding a table to your database, and you're
comfortable
dealing with possible multi-user issues, this will perform better than
either of the solutions that involve running a function (Instr or Eval)
on
every row of your table:

Create a new table with two fields:

tblCriteria:
Criteria text
Selected boolean (yes/no)

Populate the table with your values and select a couple of items. Now
you
can use this table in your query as such:

Select * from table1
inner join tblcriteria
on table1.[your criteria field] = tblcriteria.criteria
where tblcriteria.selected = true

You can easily setup a form (or subform) that is bound to tblCriteria
and
allow the users the
ability of selecting which values they want.



Thanks to Michel Walsh, here's yet another way:

SELECT Table3.ConName, Table3.State, Table3.Zip
FROM Table3
WHERE "," &[list] & "," LIKE "*," & [ConName] & ",*"

with [param] some string like: '1,4,5,7'

note that there is no space after the comas.


It works simply. If AccountID is 45, clearly ',1,4,5,7,' LIKE
'*,45,*' returns false.
If AccountID is 4, on the other hand, ',1,4,5,7,' LIKE '*,4,*'
returns true.

So, you have, in effect, an IN( ) where the list is a parameter.


-- 
HTH,
Bob Barrows


0
Bob
5/24/2010 6:21:20 PM
Reply:

Similar Artilces:

Installing Access 2010 in x64 OS
I want to install Microsoft office professional 2010 beta version in x64 XP professional OS. The OS has currently Service pack 2 being installed. I tried to install service pack 3 also, but it was not installed and later found on some site that there is no service pack 3 for 64 bit OS. Does this mean that 64 bit version of microsoft office is not supported or could not be installed in 64 bit OS ? Thanks... The 64-bit Office can only be installed on a 64-bit OS. I don't know, but I would guess that Win XP x64 is not supported and you need either Vista or Win7 x64. XP...

Display Multiple images
SGksDQogICAgICBKdXN0IHdhbnQgdG8gY2hlY2sgd2hldGhlciBpcyBpdCBwb3NzaWJsZSB0byBk aXNwbGF5IG11bHRpcGxlIGltYWdlcyB1c2luZyBTREkgaW5zdGVhZCBvZg0KICAgICBNREk/DQog ICAgIElmIHllcywgYW55IGhpbnQgb24gaG93IHRvIGJlZ2luPw0KIA0KUmVncmRzDQpMZW8NCg== #LEO CHIN SIM# wrote: > > Hi, > Just want to check whether is it possible to display multiple images using SDI instead of > MDI? > If yes, any hint on how to begin? > > Regrds > Leo It is possible to display anything using SDI. You can paint multiple images on one view, or you can use splitter windows and display somethin...

Dose the Excel 2003 support the Dual Processor performance?
Currently, the dual processors exist in many new computers. Is that help in Excel computing? or it is nothing......excel can only utilize as equal as one processor. Excel does not use the benefits of dual processors -- Regards, Peo Sjoblom "TT" <anonymous@discussions.microsoft.com> wrote in message news:AE81D2F9-5184-431E-A9B3-409035705F12@microsoft.com... > Currently, the dual processors exist in many new computers. Is that help in Excel computing? or it is nothing......excel can only utilize as equal as one processor. ...

MOBILE ACCESS
Maybe I am missing something. I have a Pocket PC running Windows Mobile 2003 with Messanger. I have tried to configure it multiple times. I am able to download messages in my email account on the initial set up, but when I try to send a message I get the error message"The message(s) could not be sent. Check that you have network coverageand that your account iformation is correct. Then try sending again." I have tried setting it up numerous times. I can access the web at the time I am getting this message..... Try a pocket pc group as this is not Outlook related. mic...

Accessing Requests on Public Folder
Hi All, We have exchange server 2000 in our organization.We have started one series of posting IT knowledge document in one of our public folder.Is there any facility in exchange server that provide us the numbers of hits for that document means can we know howmuch times that document accessed in particular time lets say one week from that public folder.is there any third party tool available that can help us to know the hits on that document by users. please let me know if such things can be done in any ways. -- sachin shah exchange system administrator ashima group,india "Sachi...

Send mail using addresses in Access?
Hi, We have a membership list in Access 2003. We'd like to send an email to all the members that have an email address. From Outlook 2003 I haven't found a way to use the addresses in Access. I tried running a query, exporting it as .csv and importing it into Outlook. This requires a non-trivial amount of cleaning up (I used Notepad) to make all the addresses look good. I could import the addresses into Outlook, but, once there, I could not do anything with them such as put them into a distribution list. Is there a way to get the email addresses in Access attached to emails in O...

recovering messages from MS-O-O-2003 .OST file
Scenario: User fired for various reasons. Deleted Profile from laptop in MS Office Outlook 2003 Deleted (or so they thought) messages on server. I _DO_ have a copy of their .OST file, and want to go into the messages to search for possible "sensitive" subject matter transmitted. With the profile intact, it's easy done by creating Personal Folders, running "offline" and copying .OST file to new Personal Folders. BUT... Is it possible to do, when the Outlook 2003 profile has been deleted? test that tools found from the MSExchange Blog http://hellomate.typepad.com/exch...

Custom Entity Lookup to multiple record types
I have created a custom entity that I would like to setup a M-1 relationship between multiple possible entity types. So, I want my custom entity to be able to be related to one account, contact, or opportunity (for example). And this record should be available on the custom entity form as a lookup (a lookup that accepts multiple record types). Similar to a "customer" field on a service case that allows for a customer or account. How (or really can I) could I do this? This cann't be done, only 1-to-many relations are supported through customization Crm system relations...

Newbie (!) Exchange 2003 push and SPV M5000 Active SYnc 4.2
~sigh~ Pulling my hair out here.... After manu years running Mercury Mail I've decided to giv MSExch a go because I have a new SPV M5000 (QTEC 9000) which supports push email Problem is I can't seem to get the SPV, Router port forwarding or Exchange set up properly and Im going on holiday in 2 weeks to wantto have it working!!! I hate to use the prase "idiot's guide" but... maybe "Big Dummy's" might be better Can anyone point me to a foolproof step by step giude own what I need to do please? The problems at the moment revolve around the Active Synce nt...

Queries in Replicated Database
I have a database that is replicated. It has been working fine. I designed 5 new queries. When I right click and bring up the properties, the option to make the queries replicable is no longer lit. I have not had any problem before adn all other queries are replicable. What have I done that resulted in these new queries not being replicable? Realistically, the other ones shouldn't have been replicated. Replication is only intended for data in tables, nothing else. Your application should be split into a front-end (containing the queries, forms, reports, macros and modules), li...

Year-End Closing Queries
Hello, I read a paper about Year-End Closing Process and mentioned about two queries sentence in the database maintenance process: reindex.sql and recomp.sql. These queries are located in \Microsoft Dynamics\SQL\Util\, but I don't have these queries in my installation. Can you explain me these queries?, Anyone has these queries?. My GP version is Microsoft Dynamics GP 9.0 Thanks. Kind Regards, Alfredo In version 9 and earlier, these scripts (and the folder) were only found on the "server" install. This is the workstation where GP Utilities can be run. Later versions of 10 wi...

Exchange 2003 OWA
I have been trying to allow users to change their password through OWA, changed the registry key so in 'options' they can see the option to change their password, but for some reason I keep getting a 404 error when clicking on it I've also created the IISADMPWD virtual directory, but still nothing Any ideas Thanks ...

Error during setup office2007/ 2003
My office has stopped working and I cannot repair or reinstall the application. It says another installation is in progress but this installation cannot be completed or stopped. This installation has the following message under interactive services: Cant launch Program Path: c:\Windows\system32\MsiExec.exe the system cant find the file specific ...

multiple inbox creation
Hi, I have three different email addresses. I would like the mail to these addresses to be delivered into their own, individual inboxes. Is this possible? At the moment everything arrives in one inbox. It is an Outlook Express account and I'm using Windows XP. Hermione schrieb am 26.05.2005 16:11: Hello, > I have three different email addresses. I would like the mail to these > addresses to be delivered into their own, individual inboxes. Is this > possible? At the moment everything arrives in one inbox. > It is an Outlook Express account and I'm using Windows XP. ...

adding an image to an access databse
how do you add an image, a photo, to a database that i've set up? What version of access are you using? -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Alana" wrote: > how do you add an image, a photo, to a database that i've set up? On Mon, 8 Feb 2010 17:08:01 -0800, Alana <Alana@discussions.microsoft.com> wrote: >how do you add an image, a photo, to a database that i've set up? Where do you wa...

Microsoft Outlook 2003 on Vista
After installing Microsoft Outlook 2003 on our new computer with Vista processor, we receive several emails that produce 20 20 and html language. It pulls up fine on a windows 2000 and Windows XP that we have. We only have problems on the machine that has Vista. Can't read it. Do you have any clues as to what changes we need to make in order to be able to read those emails properly. eu falo portugu�s..... yo ablo espa�ol I'm answer a english "texas" <allcorn@theallcorns.com> escreveu na mensagem news:42445a61-a1cf-4612-a030-a3f435ce8e72@v3g2000hsc.googlegroups.com......

HELP
My customer has emailed a file I need. It's a .msi file (an install program) Outlook has blocked this attachment and I can't seem to get it. Any thoughts ?? Splishman Cancel this request I found the answer here... http://www.slipstick.com/outlook/esecup/getexe.htm It works a treat. Cheers Splishman "Splishman" <splishman@hotmail.com> wrote in message news:42afa71e@dnews.tpgi.com.au... > My customer has emailed a file I need. > It's a .msi file (an install program) > Outlook has blocked this attachment and I can't seem to get it. > > Any...

Ensuring only one commission per product in Access Table
Good afternoon, Please can someone help me. In my Access Database I have a table called tblCommission, with the fields: Commission_ID;Client_account; Product_code; Commission; Exchange (£ or $) I need a method to ensure that each client can have a commission set for every product - but that a client cant have to commission's for one product: eg: I need a method to prevent this: Comm_ID Client_account Product_code Commission Exchange 1 000001 Coke 3 $ 2 000001 Coke ...

Exchange 2003, chat, IM and offline messages #2
Hi As you know, there is no chat, sending instant and offline messages for exchange 2003 users. How can we have these possibilities for our exchange 2003 client. which programs can do these for us? Thanks There was never an Exchange client that did that. The function set has been moved out of Exchange and into Live Communication Server. http://office.microsoft.com/en-us/FX010908711033.aspx -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "greenland" <mygreenland80@hotmail.com> wrote in message news:ekOl6aOuFHA.4080@TK2MSFTNGP12...

Help!!!!!....Too Few Parameters
Hello Everyone/Anyone...first time posting...after having gained valuable tips from others' posts. I didn't know which board (Access/Excel) I should have posted this query (no pun intended) but here goes....This is a step by step account of my pridicament: In Access: Step 1---I have 2 linked PRN files in a database with identical field names. Step 2---I create a single named query dumping all fields from both linked files (via SELECT/UNION statements) with additional field within the query that identifies my records from each linked file. Step 3---Query runs great, I get results...

Outlook 2003 hyperlinks opening to home page
I am having an issue with a Windows XP Pro machine running Outlook 2003.The problem is that when you click on a link inside an email, IE will open, but only go to the homepage. it can be as simple as going to the Yahoo website and it will not go there. it makes no attempt to go to the desired website unless you copy and paste. The issue is resolved 1. Open Windows Explorer 2. Go to Tools > Folder Options... 3. Click the "File Types" tab 4. Find (NONE) URL:Hyper Text Transfer Protocol, click it, and click the "Advanced" button 5. Click the open action an...

Exchange 2003 compatibility with SharePoint 2010??
Is Exchange 2003 compatible with SharePoint Foundation 2010?? Or must we upgrade to Exchange 2010?? Thank you, Tom If you're simply wondering if SharePoint can use Exchange 2003 to send e-mail through, the answer is yes. All SharePoint needs is any old SMTP service to pass mail through. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "tom12010" <tlyczko@gmail.com> wrote in message news:908e218a-6827-4fcb-a65b-2621b60c805c@o3g2000yqb.googlegroups.com... > Is Exchange 2003 compatible with SharePoint Foundation 2010?? > Or...

IIf Function help with my Query
First some background, I have two tables called 2007 Grid and 2008 Grid, in these tables I have a field called Census Rec'd. For my query, I need to create a field called Census Rec'd and I need this field to do the following - If "Plan Year" ends in 2007 from "AFTAP Table" then insert Census Rec'd from the 2007 Grid and/or if "Plan Year" ends in 2008 from AFTAP Table then insert Census Rec'd from the 2008 Grid. Any suggestions? I suggest you change your database design. Having a table for each year is incorrect. You are starting to se...

Lookup based on reference
The other day, I used this formula to lookup table data based on the row and column such as: =INDEX($R$3:$AA$41,MATCH(FLOOR($S$1,0.1),$Q$3:$Q$41,1),MATCH(FLOOR($S$1-FLOOR($S$1,0.1),0.01),$R$2:$AA$2,0)) So that If I had the number 2.22, it will return for me .15 from the table lookup table (made up values) .00 .01 .02 .03 .04 ... .09 2.0 .31 .32 .33 .34 .35 ... .50 2.1 .21 .35 .51 .51 .25 ... .85 2.2 .53 .52 .15 .52 .51 ... .81 2.3 .89 .58 .23 .45 .15 ... .15 My question now is, based on the above formula, how would I be able to do the op...

Any way to view users passwords in server 2003 AD ?
Is there any way to view users passwords in Server 2003 AD ? We run a server 2003 SP2 domain, us Administrators have a list of all users password on a spreadsheet, locked up. We need their passwords in the event we must sign onto their computers as an admin while they are away, we log back into the computer with their user name/password when done. You would be surprised how many people "do not" look at the user name when they log on - we had panic --- I can not log in, we go to their desktop and the user name is that of one of the administrators - user did not look. ...