Query to pull set criteria or all

I have the following query:
SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682 
Countsheet].Product, [P682 Countsheet].[Assign Date], [P682 
Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682 
Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area
FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682 
Countsheet].Task = [Area to Task Query].[Task ]
WHERE ((([P682 Countsheet].Username)=[enter opid]) AND (([P682 
Countsheet].Status)<>"Completed"));


I would like the query to ask for an OPID as i have set or if no OPID is 
entered I would like it to pull all OPID's 

Is this possible and if so How do i do this?
0
Utf
2/16/2010 7:06:09 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1121 Views

Similar Articles

[PageSpeed] 42

SELECT [P682 Countsheet].Username,
 [P682 Countsheet].[Task],
 [P682 Countsheet].[Product],
 [P682 Countsheet].[Assign Date],
 [P682 Countsheet].[Description of Task],
 [P682 Countsheet].[Due Date],
 [P682 Countsheet].[Status],
 ([Volume]-[Done]) AS Expr1,
 [Area to Task Query].Area
FROM [P682 Countsheet] INNER JOIN [Area to Task Query]
 ON [P682 Countsheet].Task = [Area to Task Query].[Task ]
WHERE [P682 Countsheet].Username 
   LIKE [enter opid or leave blank for all] & "*"
   AND [P682 Countsheet].[Status] <>"Completed" ;

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Chantel33" wrote:

> I have the following query:
> SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682 
> Countsheet].Product, [P682 Countsheet].[Assign Date], [P682 
> Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682 
> Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area
> FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682 
> Countsheet].Task = [Area to Task Query].[Task ]
> WHERE ((([P682 Countsheet].Username)=[enter opid]) AND (([P682 
> Countsheet].Status)<>"Completed"));
> 
> 
> I would like the query to ask for an OPID as i have set or if no OPID is 
> entered I would like it to pull all OPID's 
> 
> Is this possible and if so How do i do this?
0
Utf
2/16/2010 8:02:01 PM
On Tue, 16 Feb 2010 11:06:09 -0800, Chantel33
<Chantel33@discussions.microsoft.com> wrote:

>I would like the query to ask for an OPID as i have set or if no OPID is 
>entered I would like it to pull all OPID's 

One way to do this is to explicitly check for NULL in the parameter:

SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682 
Countsheet].Product, [P682 Countsheet].[Assign Date], [P682 
Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682 
Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area
FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682 
Countsheet].Task = [Area to Task Query].[Task ]
WHERE ((([P682 Countsheet].Username)=[enter opid] OR [enter opid] IS NULL)
 AND (([P682 Countsheet].Status)<>"Completed"));
-- 

             John W. Vinson [MVP]
0
John
2/16/2010 9:33:33 PM
Thanks so much Jerry it work beautifully!

"Jerry Whittle" wrote:

> SELECT [P682 Countsheet].Username,
>  [P682 Countsheet].[Task],
>  [P682 Countsheet].[Product],
>  [P682 Countsheet].[Assign Date],
>  [P682 Countsheet].[Description of Task],
>  [P682 Countsheet].[Due Date],
>  [P682 Countsheet].[Status],
>  ([Volume]-[Done]) AS Expr1,
>  [Area to Task Query].Area
> FROM [P682 Countsheet] INNER JOIN [Area to Task Query]
>  ON [P682 Countsheet].Task = [Area to Task Query].[Task ]
> WHERE [P682 Countsheet].Username 
>    LIKE [enter opid or leave blank for all] & "*"
>    AND [P682 Countsheet].[Status] <>"Completed" ;
> 
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Chantel33" wrote:
> 
> > I have the following query:
> > SELECT [P682 Countsheet].Username, [P682 Countsheet].Task, [P682 
> > Countsheet].Product, [P682 Countsheet].[Assign Date], [P682 
> > Countsheet].[Description of Task], [P682 Countsheet].[Due Date], [P682 
> > Countsheet].Status, ([Volume]-[Done]) AS Expr1, [Area to Task Query].Area
> > FROM [P682 Countsheet] INNER JOIN [Area to Task Query] ON [P682 
> > Countsheet].Task = [Area to Task Query].[Task ]
> > WHERE ((([P682 Countsheet].Username)=[enter opid]) AND (([P682 
> > Countsheet].Status)<>"Completed"));
> > 
> > 
> > I would like the query to ask for an OPID as i have set or if no OPID is 
> > entered I would like it to pull all OPID's 
> > 
> > Is this possible and if so How do i do this?
0
Utf
2/17/2010 12:36:04 PM
Reply:

