user parameter - user to enter one or more values

Hi.
I want to create a query that will alow a user to search only one drug or
more drugs depending on user's needs. For instance, a user can see activity
for only asprin or he can see activity for asprin and zantac.

select drug, username, date
from drugactivity
where drug = @drug or drug = @drug or drug = @drug or drug = @drug;

Is there an easier way to do this?

Thanks in advance.
Misty

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1

0
Mitchell_Collen
10/15/2007 6:42:56 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
504 Views

Similar Articles

[PageSpeed] 8

It all starts with the data ...

Please provide more specific information about what the underlying data 
looks like.

For example, are you trying to look in a single field?  If so, what data 
type is it?  What are some examples of what data is contained in that field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Mitchell_Collen" <u33726@uwe> wrote in message news:79bbbd3dfbd03@uwe...
> Hi.
> I want to create a query that will alow a user to search only one drug or
> more drugs depending on user's needs. For instance, a user can see 
> activity
> for only asprin or he can see activity for asprin and zantac.
>
> select drug, username, date
> from drugactivity
> where drug = @drug or drug = @drug or drug = @drug or drug = @drug;
>
> Is there an easier way to do this?
>
> Thanks in advance.
> Misty
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1
> 


0
Jeff
10/15/2007 7:11:15 PM
Add the values in a table, as primary key of that table,  and use an inner 
join:

SELECT...
FROM originalTable INNER JOIN tempTable
    ON originalTable.drug = tempTable.drug


Hoping it may help,
Vanderghast, Access MVP


"Mitchell_Collen" <u33726@uwe> wrote in message news:79bbbd3dfbd03@uwe...
> Hi.
> I want to create a query that will alow a user to search only one drug or
> more drugs depending on user's needs. For instance, a user can see 
> activity
> for only asprin or he can see activity for asprin and zantac.
>
> select drug, username, date
> from drugactivity
> where drug = @drug or drug = @drug or drug = @drug or drug = @drug;
>
> Is there an easier way to do this?
>
> Thanks in advance.
> Misty
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1
> 


0
Michel
10/15/2007 7:24:58 PM
I have 1800+ different drugs that can be ordered and referenced in the table
drugactivity.

user Mike
may want to find all records with asprin only.

user Mary 
may want to final all records of drugs asprin, ibuprofen, zantac, and pepcid.

The data already exists in the drugactivity table. It's just I want the query
to dynamically allow the user to search on one value or choose to search on
more than one value. I was thinking the where clause can contain something
like where drug = @drug and drug = @drug maybe I can put a for count list. I
just don't know how to create it using the syntax available.

Thanks, Misty



Mitchell_Collen wrote:
>Hi.
>I want to create a query that will alow a user to search only one drug or
>more drugs depending on user's needs. For instance, a user can see activity
>for only asprin or he can see activity for asprin and zantac.
>
>select drug, username, date
>from drugactivity
>where drug = @drug or drug = @drug or drug = @drug or drug = @drug;
>
>Is there an easier way to do this?
>
>Thanks in advance.
>Misty

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1

0
Mitchell_Collen
10/15/2007 7:47:36 PM
User Mike fills his temp table (on his front end db) with one record, with 
the value aspirin.  Use Mary fills her temp table (on her front end db) with 
four records, each with values aspirin, ibuprofen, zantac, and pepcid. Each 
user uses the same query:

SELECT drugs.*
FROM drugs INNER JOIN temp ON drugs.drug=temp.drug


An inner join, in such case, IS like a WHERE clause based on an IN( list ), 
except that the inner join allows what you want, while an IN( list ) does 
not allow it as efficiently, or as easily, or as fully manageable.


When I say that the user fills the table, in fact, your application does it 
for him/her.  See http://www.mvps.org/access/reports/rpt0005.htm for an 
example.



Hoping it may help,
Vanderghast, Access MVP




