Quick Query Problem

PARAMETERS [Forms]![Main Form]![cmbArea] Text ( 255 );
SELECT InspectionsDI.ID,InspectionsDI.Area
FROM InspectionsDI
WHERE InspectionsDI.Area=Forms![Main Form]!cmbArea;

This code works correctly for each individual area but I also want to allow 
all the records to show if Forms![Main Form]!cmbArea = "ALL"

I am not sure how to do this, I tried If statement but my syntax must have 
been wrong, thanks for help.
0
Utf
2/3/2010 10:51:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
793 Views

Similar Articles

[PageSpeed] 41

Try this --
PARAMETERS [Forms]![Main Form]![cmbArea] Text ( 255 );
SELECT InspectionsDI.ID,InspectionsDI.Area
FROM InspectionsDI
WHERE InspectionsDI.Area Like IIF(Forms![Main Form]!cmbArea = "All", "*", 
Forms![Main Form]!cmbArea);

-- 
Build a little, test a little.


"dannie" wrote:

> PARAMETERS [Forms]![Main Form]![cmbArea] Text ( 255 );
> SELECT InspectionsDI.ID,InspectionsDI.Area
> FROM InspectionsDI
> WHERE InspectionsDI.Area=Forms![Main Form]!cmbArea;
> 
> This code works correctly for each individual area but I also want to allow 
> all the records to show if Forms![Main Form]!cmbArea = "ALL"
> 
> I am not sure how to do this, I tried If statement but my syntax must have 
> been wrong, thanks for help.
0
Utf
2/3/2010 11:18:01 PM
PARAMETERS [Forms]![Main Form]![cmbArea] Text ( 255 );
SELECT InspectionsDI.ID,InspectionsDI.Area
FROM InspectionsDI
WHERE InspectionsDI.Area=Forms![Main Form]!cmbArea OR 
Forms![Main Form]!cmbArea="ALL";

-- 
Duane Hookom
Microsoft Access MVP


"dannie" wrote:

> PARAMETERS [Forms]![Main Form]![cmbArea] Text ( 255 );
> SELECT InspectionsDI.ID,InspectionsDI.Area
> FROM InspectionsDI
> WHERE InspectionsDI.Area=Forms![Main Form]!cmbArea;
> 
> This code works correctly for each individual area but I also want to allow 
> all the records to show if Forms![Main Form]!cmbArea = "ALL"
> 
> I am not sure how to do this, I tried If statement but my syntax must have 
> been wrong, thanks for help.
0
Utf
2/3/2010 11:28:02 PM
On Wed, 3 Feb 2010 14:51:01 -0800, dannie <dannie@discussions.microsoft.com>
wrote:

>This code works correctly for each individual area but I also want to allow 
>all the records to show if Forms![Main Form]!cmbArea = "ALL"
>
>I am not sure how to do this, I tried If statement but my syntax must have 
>been wrong, thanks for help.

PARAMETERS [Forms]![Main Form]![cmbArea] Text ( 255 );
SELECT InspectionsDI.ID,InspectionsDI.Area
FROM InspectionsDI
WHERE InspectionsDI.Area=Forms![Main Form]!cmbArea
OR Forms![Main Form]!cmbArea = "All";

-- 

             John W. Vinson [MVP]
0
John
2/3/2010 11:52:48 PM
Reply:

Similar Artilces:

OWA authentication problem #5
When trying to access mail boxes in my organization trough OWA, I get the login screen; but it doesn't allow me to access them after entering the login information. It seems it is not authenticating. Any ideas? Manolo On Tue, 17 Oct 2006 08:59:01 -0700, Manolo <Manolo@discussions.microsoft.com> wrote: >When trying to access mail boxes in my organization trough OWA, I get the >login screen; but it doesn't allow me to access them after entering the login >information. It seems it is not authenticating. >Any ideas? >Manolo Front End / Back End? Integrated Auth?...

