Parameter Query Question 03-01-10

Why doesn't this work?

In the Criteria field of a QBE grid, I have the following formula, to
pull some parameters off a form. How can I code the "*" condition, if
the chkAll is true?

IIf([Forms]![frmLA_Control_Panel].[chkAll]=True,([tblOnliners].
[Region]) Like "*",[Forms]![frmLA_Control_Panel].[cboRegions])  (this
doen't work)

IIf([Forms]![frmLA_Control_Panel].[chkAll]=True,"*",[Forms]!
[frmLA_Control_Panel].[cboRegions])   (this doesn't work either)


0
Zoomiest
3/1/2010 11:33:47 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
666 Views

Similar Articles

[PageSpeed] 30

"doesn't work" doesn't give us much to go on ... can you be a bit more 
specific?  Do you get an error message?  What does it say?

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"Zoomiest" <zoomiest@gmail.com> wrote in message 
news:919bb740-bde9-4fe2-9415-d67e91153180@b7g2000pro.googlegroups.com...
> Why doesn't this work?
>
> In the Criteria field of a QBE grid, I have the following formula, to
> pull some parameters off a form. How can I code the "*" condition, if
> the chkAll is true?
>
> IIf([Forms]![frmLA_Control_Panel].[chkAll]=True,([tblOnliners].
> [Region]) Like "*",[Forms]![frmLA_Control_Panel].[cboRegions])  (this
> doen't work)
>
> IIf([Forms]![frmLA_Control_Panel].[chkAll]=True,"*",[Forms]!
> [frmLA_Control_Panel].[cboRegions])   (this doesn't work either)
>
> 


0
Jeff
3/1/2010 11:57:01 PM
IIf() is a function returning a value, not a conditional statement; so 
probably that you are trying to write something like:

[tblOnliners].[Region]) Like 
IIf([Forms]![frmLA_Control_Panel].[chkAll]=True,"*",[Forms]![frmLA_Control_Panel].[cboRegions])

This is something close to your second attempt so maybe this is already what 
you have wrote with QBE but I don't know it; so I'm not sure.  A much better 
solution would be to write something like directly in SQL:

WHERE (Forms]![frmLA_Control_Panel].[chkAll]=True)
  OR ([tblOnliners].[Region] = [Forms]![frmLA_Control_Panel].[cboRegions])

You should forget about using the QBE and learn to write your queries 
directly in SQL.  QBE is, at best, a bad tool with which you can only write 
simple expressions and that will quickly mix up your stuff if you trow 
anything at it that is not plain simple.  You'll go nowhere with this (bad) 
tool if you insist to work with  it.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"Zoomiest" <zoomiest@gmail.com> wrote in message 
news:919bb740-bde9-4fe2-9415-d67e91153180@b7g2000pro.googlegroups.com...
> Why doesn't this work?
>
> In the Criteria field of a QBE grid, I have the following formula, to
> pull some parameters off a form. How can I code the "*" condition, if
> the chkAll is true?
>
> IIf([Forms]![frmLA_Control_Panel].[chkAll]=True,([tblOnliners].
> [Region]) Like "*",[Forms]![frmLA_Control_Panel].[cboRegions])  (this
> doen't work)
>
> IIf([Forms]![frmLA_Control_Panel].[chkAll]=True,"*",[Forms]!
> [frmLA_Control_Panel].[cboRegions])   (this doesn't work either)
>
> 


0
Sylvain
3/2/2010 2:51:10 AM
Reply:

Similar Artilces:

Multi Table Queries
At my job, I use enter data through generated queries. However it appears that I can only ENTER data in a query when it is based on two tables. If it is three or more, it doesn't let me enter any info. Is this normal? Am I doing something wrong? -Pete Pete wrote: >At my job, I use enter data through generated queries. However it appears >that I can only ENTER data in a query when it is based on two tables. If it >is three or more, it doesn't let me enter any info. > >Is this normal? Am I doing something wrong? Yes, that's common. You should only edit data ...

microsoft money #10
I tried to open Money on my computer (version 2003) The original page was replaced by Microsoft. How did this happen? How to I get my original files? What "original page"? What makes you say it "was replaced by Microsoft"? Have you tried opening your original files? (Money File|Open or double-click on them from Windows Explorer.) "DR support" <DRsupport@discussions.microsoft.com> wrote in message news:AB8D2DDB-8EA1-4665-A875-D19A468B19AA@microsoft.com... >I tried to open Money on my computer (version 2003) The original page was > replaced by Mi...

SQL Reporting Services and GP 10
Hello: Is it possible to associate SQL Reporting Services reports with SOP invoices in GP 10.0 rather than with Report Writer? I know that Accountable Software has Forms Printer, but I believe that that only works with Crystal. And, I know that there are SRS reports written to display data from GP but I do not think that these reports will work in terms of printing invoices or orders from SOP. Thanks! childofthe1980s No not really.. The problem is that when an SOP or POP document is printed, GP needs to be updated. Accountable does that with their Crystal interface but I dont kno...

IIF stmt in query
I would like to return different results for different conditions more than one field. here is my IIF stmt: IIF([transactiontbl.insttype]="A",”FMYHF0002”,IIF([transactiontbl.insttype]="B","FMYHF0003”,IIF([transactiontbl.insttype]="T",”FMYHF0005”,0))) or IIF([etype]=”R”,”FMYHF0004”,”FMYHF0006”) any help is greatly appreciated. Thanks You can AND the conditions, but it would be preferable to use a table to make the translations. iif( type = "A" AND country = "USA", "FMYHF002", iif( type = "A"...

Report of sales by month based on a cross tab query
Hi, I have a cross tab query that will provide sales by month for 2008. Right now it is sales for January08. As there are sales for future months, they will appear as well. I want to create a table that has all of the months listed out already. Right now, on the first day of the month, I go into the report and add the new month. I want it so that all month are listed which I have done. The problem is that when I run the report, I get an error that says "the Microsoft Jet does not recognize February as a valid field name. After today, there will be February data but no March ...

Macro Question Again
I'v never had any dealings w/ macro before, so this is all new to me. I have a windows 98 computer that is running office 2000 and there is an excel macro on this pc that was created by someone years ago. I have to get this 98 machine of the network and replace it w/ a 2000/XP platform also running either office 2000/xp. So far i'v copied the macro and am able to run it, but its not gathering the correct info. It is collecting the correct info on the 98 machine, just not the one im replacing it w/. It looks like some of the fields are supposed to be populated w/ info in a text d...

"IF" Function question
Hello I am trying to create a spreadsheet to keep track of scores. Each score has a corresponding number of points. For example if the score is between 170 and 174.5 the number of points is 1, if it is between 175 and 179.5 it is 2 and so on up to 200 where it is worth 8 points. I can't seem to figure out how to tell excel that if the score falls between two numbers display this value. I have tried it multiple ways such as =IF(D8>=170<=174.5,1, IF(D8>=175<=179.5,2, IF(D8>=180<=184.5,3, IF(D8>=185<=189.5,4, IF(D8>=190<=194.5,4, IF(D8>=195<=197.5,6, IF(D8...

only the first 5 columns of a 10 column excel spreadsheet sort
How do I get the whole spread sheet to sort? There is a blue lox for the first 5 columns that limits the range of the sort. How do I remove it? Using Office 2003. Maybe if you remove the Data|list Select a cell in that blue box. Data|list|convert to range jrw562 wrote: > > How do I get the whole spread sheet to sort? There is a blue lox for the > first 5 columns that limits the range of the sort. How do I remove it? > Using Office 2003. -- Dave Peterson ...

Licensing Question with Professional Suite
Hey, I've seen some licensing questions out there, but wanted to be clear. Microsoft Reps are giving me different information, but admit they do not know!? I think there needs to be a little more training with the CRM licensing. I need the CRM Professional Suite (sales and service). If I have 5 sales people (that don't really need to get into the Service Stuff), and I have 5 Service People (that don't need to do anything with Leads and other stuff), can I purchase 5 Sales Professional Users and 5 Service Professional Users? Or do I still need to purchase the Suite User...

ODBC Failure In JOIN Query ??? (BUT....)
I have a query that is simply a combination of 3 other queries (joined by 1 common field in each). Each of the 3 "sub queries" will execute fine, Individually.... But....that final query which simply combines them all will display an ODBC failure error. I'd always assumed that if each individual query ran okay....the query which combines them should not get such a data-access related error (since the core data is already being successfully accessed). Has anyone else experienced this and found a solution? Thanks very much -- Message posted via AccessMonster.com http://www.a...

IF question...
I need a formula which will allow me to accept a number if it falls within a certain range. For instance, I want the cell to recognize the number in the cell if it falls between 1 and 10. Otherwise, I want the cell to return a 0 value. Anybody? =IF(AND(A1>=1,A1<=10),what?,0) On Mon, 25 Oct 2004 19:15:06 -0700, "mailrail" <mailrail@discussions.microsoft.com> wrote: >I need a formula which will allow me to accept a number if it falls within a >certain range. For instance, I want the cell to recognize the number in the >cell if it falls between 1 and 10....

Outlook macro question
I have two email accounts in Outlook 2003. I know about the Accounts drop list in new emails. But is it possible to create a few macros so that I end up with two buttons where each runs a macro that starts a new email using a specific account? One button would start a new email using account 1 and the other button would start a new email using account 2. If so, could someone please give me some pointers? Thanks. Outlook doesn't provide any direct way to change the account for an = outgoing message in versions before Outlook 2003. See=20 http://www.outlookcode.com/codedetail.aspx?id=3D88...

Outlook Integration 09-01-06
Hi Guys, I'm new to this newsgroup so appologies if this question has already been asked. I have a client that has 4 remote members of staff. They all currently use Outlook 2003 with Business Contact Manager. My client intends to purchase a server and use ACT! and allow her staff to VPN in to acces the various contacts. I've suggested Windows SBS 2003 Premium for the server and a colleague has suggested CRM instead of ACT!. Her biggets nightmare is converting the BCM data to ACT! format. Can CRM import her current 3000 contacts already stored in BCM ? Are any of you using CRM o...

LINQ related query
Hi I have a question regarding joining xml files on a key which is present in all file, following is my code docFTR is of type XDocument xdList is List(Of XDocument) Dim doc = From x In docFTR.Descendants("Data") From i In xdList.GetRange(0, 1) Join d In xdList(0).Descendants("Data") On x.Descendants("Guid").Value Equals d.Descendants("Guid").Value i need to programmatically replace xdList(0) with the current XDcoument which comes from "From i in xdList.getRange(0,2)" GetRange(0, could be 2 to 10) Any help is welcome...

Stack overflow question
Occassionally when on Facebookwith MSIE if I have 2 instance of Facebook open (Different pages) I will get an error message of "Stack Overflow at Line: 31". When I click OK the page finishes loading fine. Any suggestions as to what is going on? Running: <<< System Summary >>> > Manufacturer : eMachines Inc. > Mainboard : eMachines WMCP61M > Chipset : nVidia GeForce 6100V > Processor : AMD Athlon Neo MV 26 @ 1600 MHz > Physical Memory : 4096 MB (2 x 2048 DDR2-SDRAM ) > Video Card : NVIDIA GeForce 6150SE nForce 430 ...

Questions about Migrating from Outlook Express to Outlook
I'm thinking about migrating from OE 6 to Outlook 2003 and was wondering if I will lose my folder structures, rules, and address book by doing so. I also use Yahoo POPs for importing my Yahoo e-mails into OE. Will I need to reconfigure the Yahoo POPs to work with Outlook after the migration? Thanks in advance for your help! Spudy Not sure about the YahooPOPs thing but using Outlook's File menu > Import & Export command should get most of the data. You can also try exporting from OE. "Spudy" <spudy@spamless.com> wrote in message news:th1hd1ddvnto8he4fsv9mluf...

Easy question on Excel...
Hello I have an Excel workbook open which has 5 worksheets. How do I toggle through them manually without using the mouse to click a sheet? I am sure I used to know how but now I forget! Regards Alex Try Ctrl + PageUp Ctrl + PageDown Regard -- Message posted from http://www.ExcelForum.com Well done. Thanks. >-----Original Message----- >Try > >Ctrl + PageUp > >Ctrl + PageDown > >Regards > > >--- >Message posted from http://www.ExcelForum.com/ > >. > Ctrl + Page Up or Page Dow -- Message posted from http://www.ExcelForum.com Hi I th...

GP 10.0 Fails to install .Net Framework 2.0 not installed
I'm trying to install GP 10.0 on server. We have .net framework 2.0 sp1 and 3.0 installed. We have windows sharepoint services installed on framework 3.0. When we try to install, it is returing error .Net framework 2.0 not installed and ending prematurely. I've tried the workaround provided in Partnersource (KB ID 947538) for .Net 2.0 sp1. However it is not working. Thanks Jack - unfortunately, unless you write out the command line scripts exactly as they say in the KB, it will continue to bomb. Microsoft is costing us hours and hours worth of unbillable time to work aroun...

Excel template and access questions
We are going to use the Excel purhase order templates. We need to save each order in order to refer back to it. Is there a way to do this without saving each individual file (order)? Can we save in some sort of easy to locate "batch"? Also, is there a way to transfer a template that we have done (and all the data realted to it) into a Acess database withouthaveing to so much tweaking? If you have Access, I would suggest creating your purchase order database in Access and then having a mail merge push this information into an Excel template as needed, rather than going the othe...

Exchange 5.5 EDB questions
Hello, I have an exchange 5.5 server, which is running on Windows 2000 server. I have set it up so that I can try to recover some data from a stray pub.edb file. I have tried using the edbutil/eseutil to try and recover the database and change the headers, but I cannot find a definitive answer to the following question. 'How do I import a foreign PUB.EDB folder into my exchange database, and allow users to access it as a folder (Public)?' Thanks in advance, James Copp-Taylor You do not import .edb into Exchange database. You perform disaster recovery, export data to .pst, then imp...

Button Control Event: question
I have a small dialog that prints data it queries from a server to a listbox when a user presses a start button. What I'd like to do is to have the data continually print after the button is pressed until the user presses a stop button. So, I guess I would need to initiate some kind of continuous loop for the data querying/printing, but I'm unsure how to get the stop button to break the loop. If this even the best solution. Does anyone have any ideas of how I could accomplish this, or perhaps a better design idea? Thanks, Marcus Marcus wrote: > I have a small dialog that prints d...

Question on Returns to Vendor without PO & Receipt number
Our company switched to Great Plains Dynamics from Simply Accounting 3 months ago. We currently need to return some of the inventory items to our vendors. However, when we try to enter a transaction entry in Purchasing/Returns Transaction Entry, in the detail lines we are asked for the PO number or the receipt number. We are not allowed to go further or save without the PO number or receipt number. When we converted from Simply Accounting, all we entered was the beginning balance of the vendors and only the PO's which we expect to receive. The older PO's were not carried over. W...

Query Not Working 06-11-07
Hello, I have set up a query for 1 table. The field I'm trying to run the query on is a text field. I enter a name (="smith, john") I get zero results. However I have confirmed that this user exists in the table. Also (if this helps), this field gets populated via a combo box on a form. The table being used for the lookup contains a "user" field which is also a text field Thanks. sometimes this is caused by a leading blank in the field. It displays very narrow. one solution sname: TRIM([field]) "CT" <ctroy01@hotmail.com> wrote in message ...

Microsoft Query #4
I receive message SQL0104 when changing a column heading in Microsoft Query while using the Client Access ODBC driver to connet to an iSeries 400 server. I have been informed by IBM that this is a problem with Microsoft's Query in that it incorrectly quotes the new Column Heading name before sending it to the ODBC Driver . Is anyone aware of a update or solution? Thanks Nick Thanks everyone, I just located the following Microsoft Knowledge Base Article 298955, I followed the procedures below, and the SQL0104 error went away. 1. Quit all Office programs.. 2. Click Start, and ...

Combo values from query based on form fields
I am setting the values for a combo box in a form(s) via a query that 'filters' the results with criteria based upon the values of other fields on the form. The combo is a field that is bound. However, this is giving all kinds of problems ranging from Access completely crashing to being asked for the parameter values of those criteria fields when closing the form. I have tried making the combo an unbound field and then setting the value of the bound field to that unbound field after update, but that still leads to the same issues. How can I do this? As example - I have a form w...