Similar Artilces:

The correct way to set XSD namespaces?
Hi, I have created a set of schemas for messages that i need to exchange between services. I set the targetNamespace ="http://mycompany/services/abc" and the root elements have names like FindCustomerRequest, FindCustomerResponse, CreateCustomerRequest and so on. In Biztalk when a message of any of the above comes in, it validates correctly because the engine sets the subscription to "http://mycompany/services/abc#FindCustomerRequest" . I also managed to create a webservice with these XSDs. However when creating a proxy i got an error saying that the targetNamespa...

retrieve color quality setting
Who knows how to retrieve the display color quality setting (medium (16 bit), high (24 bit), highest (32 bit)) in VC6? Roland. >Who knows how to retrieve the display color quality setting (medium >(16 bit), high (24 bit), highest (32 bit)) in VC6? Use EnumDisplaySettings ENUM_CURRENT_SETTINGS and the DEVMODE dmBitsPerPel member. Dave If you have a CDC then GetDeviceCaps(BITSPIXEL); AliR. <rreuter@gmx.net> wrote in message news:1174989490.485570.81790@l77g2000hsb.googlegroups.com... > Who knows how to retrieve the display color quality setting (medium > (16 bit), high...

Problem setting SeriesCollections source range
In VB6 sp4, I'm having problems setting the SeriesCollection() range. The end row of the range will be dependent on the number of bins the user declares so will always be variable. I've created a macro in Excel to use as a guideline, but have had little luck with it. The chart will have 4 SeriesCollections one of which should only be visible/used for the XValues. Dimming the strRange1 and strRange2 as Objects and using the Set statment to define them stops compiling at the start of the Sub. Perhaps I'm using the Set statment incorrectly. Dimming the strRange1 and strRan...

Excel Form: General Query Data Management
Hi, A quick question- I have designed a questionnaire in Excel, this is combination of check boxes, drop down list boxes, cells to input whol numbers etc. I have created (in hidden rows) formula to count each o the variables listed in drop down boxes etc). I was under the initia understanding that the form would not be distributed to that man people however now I am advised that this will probably go out to i excess of 250 people. So my question relates to how I can manage th data that I receive back. I was intending to create a template that would take the value calculated in each individu...

Query using a date criteria
I have a query that I have set the criteria by a date field - ">=#12/1/2009#" What I would like to do is have the query run with the same operators but with a date that has been input by the user. I am not sure how to write it for that use. Thanks Eric Set your criteria something like any of these. I think 2 is what you're after. 1. WHERE ([DateField] Between [Enter Begin Date] And [Enter End Date]); OR 2. WHERE [DateField] >= [Enter Begin Date] Eric Starn wrote: >I have a query that I have set the criteria by a date field - ">=#12/1...

Set nonclient area
I want to create a window with custom borders/frame (reducing the size of the normal framewnd borders). To do this I figure I have to specify exactly what the nonclient area size is. How do I set this? I've figured that only overriding OnNcPaint doesn't work since there's still an area that is "undrawn" since it's expecting a certain size nonclient area. So how do I set this nonclient area size? Basically all I want to do is create my own borders instead of the normal frame window borders... and these borders will not be the same size as the normal borders. What mess...

Outlook 2003 - Search folders
Greetings! I'm setting up search folders in Outlook 2003, and here's the criteria I want on one: Received more than 2 weeks ago and no expiry date. I can search for either of the two items separately. There's a prebuilt criteria for mail older than 2 weeks, and I can do an advanced criterion for expiry does not exist. What I can't figure out how to do, though, is add that expiry criteria to the filter I have for mail older than 2 weeks, nor can I figure out the syntax to write an advanced criterion for received (on or) more than 2 weeks ago. I can't be the first person ...

