IIf works in query but not in report

Does the IIf function work differently in queries from the way it works in 
reports?

For example, this works in a query: 

lastName: IIf([Last]="Smith",Null,([Last]))

(I can hid the last name "Smith" in the lastName field.)

However, I cannot use the same IIf as a control source in a report.  It 
generates a circular reference error message.

= IIf([Last]="Smith",Null,([Last]))

(If the name is not "Smith", then print it.)

Is this just how IIf works?
0
Utf
6/7/2010 10:30:37 PM
access.reports 4434 articles. 0 followers. Follow

0 Replies
1767 Views

Similar Articles

[PageSpeed] 58

Reply:

Similar Artilces:

Query Based Distribution Lists #3
I can create QBDL based on STATE, ZIP, LASTNAME and many other fields. I would like to make a list based on Group Membership. There is a field under USER called MEMBER OF but it does not give me any results when I do a preview. I have many employees who work in multiple locations and need to belong to several different groups even though they have only one login and work out of a single office with the same address etc. So I made them members of groups based on the different location in which they work and wanted to send mail based on this membership. McMurray <McMurray@disc...

force a font name and font size in report
I am using Access 2007 & rich text format. I copy & paste course descriptions from emails into an Access database. There are as many font types as there are emails. Is there a way to force the Access report to display and print the field DESCRIPTION using only one font name & size; i.e. Times New Roman and size 10? Try the PlainText() function. For example the ControlSource of your text box might be: =PlainText([Description]) Be sure to change the Name property of the text box first. Access gets confused if it has the same name as a field, but is bound to something e...

Report objects missing after transferring .mdb to new location
Hi, I have created a database with only 1 table, many queries, and many reports. The reports contain objects (tables copied and pasted from excel) with fields scattered in relevant positions on the object (it was easier to do this than draw tables in Report Design!) I uploaded the .mdb to a http server and it was subsequently downloaded by my associate to his HDD. The database is blank, the queries still work. However the reports appear in name only (i.e. there is nothing to view). He gets no response from clicking into the particular report design either. He can generate new repo...

Why doesn't active quote get used in Pipeline report?
Here's the scenario I'm trying to model: 1. Create opportunity. Manually enter a ballpark figure of $10,000. 2. Work the opportunity a bit, and determine the needs. 3. Create a quote, adding existing products, for a total of $15,000, and activate it. 4. Run the pipeline report. Revenue is $10,000 5. Go to opportunity and change to system-calculated. 6. Save and close. 7. Run pipeline Result: Revenue is zero. Expected Result: Revenus is $15,000 I'd expect that if I activate a quote, the quote now drives the forecast revenue for that opportunity. I would expect that when I acti...

Appt reminders not working
User has Outlook 2002- when they set appointments with the reminder option, it does not remind them. Checked Tools/Options/Other/../Reminder Options; ck'd delivery; ran cleanreminders and cleanfreebusy. Any ideas? Will try uninstalling next but.... What version of Outlook does the recipient of the appointment use? --� 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 searching google.groups.com and finding no answ...

why has my copy and paste quit working in my words 2007
I did the select all and copy but when I try to paste it just paste an old copy from my clip board and won't paste the new copy Does it work in Word Safe Mode? From Start | Run (Winkey+R), type in winword /a and press Enter. Word will open in safe mode. Open a document and try copy/paste now. Does it work? -- Terry Farrell - MSWord MVP "Sandra Gillhouse" <Sandra Gillhouse@discussions.microsoft.com> wrote in message news:2DFCC793-AA5C-46F6-9D17-87BB8C931F87@microsoft.com... > I did the select all and copy but when I try to paste it just paste an old ...

SmartList "Order By" Window Does Not Work
Hello: Has anyone ever gotten SmartList's "Order By" window to work? I swear, I'll put in multiple columns to order by and the SmartList still sorts based on one column only. It always ignores the window. Has this window ever worked? SmartList and SmartList Builder are both wonderful. But, a lot of times, I end up having to dump the data to Excel and sort the multiple columns that way. childofthe1980s Child, Have you compared the sort results with Excel. You can test the same by taking the results to excel, then apply the same sorting options in smartlist order...