"Mitchell_Collen via AccessMonster.com" <u33726@uwe> wrote in message 
news:79bde7009fb16@uwe...
>I have 1800+ different drugs that can be ordered and referenced in the 
>table
> drugactivity.
>
> user Mike
> may want to find all records with asprin only.
>
> user Mary
> may want to final all records of drugs asprin, ibuprofen, zantac, and 
> pepcid.
>
> The data already exists in the drugactivity table. It's just I want the 
> query
> to dynamically allow the user to search on one value or choose to search 
> on
> more than one value. I was thinking the where clause can contain something
> like where drug = @drug and drug = @drug maybe I can put a for count list. 
> I
> just don't know how to create it using the syntax available.
>
> Thanks, Misty
>
>
>
> Mitchell_Collen wrote:
>>Hi.
>>I want to create a query that will alow a user to search only one drug or
>>more drugs depending on user's needs. For instance, a user can see 
>>activity
>>for only asprin or he can see activity for asprin and zantac.
>>
>>select drug, username, date
>>from drugactivity
>>where drug = @drug or drug = @drug or drug = @drug or drug = @drug;
>>
>>Is there an easier way to do this?
>>
>>Thanks in advance.
>>Misty
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1
> 


0
Michel
10/15/2007 8:55:18 PM
Thanks for clarification. I will try this and see how it works.
-Misty

Michel Walsh wrote:
>User Mike fills his temp table (on his front end db) with one record, with 
>the value aspirin.  Use Mary fills her temp table (on her front end db) with 
>four records, each with values aspirin, ibuprofen, zantac, and pepcid. Each 
>user uses the same query:
>
>SELECT drugs.*
>FROM drugs INNER JOIN temp ON drugs.drug=temp.drug
>
>An inner join, in such case, IS like a WHERE clause based on an IN( list ), 
>except that the inner join allows what you want, while an IN( list ) does 
>not allow it as efficiently, or as easily, or as fully manageable.
>
>When I say that the user fills the table, in fact, your application does it 
>for him/her.  See http://www.mvps.org/access/reports/rpt0005.htm for an 
>example.
>
>Hoping it may help,
>Vanderghast, Access MVP
>
>>I have 1800+ different drugs that can be ordered and referenced in the 
>>table
>[quoted text clipped - 32 lines]
>>>Thanks in advance.
>>>Misty

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1

0
Mitchell_Collen
10/16/2007 1:26:06 PM
Reply:

Similar Artilces:

convert the functions and formulas to values
Hi How to convert all the functions and formulas in the whole workbook to values without the method (copy, paste special as value) Thanks Why? Look at Value in the help for VBA Range("A1").Value = Range("A1").Value -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Mireille Abi Nader" <mireillea@newhorizons.com.lb> wrote in message news:7D82E49E-8709-4930-B8D6-7ED311A1F8C9@microsoft.com... > Hi, > How to convert all the functions and formulas in the whole workbook to values without the method (copy, paste ...

100% Stacked Bar chart, two bars, want percent as well as real value
I am attempting to chart the following: Series 1 - Values over the prior 30 days Series 2 - Values over the prior 12 months I would like to show both data series as 100% bar charts, so users of the information can compare percentage of the last 30 days with percentage of the same piece of data over the last 12 months. So, for example the two data series might look like this: Series 1 20 25 45 65 Series 2 211 324 403 822 I want to display both the actual value, and the percentage of the total, for each series, in the labels. For some reason, showing percentage is not an option. Why is th...

Show which cell has MAX, MIN values?
At the bottom of a couple thousand rows of data, I have =MAX and =MIN formulas. Is there some way I could make the cells beneath my MAX and MIN formulas show me the address of which cell has the displayed MAX or MIN value? At least the row number? Ed Ed, To return the row =MATCH(cell with Max or Min value,range starting in row 1,false) or to return the address, say, in Cell N3000, for a value given in N2999 =ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1)) or to return other matching information, like a name in column A =INDEX(A:A,MATCH(N2999,N1:NN2998,FALSE)) HTH, Bernie MS E...

two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I download transactions into Microsoft Money for either account, all the transactions go to only one of the accounts set up in Money. I was prompted for the first download to our "main" account. Then when I went to download for the other account, I wasn't prompted where to download to... and all the transactions went into the first account. Did that make sense? My bank required we switch to Money from Quicken, so I am new to this. Tried to search in users guide and online for help. Help would be much appreciated - ...