entering parameter criteria MS Query
Hope someone can help. I’m trying to extract data from a SQL Server 2005 into Excel, using a parameter criteria in MS Query. If I use criteria in a field with an integer data type it works fine. If I use a field with an NVARCHAR data type (which is the one I want) I get a “Syntax Error” message. There seems to be no way of entering criteria into this type of field which MS Query will accept. If I enter james it defaults to N’james‘, which works as syntax criteria in SQL Server Management Studio 2005, but not in MS Query. What am I doing wrong?! ...

Installation problems
Hi.. I am new to GP and am having problems with the installation (our IT guy left the company). We use GP 6.0 on our network running Microsft SQL 7.0 and have 3 workstations (running Windows 2000) using it. I am setup to logon and can do so on any of the other workstations, but when I try to install it on my PC (I use Windows XP Pro)... I am stuck... The questions I have is: 1) Do I need to have SQL on my workstation? 2) I followed the instructions on the ODBC setup...but still I am not recognized (nor can anyone else access GP from my PC) Can anyone please walk me through (simp...

Query on a text file..help needed
I am trying to query a large text file and I'm beginning to wonder if it is just impossible to do. I used the excel query wizard but because I am working with a fixed length file I am not coming up with the columns I need. The wizard picks up each row as a solid block of information. Is there a way to pick out certain information from the text file into the query? so if my text line reads: 0110172006johndoe234street excel could pick out 01 10/17/2006 John Doe 234 street I know someone ran a query on my file and put it into the most beautiful excel spread sheet but it just isn't ...

Including all tables into a query
Hi, I'm not sure whether this is the right group. If not please point me to the right one. I have a database containing a table for each month and each table contains information on the articles in the stock. I would now like to make a query which goes across the tables and draws the same number for every table. The tables all contain a product for each row and the properties of it in the columns (eg. type, number on stock, number out, number in, weight,...). All table come from the same source so they all have the same design except that a product might be in one table and not in an ot...

Temp Table Problem
I am getting this following error message randomly (with different table names each time). "An open operation on table 'taxRebate_TRX' failed because the path does not exist." Each table is a temp table, so I can't view it in SQL. It is only happening on one computer. How can I resolve this? Did you try restarting the computer w/ the error? Are all the modules installed on this computer? "Ryan" <Ryan@discussions.microsoft.com> wrote in message news:4B46A24C-2A30-4EE4-8109-C5F94E19F5E0@microsoft.com... >I am getting this following error mes...

default appointment times problems
When i re-open outlook and go to the calender, all the appointments times have changed to 1am on the start day - 1am on the day after it waqs meant to finish. So a 12 day appointment is now shown as over 2 days. How do you change the default appointment time settings so an appointment which is just typed into the day is from 9am - 5pm for example and will stay as a 1 day event when opening and closing outlook? All day events are 12 -12, not 9-5. If you want it 9-5, you need to make it for 9-5. All day appointment change to 1 -1 if you change the time zone or DST settings aft...

Copying certain column information quickly
As you know in Excel you could quickly copy the information you needed with a drag of the mouse. Because I need information copied repeatedly and easily for my access unfirmiliar co workers. Example JOB CUSTOMER QUOTE # A B1 110701 A(copied) A2 110701 (copied) A(copied) C4 110701 (copied) How do I do this quickly and efficiently for the computer challenged. Sorry, Access is a database, not a spreadsheet, and can't be used like one. You can write some code to select the rows that you want to ...

Query
I have changed my default mail program from Microsoft Outlook to Outlook Express but emails are still going into Microsoft. How do I make them go into Outlook Express? Any help much appreciated.... On Aug 19, 12:35=A0pm, "Ron Head" <ronh...@iinet.net.au> wrote: > I have changed my default mail program from Microsoft Outlook to Outlook > Express but emails are still going into Microsoft. =A0How do I make them = go > into Outlook Express? =A0Any help much appreciated.... You would have to set the account up in Outlook Express as well. Changing the default email ...

Search Problems with Outlook 2007
I have been using Outlook 2007 for the last 2 months with no problems except that lately my "search" is not working correctly. I have emails going back to 1999 in the Inbox, and for some reason, when I try to search my old messages (using email address, subject or text within the message), Outlook never finds anything, eventhough I can find them MANUALLY if I scroll up and down. With about 6,000 messages in my Inbox, this is horrible and time-wasting. I have tried changin the search options (this is the little Search Inbox) but to no avail! How can this be fixed that inside ...

excel crashes when changing web query
Hi, In excel 2003, i have a file that pulls in data via Data / Import External Data / New Web Query. I've been using the file for about 3 years, and now when I try to edit the query to pull in the latest table on the same website, excel crashes. And it does the same on another pc running excel 2003. However, I can successfully import data using the same method from other websites. (When I'm editing the query, I can the IE Script error, but not sure if that's related.) I would be grateful for any suggestions. Thanks, Graeme ...

Excel Data Connections to Access Queries: Limits?
I have an Access 2007 database with data connections to: + Sharepoint Lists + SMS SQL tables/views I've written Access queries that use tables from those data connections. I want to use Excel to connect to those queries in the Access database to run various reports (such as charts, lists, etc...) However, I'm having 2 problems: 1) In Excel, when I go to Data / Get External Data / From Access, I cannot see all of the queries listed in the Access Database. 2) The queries I do see only return a small subset of records. (I've tested the query within Access, and I should get 17...

Quick help with excel formula
Ok, i need a forumla. this is what I want, i have two columns like this Time Start: ............. Time Finish: ............ I fill out these two columns like this Time Start: ...17:42... Time Finish: ...18:25.. Now i have another Column that says Training Length: ........ I want this to work out the time between 17:42 and 18:25 Any one help pleas -- Message posted from http://www.ExcelForum.com Hi simply =A2-A1 and format as time If your training time can span midnight use the formula =A2-A1+(A2<A1) >-----Original Message----- >Ok, i need a forumla. > >this is what ...

incompatibility problems
I had Money 2001 UK version and now have 2004 US version. I know that I have to open my file in 2001 UK and export the data...the problem is I no longer have the 2001 UK version as it was lost in the move. Does anyone know a way around or know where I can get a temporary copy to perform this task? BTW, I didn't have this problem going from Money 1999 US to 2001 UK. Why are there problems now? Also, why can MS program conversions for Quicken and not their own software? Best idea would be to download one of the UK trial versions, install it, export your accounts and then uninst...

Query Speed
Hi there! I am setting up a database for our branch offices to use in a distributed (FE/BE) format, and am wondering which would be better for speed: 1. A distinct table for each branch, with a UNION ALL query forming the basis for regional/countrywide reports. 2. One massive data table that queries would launch SQL against. Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1 One table - please! Union queries are slow and inefficient. Union All is somewhat faster but there's always a chance of dupli...

Problem with GetShortPathName()?
Hi I am having problem using GetShortPathName() on Windows 98. The function returns garbage for some file names. Same code when run on Win 2000 gave correct results. Have a look at the code: CString pPath = "C:\\Documents and Settings\\Davinder\\Recent\\"; CString path = "C:\\Documents and Settings\\Davinder\\Recent\\*.*"; CString cPath; WIN32_FIND_DATA FileData; HANDLE hFind; CString csTempPath; hFind = FindFirstFile(path,&FileData); if(hFind != INVALID_HANDLE_VALUE) { do { if(!(FileData.dwFileAttributes & FILE_ATTRIBUTE_DIRECTORY)) { c...

Updating table from a report query
I'm tracking production gate for several departments. I want to set one of three switch in a tblGoals for a specific departmetn based on a calculated value in a report query. The thresholds are .10, .50 and .75 there are also three cutoff dates in a table called tblGateDates. I pass the NRS (department), NCOPercent (the production goal), EndDate (gate cutoff date), and the Gate (1, 2 or 3) which is the gate I'm trying to determine the depart made or missed to a function. Something not working right because near as I can tell based on message boxes I've placed in the code ...

Quick Question
I have been using Office 2007 and OneNote for some time now and have recently set up the 2010 Beta on a second system. In the 2007 version there was a way to repair office if things went flaqky. How do I do that in Office 2010? Michael On Tue, 8 Dec 2009 23:59:26 +0000 (UTC), Rev. Michael L. Burns <sdbpastor@charter.bet> wrote: > >I have been using Office 2007 and OneNote for some time now and have recently >set up the 2010 Beta on a second system. In the 2007 version there was a >way to repair office if things went flaqky. How do I do that in Office 2...

exchange 2003 archiving outlook 2003 problem
has anyone had this problem yet? i am performing an archive and then when the archive is complete the old archived messages are still online in the inbox?? how the heck. i tried it for multiple accounts and multiple computers, still the same problem. i don't understand what might be causing this. archiving to a pst... -- nick Had same problem with user. But when the user was an administrator. Try that. Jeff "Nicholas Oneil" wrote: > has anyone had this problem yet? > > i am performing an archive and then when the archive is complete the old > archived...

Is there a quick way to...
highlighting only the odd or even numbered rows? Thanks. Something like this should do: Sub banding() ActiveSheet.Range("A65536").Select Selection.End(xlUp).Select lastrow = ActiveCell.Row Range("A6:AK" & lastrow).Select Selection.Interior.ColorIndex = xlNone 'Change rownum to the the first row number where data occurs rownum = 6 For i = 1 To (lastrow - rownum) Step 2 Range("B" & rownum + i & ":AK" & rownum + i).Select With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With ...

Problem replying to emails...
Outlook 2003 Sp2. ISP: f2s Never had a problem up until 2 days ago. Whenever I try to reply to an email from an AOL user it bounces straight back. If I email any AOL user direct (including the ones that bounce) it goes straight through ok. This is the reply I get in the bounce back... Reporting-MTA: dns; outmail.freedom2surf.net Received-From-MTA: DNS; i-83-67-124-50.freedom2surf.net Arrival-Date: Tue, 14 Mar 2006 20:32:45 GMT Final-Recipient: RFC822; ("the persons emil address is here") Action: failed Status: 5.0.0 Remote-MTA: DNS; mailin-03.mx.aol.com Diagnostic-Code: SMTP...

Formatting and Macro Problem
I have an Excel File with ~7000 unique account numbers. My problem is I am trying to summarize the data in a separate worksheet and deliver to Senior Management and it needs to be formatted as I have indicated in the attached spreadsheet. I was trying to write a Macro that would do this for me, but am having no luck. For each account number found, I would need to create a table and fill the information into the appropriate columns. Once the information has been populated, the Macro would move to the next account and repeat the entire process thus creating another table and so on. The sec...

Controling queries displayed when clicking Load from Query button?
I want to be able to control the queries that are displayed when the Load from Query toolbar button is displayed. Can anyone tell me how the filter box list gets populated? Thanks. Paco ...

Problem with exchange and OWA Filtering on DSL504
Dear All, I have come up against a problem when i've changed the default SSL port for outlook web access from 443 to 11500. The problem is not that i can't access OWA. That i can do. I changed the SSL port in the default website in IIS (11500) and redirected the 11500 port to point to the exchange server (192.168.0.3)on the router. The problem is i can no longer access the public folders in exchange manager only. I can still access them from any outlook client and owa. If i change the SSL port to 443, no problem access to exchange is fine again. error message as soon as i click on ...

Quick campaign- change to all quick campaigns
Does any one know how to set the following to default. In the quick campaign view, change 'my quick campaigns' to 'all quick campaigns' Cheers Sid ...