Using single button to run multiple query
I want to use single button on the form to run multiple queries. How it can be done? Where should I write queries and code? One way to do this would be to create your queries, then use the command button to run code that calls each query. Another approach would be to create all your queries, then create a macro that runs all your queries, and call that macro using code behind a command button. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Gajanan&qu...

File Share not working ove VPN SBS 2008
Hi All, I am having a problem with file share access over VPN on SBS 2008. The server has two NIC and one is disabled. It currently has AD role enabled, File Services, Exchange, RRAS (only running VPN). I have configured RRAS with a simple dial in VPN connection, forwarded the ports through the router, and made sure GRE is allowed. I can connect to the VPN just fine, and connect to the file shares of other domain joined computers on the same network as the server, but when I try browse to the server itself it times out (ie using windows explorer browsing to \\server). ...

Remember password (registery change do not work)
After updating from Outlook2000 to Outlook XP(2002) OutlookXP keeps asking for passwords for every account :-((( I use XPpro. I have tryed the trix "remove Protected Storage System Provider" in the registery (done by the letter as described in two Q-articles) but it do not help or change anything :-((( Please help -- erik Denmark Does the problem happen under a new operating system userid/profile? "erik" <fox44REMOVETHIS@c.dk> wrote in message news:%23wIdKxBNEHA.3572@tk2msftngp13.phx.gbl... > After updating from Outlook2000 to Outlook XP(2002) OutlookXP ...

Money 2004, Citibank, Auto Update Stopped Working
Hi I use Money 2004 Dlx to download transactions from my Citibank Credit card accounts. It was working perfectly fine till last month. Now, Money is not able to download the transactions from one account. Interestingly, it has no problem downloading the transactions from other account. BTW, both accounts have different user ID's. I tried to solve this issue thru "modify services". It does connect to my account as it detects the right CC number, but nothing happens after that. I would really appreciate your suggesions to solve this issue. Thanks In microsoft.public.m...

Not working {={sum(if(A1:B3=C1:D3,1,0))} sometimes
I have values 11,12,13,14,15,16 in A1:B3 and in C1:D3 I have 1,2,14,3,4,5. That is, instead of matching 14 in A2 with C2, I put it in D2. Now if I use the above formula, I get a zero, also when I check for <> I get 6.. However, If I put it back to C2 I get 1 & <>=5. Why this irregularity? =SUM(COUNTIF(A1:B3,TRANSPOSE(C1:D3))) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "dindigul" <padhye.m@gmail.com> wrote in message news:urgkn8mlHHA.4960@TK2MSFTNGP02.phx.gbl... >I have values 11,12,13,14,15,16 in A1:B3 and in C1:D3 I have 1...

Savings balance error in Monthly Report
I don't ususally run the monthly reports, but I did this month and noticed that Money was reporting that I have $158,388.62 in my Savings Account (which it claims earns 5.5%). This is under the heading of "Invest in your Debt". It is of course advising me to use some of that to pay down my current debt. As much as I wish this balance were correct, it ain't. Anyone know where Money gets this number and interest rate? Only thing I can think of is that maybe it's including my House. ...

Problem with iif and datediff expression in form
Hi, I have a form where I have to display the value of the field based on some criteria. The first criteria is =IIf((DateDiff("d",[dbo_PWO_Status].[WorkOrderDate],Now())-180)>0,"Y","") and the second criteria to add is dbo_PWO_Status.[Quote] = -1 My first question is how do I accommodate the dbo_PWO_Status.[Quote] = -1 criteria in the iif statement? The second question I have is that with the first expression put in the source for display I am getting Name instead of the Y value for matching condition. I would appreciate any help. Thanks. You n...

Microsoft Query #8
I'm using Excel to query an oracle database. When I go to Microsoft Query to edit my query, I can't view my criteria or tables. I click on the view menu and am unable to select view "criteria" or "tables". Thus, all that shows up in the screen is the data in my query. This has happend to me before. Can anybody help me? I was able to answer my own question. It seems when query is complicated enough, you are unable to view the criteria, since you are now using SQL to query the data. It seems that if you click on the SQL button and scroll down, delete th...

Totals Query Help!
Hi, I have a table with the following fields: Manufacturer Model Value SoldMonth What I'm trying to do is write a query that will give me an average Value for each month, but I want the average to be based only on records where the Model is consistent across all months. So if Model x is missing from one or more months all records for that model would be excluded from all the overall Monthly averages. So the query will only average records where the Model is present in each month. Any help with this would be greatly appreciated. Regards.....Jason It'll help us if you can ...

Get Report Name
I have a report selection form that allows the user to select multiple reports to preview. They have the ability to check a box that automatically sends each report to a separate pdf with a predefined name and path, using the CreateReportToPDF code by Stephen Lebans. What I want to do now is allow the user to preview multiple reports but selectively send reports to pdf. I have a custom reports tool bar, I’ve added a button but can’t figure out how to determine the name of the report the user is viewing. Thanks for your help, Jim Not sure but, I imagine you could loop through the R...

Adding multiple tables in one report
I am trying to customize the default report Daily Detailed Sales with Tax. What i need to do is add the Tender Type (Credit card / cash / check) as another column in the report. So far i have found out: i need to add a column i need to import the TenderEntry table for the data I need to find out: How to import another table I have tried to import the table using sql UNION function, but that wont work for me either. any help would be greatly appriciated. Thank You Hi ED, The things make sense to me regarding the adding tables and fields which you can do and customized the .grp file. ...

SSL
Hi, I finally have a working SBS 2003 SP1 install of CRM3.0 using SSL. I can access reportserver and reportmanager directly, but trying to run the reports through the CRM interface results in 'unable to establish trust' type error (after I turned on error display) I am currently using a private cert (possibly mismatched server name) - I have to accept this condition on initially opening - and wondered if this might be the issue? I saw somewhere that I could say for RS not to check the cert detail - but where please? TIA Chris ...

Graphic report in Report writer
Hi, I have modified the Short SOP invoice form to a graphic report. My issue is when I print the report on screen all the fields display correctly as modified. But when printing on paper most of the fields do not display correctly. What I see is that on the place where data should be printed some strange characters are displayed. I tried to print on different printers and different workstations, but still this issue remains. Also at the client site the same issue remains. Does anybody have an idea of how this should be solved? Looking forward for an solution, -- Elaine ...

TimerInterval not working
I have an access db application which needs to call the macro on startup. The macro is to call the function opening two forms on startup and set the TimerInterval of two forms. Public Function auto_login() DoCmd.OpenForm "frm_A" Forms![frm_Import&Export].TimerInterval = 300000 DoCmd.OpenForm "frm_B" Forms![frm_TREATS_ftp].TimerInterval = 120000 End Function However only the Timer for form B (at the toppest) works. It works for form A but not B if I open form B first and then A later. Is there any way to solve the problem? Regards Caris Caris, This seems a litl...

Control the number of records shown in a report/subreport.
I've posted this question before and when I follow the advice, I keep getting a circular reference error. So I'm hoping that if I post some more info, I may get insight as to what is going wrong. I have a report (BartS1report) it uses a query (BartS1Report). This report as a subreport (BartS1Sub2) that uses the query (BartS1). The report lists the customer name, address, and systems serviced. The subreport lists the systems serviced. The reports are connected through the Master/Child links Customer ID and the ServiceAddress. When there are more than 20 systems per ...

Date Selection on HR Training Reports
I am implementing Human Resources, Advanced Human Resources and Certifications, Licensing and Training On the standard HR training reports such as Employee Classes, date selection does not work. If I enter a date selection, nothing is printed on the report. Without date selection, everything prints. I found a KB article - 863263 that states this is an issue for V 8.0 but I can't find anything more frequent. This appears to still be an issue. Does anyone have an update or are you experiencing this as well. Thanks Becker, If you are a microsoft partner, You can log into Dynamics GP...

Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes reaches around 500 characters. Not huge, but larger than the Text limitation to which is what Access 2007 of course converts this. How can I force the data type to be Memo while I'm in the query so the resulting table displays all the data without any truncation. -- TIA Eric S UPRR I don't believe you can. I think you'll have to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "E...

Blank Report Message
I am working with Access 2002 trying to add code to a report when it opens to tell the user when there aren't any records to display. It works fine on a regular report, but I have a report that starts out blank, no query and no fields, but has two subs on it. I needed to do this because of calculations done in the footer on different fields. I have added the following code to the button that opens the report, rptQuestionsOneAndFive. Private Sub Command30_Click() On Error GoTo ErrorOpen DoCmd.OpenReport "rptCalendar", acViewPreview ExitOpen: Exit Sub Error...