Entering Data Into Text Boxes and Worksheet Protection
I'm wondering if there's a way to enter data into a text box once a worksheet has been protected. Currently, once the sheet is protected, there's no way to enter data into a text box. Any thoughts appreciated. Thanks. Stephan There are two different textboxes--one from the drawing toolbar and one from the control toolbox toolbar. But you can rightclick on each and choose: format control (or Format CheckBox There's a tab call protection. Each has a Lock option. (the drawing toolbar has Locked and Lock Text) After I unchecked each of them, I could use them (when I protect...

Most logical value
I would like to know if there is a method of determining "the most logical" value in a table. These are some data that I put in the table below. These numbers are obtained from some dedicated software to compute the influence of an investment on some projects. I would like to fill in the gaps myself. I can add the data from column 10 en 20 and divide it by 2 to compute the value for column 15, but is there a better method? investment year 0 5 10 15 20 25 30 2005 72,8 72,8 72,8 72,8 72,8 72,8 72,8 2006 79,4 76,7 71,5 60,9 50,3 2007 92,3 87,4 76,9 55,6 33,5 2008 108,6 102,3 8...

Error when updateing users info
Hi On installation upgraded from 1.2 to 3.0, I logged as CRM administrator and what to update my e-mail or any other user information and received this error. "The user authentication passed to the platform is not valid." Error code 0x80040204 I checked that all users including me are in UserGroup in AD. Any suggestions ? TIA Shai ...

Want use results of Drop down list to look up a value
I am using Excel 2002 and I have a multisheet workbook and on the first sheet I have created a series of 6 drop down lists pulling from predefined named ranges. I also have limited subsequent choices based on what is selected in the other lists. The end result is the user will be selecting parameters that identify a product and now I am stuck with an easy way to look up the associated part number based on the parameters they selected. Essentially, once they have chosen the parameters in the list, I would like to create a macro that uses those values to locate the associated part...

Migrating Mailboxes from one domain to another domain
I am having a hard time trying to figure out how to do something and was wondering if anyone could point me in the right direction. I need to migrate user mailboxes from one server to another, easy enough right... well here is where my issues are. server one is in say abc.local domain server two is in say xyz.local domain there are the same users in both domains, with the same naming convention. so to clear that up if i have a user called ttest in domain abc, user ttest is also in xyz. the old administration had seperated these domains due to function and security, but now they want to...

Ten calendar harm one of the products
Ten calendar harm one of the products www.laptop-battery-inc.co.uk/spy-pen.htm ...

M2005 -- one word.... UGGGHHH!!!
I just got the demo... was using M04 small biz. Very disappointed. 1) Runs like a SLOTH in need of a NAP, on my 3.4GHz P4 / 1GB ram. 2) MSN crap littered in every nook and cranny possible. 3) Apparently uses IE engine to "render" all pages so everything has a clunky "web-browsery" feel to it. 4) No "Find" function?? How do I just "Find" a transaction across all accounts??? Hello????? 5) Money Express has gone by the wayside... apparently they want you to use MSN Alerts. Well, I don't have MSN Messenger installed on my machine because I...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Changing info from one worksheet to the next
Hi all. I have linked my 55 sheets to one which is great. What i need to know now if possible. Each sheet has the same question over 11 columns Each row is dated and a numeric number from 1 - 10 in each row Now on the master sheet where everything is linked, is there a way that If i changed the date on the master sheet it would reflect the answers from the row with that date? At present the answers showing is for 01/03/10, but i would like to look at the totals for 08/03/10 and show the answers from each sheet for that date. I could have a sheet for each week, but im hoping there i...

Grant permission on users mailboxes to Manager
I have several users that I would like to be able to grant Full-mailbox permission to their branch managers. Preferably this could be done via a script as I have several different branch managers that would like to be able to access all of their employees e-mail when an employee is away on vacation or leaves of absence. -- Charles Miller Systems Engineer Harrington Industrial Plastics I'm not so sure about easily scripting as this would require a 2nd mailbox to be added to outlook as well as 'full mailbox access' permission granted to the user account....maybe a help desk l...

Zero Values
I have a simple formula that inserts a value in a cell from one spreadsheet into the cell of another spreadsheet. If the source cell doesn't have a value, the destination cell has a zero in the cell. I would like to display a blank if the source cell doesn't contain a value - any ideas? Paul C. Peterson =if(a1 = "", "", a1) -- HTH... Jim Thomlinson "Paul Peterson - Velox Consulting, LLC" wrote: > I have a simple formula that inserts a value in a cell from one spreadsheet > into the cell of another spreadsheet. If the sou...