DEP settings change in the Boot.ini
(XP_SP2, SP3) Lets say this is my boot.ini file: [boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(4)\WINDOWS [operating systems] multi(0)disk(0)rdisk(0)partition(4)\WINDOWS="XP Pro" /fastdetect /NoExecute=Optin multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="XP_HOME" /fastdetect /Noexecute=Optin I show the following: From Everest on the CPU: Data Execution Prevention (DEP, NX, EDB) -> (DEP is Supported) ---- CMD: wmic OS Get DataExecutionPrevention_Available output: DataExecutionPrevention_Available output: TRUE ---> (I ha...

Query of uncommon records...
I am having some trouble writing a query where I can match records with similar but exactly the same content. For example I want to watch times with second or five second differences... What is the best way for me to do this? On Mon, 18 Feb 2008 18:44:00 -0800, Hydrogeo <Hydrogeo@discussions.microsoft.com> wrote: >I am having some trouble writing a query where I can match records with >similar but exactly the same content. For example I want to watch times with >second or five second differences... What is the best way for me to do this? Use a "non equi join"...

MSOutlook Queries
MSOutlook Question 1:How can I get to know from which IP adress, I recieved mail. i.e., how to know IP address of mail sender. MSOutlook Question 2: Can I know last login time and last IP address I used last time for my MS Outlook. Please let me know if there are any possibilities for MS Outlook mail. Thank you in Advance, Regards, Satishsngh@gmail.com "MSOutlook Question" <MSOutlookQuestion@discussions.microsoft.com> wrote in message news:72B4746C-80E7-4AE0-8811-B948B1A0C886@microsoft.com... > MSOutlook Question 1:How can I get to know from which IP adress, I >...

Security Alert Setting
Hello, Using Excel 2007 I have a spreadsheet that requires the "Security Alert, Macros And Active X" to be enabled/On. No problems, but a bit annoying to have to set it to On each time. I, of course, do a Save each time I exit the program, but it does not seem to save the Security Alert permission setting. Is there some way to have it set, so I don't have to fiddle with it each time ? Thanks, Bob ...

Offline Folder File Settings
In Outlook 2003 Does anyone know why my file location for Offline Folder File is "grayout"? I can not either browse or set the - Encryption Setting, its also all "grayout" I'm running Exchange 2003 Thanks, Henning ...

How do I set Calendar to permanently retain appointments?
Outlook 1997/2003 removes appointments set in Calendar after about two months. I would like to retain this information. Is there a setting that would allow this? Check your auto-archive settings. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Genemarkey asked: | Outlook 1997/2003 removes appointments set in Calendar after about two | months. I would like to retain this information. Is there...

HTTP Server Settings for MSN Personal Address
Can anyone provide the HTTP Server Settings for MSN Personal Address Services? The account does not have typical "@msn.com" or "@hotmail.com" setting. The $34.95 Subscription for MSN Personal Address has a .NET Passport associated with it and provides a.. Your own Web address*: "www.yourname.com" a.. Personalized e-mail address: "you@yourname.com" a.. URL for this MSN premium Service: http://join.msn.com/?pgmarket=enus&page=hotmail/domains&HL=MSN_Personal_Address&ST=1&xAPID=1983&DI=1402 Regular MSN or Hotmail Server Settings ...

pulling a list of hyperlinks from a worksheet
I have a worksheet with over 800 cells. Each cell displays a churches website url. I need to display the actual hyperlink http://www.whatever.com not the Link name. example First Baptist Church Nashville. I need the hyperlink http://fbcnashville.org. displayed in the next cell. 'Here's a short macro: 'Note that this assumes you are not using the function HYPERLINK, but just an 'embedded hyperlink 'To install, right click on sheet tab, view code, paste the following in. Sub ListHyperlinks() i = 1 For Each h In Me.Hyperlinks 'Generates list in column...

Passing query user input to report body
Hello, I set up a simple query to prompt the user for the date which is used as a criteria. [Enter the start date] is there a way to pass the value entered by the user at the prompt the the report, so I can have in the header 'report from ####/##/## to date()'? Is there a term for what I'm trying to do? I could find anything on it because I don't know what it is I am looking for. Thank you, QB You can refer to the parameter just like a field. Try a text box with Control Source of: =[Enter the start date] -- Allen Browne - Microsoft MVP. Perth, Western Austr...

Calculating Values in a Query
I have 4 fields in a query, Drawing Dim., Tolerance, Upper Limit, and Lower Limit. I am trying to calculate the Upper and Lower Limit fields by taking the Drawing Dim. +/- the Tolerance (this works fine on my form for entering the data). The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. I am pretty sure this is why I am getting a Data Type Mismatch Error in my query, but I cant figure out how to get it to work. Can someone please help me? Thanks!! >>The problem is that the Tolerance is a combo box (dropdown), and th...

Need formula based on criteria.
I have a spreadsheet listed by day as follows. I need the data from column B, C & D summarized by month. How do I do that? Col A Col B Col C Col D 01/01/04 5 5 4 02/01/04 6 6 6 03/03/04 7 0 0 I need the result to look like this - Jan 04 11 11 10 Mar 04 7 0 0 How do I tell it to search for dates between 01/01/04 - 31/01/04 in column A then add column B, C or D to comue up with my totals for Jan 04? Hi one way for Jan Col B use the following formula =SUMPRODUCT(--(MONTH($A$2:$A$4)=1),B$2:B$4) this can t...

Puzzle with query expression
I have the following in a database: MainTable - - fields- phone1, phone2 phone3 phone 4 MainQuery - based on Main table with all four fields Report - all four fields in the query Information stored in each of the field are numbers but I replaced null values in all fields with the text "n/a". I did this so that the report would not show any null values. What I what the report to reflect is this. If a record has a "n/a" entry in all four fields, I would like the report not show that record but show all other records that have valid telephone numbers even if some of...

Creating a customer accounts statement vis query?
Hi, i use access to create invoices for jobs that i do.. currently i have the following...Jobs database that includes a the cost for the job. I print an invoice from this information... The layout is basically the following..job.customerIDjob.descriptionjob.datejob.chargeI also record payments in a separate database called Payments because the payments are sometimes different than the charges...payment.customerIDpayment.datepayment.amountI would love to create a report that uses the data from both of these tables and generates a statement like so...Date Credit Debit1/2/07 �5...

Can the "Show Full Menus" option be set with code?
Does anyone know if you can control the setting for "Show Full Menus" through code? This is a constant complaint from users of my applications, and being that I support thousands of users across several applications its just not feasible to deal with this issue person by person. I was hoping to be able to control this on open of my applications using my hidden startup form that I use to set other Access options. In the VBE editor window. Search the help for 'Set Startup Properties from Visual Basic' Also, I found the following a while back that might get you started. ...

Query will not open in Design Mode
Access 2007, I've created the following query from two tables: SELECT tblTrnsctn.TxnDate, tblTrnsctn.Amount, tblCoa.AcntNmbr, tblCoa.AcntDscriptn, tblCoa.LeadSheet, tblCoa.qbListID FROM tblTrnsctn LEFT JOIN tblCoa ON tblTrnsctn.AccountRefListID = tblCoa.qbListID; This query seems to run fine, but once I save the query, I no longer can open the query in Design Mode to edit - I can view in sql mode. I modified the above query to return records that do not contain any null values in any fields. When I try to view the query in Design Mode - 'Invalid use of Null' error message is ret...

Where are all the tweaks and advanced settings for Win7 MCE
Where are all the tweaks and advanced settings for Win7 MCE? There has got to be a better way to configure WMC than using the clunky on-screen interface? What's the trick? http://www.hack7mc.com/2009/02/theme-media-center-changing-background.html and many more. regards, S.Sengupta[MS-MVP] "G. Morgan" <usenet_abuse@gawab.com> wrote in message news:purqi5tn4p8d4s4ts7hfert107gk97otrm@4ax.com... > Where are all the tweaks and advanced settings for Win7 MCE? > > There has got to be a better way to configure WMC than using the > clunky o...

Parameter Query Problem 12-14-07
I have a large Database with many data tables that link together. If i have a Query via ODBC that accesses just one of these data tables then i can run a parameter query and get the results i want, but if i have multiple tables in the query then the parameter query does not work and i get a blank/empty return? Is there a way that i can solve this? What have you done to trouble-shoot? Have you tried running the multi-table query without a parameter? IMHO, http://www.tek-tips.com/faqs.cfm?fid=6763 -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to...

Dialog + set foreground problem
Hello All First Let me explain what I want to do. I have a modeless dialog which gets displayed each time when the document is completed in browser(Using BHO - Browser Helper Object). As well as I want to display the dialog only when the broswer in in foreground. ( Since there could be many browsers and each browser would have its own dialog. So in order to identify dialog , I am showing the corresponding dialog only when the browser is maximized.) So I set the SetForeground property of dialog to FALSE which tells that dialog will not bring in foreground if parent is not in foreground. It wo...