Creating User IDs in GP 10
Hi all, Can a user other than sa create user ids if he/she has been assigned the specific security task? I have created a user id and a Security Role specifically to create user ids. I have assigned the Task : ADMIN_SYSTEM_001* But this Security Admin user id I created is still not able to create other users. The 'Save' button is disabled. If only 'sa' can create users, what is the point of having that Security Task? Thanks in advance. Saras Saras, I'm pretty sure you have to be logged in as "sa" in order to setup users. The "sa&qu...

Default numeric value
I am creating a spreadsheet dealing with dollars. There are several formulas that I have created using percentages. One particular formula multiplies a percent in one cell by the $ value in another. If I have no $ value in the cell the formula uses a '1' instead of what I would expect '0'. How can I set up my sheet so that if a cell is blank the default value will be '0'? Would help to see your formula, but: =if(isblank(a1),"",a1*b1) ************ Anne Troy VBA Project Manager www.OfficeArticles.com "thomson" <thomson@discussions.microsof...

Find certain cell value
Hi, I have a matrix with on the rows dates of stock prices and on th columns stock names. Now I'm trying to find a certain stock price on a certain date. If try to find the stock price of Microsoft on January 1st 2005, how woul this be done. I know how to lookup the stock name and the stock date (using VLOOKU etc.) but not how to combine these lookup functions in order to receiv only one cell value. Tnanks in advance. Ro -- Rob ----------------------------------------------------------------------- RobJ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2...

Find Future Value of a Savings Account
I am trying to set up a spreadsheet that determines the future value of a savings account. Take a look at the FV function in XL Help. In article <EC8EEB72-AF22-47D8-A266-839CA9356725@microsoft.com>, "gingerjane" <gingerjane@discussions.microsoft.com> wrote: > I am trying to set up a spreadsheet that determines the future value of a > savings account. ...

RMS should allow you to choose more than one item at a time
RMS should allow you to choose more than one item at a time, this would be helpful in creating PO's. Mutiple item selection is a standard in every software; you really dropped the ball with this one. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

That darned "USER"
I am an oldfashioned computer user, who usually decides and usually knows where EVERYTHING is on my computer. MS Explorer in Windows is my main helper in keeping things as I wish. However, of late, more and more information is placed by applications (and some other precesses) into various directories deeply burried in a very looooong hierarchy starting with the directory "USER." To make things worse, there may be more than one of these USERS. I would like to undo this practice and get back to putting things where I want them to reside. There is no one else whom I would have...

Entourage: Calendar User Name Incorrect-Correct elsewhere.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Hi There, <br><br>It appears that whenever office was installed, the users name was misspelled. Now when he goes to setup a meeting, his own user name is misspelled. I've verified his own contact is set as default for himself. I've also deleted the office.pd and rerun the setup assistant and verified his name. <br><br>Is there a way to change that name? <br><br>Jaime On 2010-03-10 15:01:16 -0500, jaime_sf@officeformac.com said: > Hi Th...

incrementation of a value depending on another value
Is there a solution to increment a value in a cell everytime I introduce another value in another cell. For instance in A1 i intriduce a srting and I want that in A2 the result will be the incremetation of a value, let'say one(1), then if I introduce the same srting in A1 the value in A2 will be two(2) and so on.. Thanks for your time and effort ..I realy appreciated. You can use an event macro to do this. Right click on the worksheet tab that should have this behavior. Then click View Code. Paste this in: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) ...

IIS reset for one user only
Hello, I have one user who can't link appointments from Outlook and also at the same time the user can't create appointments from CRM Web client as well. It will hang outlook and IExplorer. If I check task manager is starts Offlb.exe, meaning outlook is hung. If I reset IIS on crm server, the user is ok, he can create appointments and link them, the web client is ok as well. When this is happening none of the other users who create appointments and linked them to CRM are affected. After a day or so goes by, the same user have same issues. The only thing that fixes it is the II...

Need help changing cell value when another value changes
I have this code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = ("$D$5") Then If Range("$Q$9").Value <> "CA" Then Range("$N$32").Value = "Out of State" Else: Range("$N$32").Value = "In-State" End If End If End Sub I want the value in cell N32 to change based on what I have in cell Q when I enter data into cell D5 and press enter. It only works after I click on cell D5 (after changing d5 and pressin enter) What am i missing -- Message posted from http://www.ExcelForum.com You ...