IIf in a report based on select query -- problem w/parameters

I have a report based on a select query.  I've added a column in the report 
to add information into the report based on whether or not two values within 
the query are equal.  The following code is in the unbound text box of my 
report (created using expression builder).  

=IIf(MG8!Cust_First_Order_Date_Time=MG8!OrdFlow_Actual_End,"1","0")

When trying to run the report I get the "Enter Parameter Value" window 
requesting a parameter of MG8.  Entering any or no parameter returns my 
report with "1" in all rows (which is not correct based on review of data).  
Here's what I've tried/done:
1. variations on the code's syntax but to no avail. (ie. 
=IIf(([MG8]![Cust_First_Order_Date_Time]=[MG8]![OrdFlow_Actual_End],"1","0")

2. "masking" the field name within the query (ie. FO: Cust_First_Order_Date) 
to get rid of possible "Date" confusion

3. text box in report is uniquely titled to avoid conflict 

Here's what I'd like:

A. The reason it's not working
B. Help in writing the correct formula

Thanks in advance

0
Utf
3/25/2010 1:44:08 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
803 Views

Similar Articles

[PageSpeed] 42

The reason it's not working, is because the report doesn't see any
fields called MG8 in it's local scope, hence it asks for its value as
parameter.

if MG8 is a query, is it the record source of the report?  if so, they
you can just exclude it altogether, because the report will be able to
"see" those Cust_First_Order_Date_Time and OrdFlow_Actual_End fields.
You can verify which fields you can use in the report by viewing the
Field List in the View Menu.


if MG8 is a query that is not part of the report's recordsource, you
would need to use a DLookup function to get the values for those 2
fields.


if MG8 is the name of a form, then you need to change the syntax to:
Forms!MG8.Cust_First_Order_Date_Time



0
ghetto_banjo
3/25/2010 2:22:31 PM
Suggestions:

1. Omit the MG8!
Unless your query returns multiple fields with the same name, you don't need 
to (and shouldn't) use the table name in your report.

2. Due to the way Access fetches data for a report, you may need to include 
text boxes for Cust_First_Order_Date_Time and OrdFlow_Actual_End. Hide them 
if you wish.

3. If you want numeric values (not text values), omit the quotes, i.e.:
    =IIf([Cust_First_Order_Date_Time] = [OrdFlow_Actual_End], 1, 0)

4. Set the Format property of this text box to General Number (or some 
numeric format), to ensure Access understands the data correctly.

5. If the date/time fields contain a time value (not just a date), they may 
not be equal even if they look like they are (due to the way Access handles 
fractions of a day.)

6. This all assumes that the query returns these actual fields.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"AGD" <AGD@discussions.microsoft.com> wrote in message 
news:127EE9FE-5A07-45B7-B965-F0F9F165CD18@microsoft.com...
> I have a report based on a select query.  I've added a column in the 
> report
> to add information into the report based on whether or not two values 
> within
> the query are equal.  The following code is in the unbound text box of my
> report (created using expression builder).
>
> =IIf(MG8!Cust_First_Order_Date_Time=MG8!OrdFlow_Actual_End,"1","0")
>
> When trying to run the report I get the "Enter Parameter Value" window
> requesting a parameter of MG8.  Entering any or no parameter returns my
> report with "1" in all rows (which is not correct based on review of 
> data).
> Here's what I've tried/done:
> 1. variations on the code's syntax but to no avail. (ie.
> =IIf(([MG8]![Cust_First_Order_Date_Time]=[MG8]![OrdFlow_Actual_End],"1","0")
>
> 2. "masking" the field name within the query (ie. FO: 
> Cust_First_Order_Date)
> to get rid of possible "Date" confusion
>
> 3. text box in report is uniquely titled to avoid conflict
>
> Here's what I'd like:
>
> A. The reason it's not working
> B. Help in writing the correct formula
>
> Thanks in advance
> 
0
Allen
3/25/2010 2:26:04 PM
Reply:

Similar Artilces:

need help- count w/ multiple criteria
--------------752C968DC7D28F3A2ED980B0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Here's a snippet of my data source TEAM MANAGED OPENED TROUBLE_TICKET_ID MAG Y 4/10/2004 5:30 1253992 MAG Y 4/13/2004 5:18 1253984 MAG Y 4/12/2004 5:08 1253979 MAG Y 4/11/2004 14:10 1253953 MAG N 4/13/2004 3:59 1253947 PWS N 4/13/2004 3:33 1253926 MAG N 4/13/2004 2:59 1253883 IPT N 4/13/2004 2:45 1253871 MAG N 4/13/2004 2:33 1253865 IPT N 4/13/2004 ...

Office XP SP 3 interfering w/ Excel
I tried installing Office XP service pack 3, but, like many other people, I don't have the Microsoft Office CD to insert when prompted. I figured I wouldn't install this update and ignore it. However, I just tried to open Excel to work on something and I received a message that said Windows in trying to update Microsoft Office with Front Page and I need to insert the CD (which I don't have). It also says I could browse for the file 'PROPLUS.MSI' (which I can't find on my computer). I can't open Excel, but all the other Microsoft Office Applications work j...

Problems saving a worksheet with Links
Does anyone know how I can resolve this issue ... I have a directory which contains 129 worksheets which have links to external data (in a Master Spreadsheet) -- I need to copy these files into a New Directory, but kee the Master Spreadsheet (which they are linked to) in the original location. If I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets moved to the New Directory (where the file does not exist), but if I open the worksheet (in the original directory/location) and Save As to the New Directory, the worksheet saved in the New Directory maintains its link t...

RPC over HTTP problem #3
Hi, All! My network configuration: DC1, DC2 and MX (MS Exchange 2003, sp1). All of them Windows Server 2003. What was done: In the registry on dc1 and dc2 was created a new key: "NSPI Interface protocol sequences" with value: ncacn_http:6004. MX was promoted to be a GC. Installed RPC over HTTP windows component. MX was changed to be RPC-HTTP back-end server. On the MX Default Web Site was installed cerificate from the local authority running on DC2. On the RPC virtual directory anonymous access and integrated windows authentication were disabled. In the registry of MX the key HK...

Excel template that lists each month's meetings w/ some detail
I'm looking for an Excel template that lists every month of the year with room to list weekly, monthly and quarterly meetings, including some detail for each meeting. Probably would need to be landscape oriented. The main intent is to give the person using it an idea of upcoming meetings, the intent of each and what prep is required. ...

Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Tax software report
I am unable to view any of my "Insurance : Health" category transactions when I run the "Tax software report" in the Reports section. To be sure, I confirmed that I previously check the tax form, Schedule A, and Doctors - medicine - etc. tax line in the tax manager. The error seems most perculiar given all of my "Healthcare" category transactions are viewable when I run the same report. Any suggestions? ...

How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2 cases 2 bottles if possible 2.2 in a case column. See http://www.cpearson.com/excel/fractional.htm for details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in message news:893CABE9-37D7-4E6B-8A7E-A5E679C8C824@microsoft.com... >...

http://crm/Reports vs. http://crm/Reportserver
I have several MS CRM reports residing on a SQL Server 2005 that were migrated from 3.0. I can access the reports via the following url: http://crm/Reports then clicking the folder and opening up a report. However, When I try and use http://crm/Reportserver and click on the reports I see the same folder and reports but I get an error message (below) and cannot open any reports? I believe this to be a SRS issue. In the following the GUID appears to be the link and the reports are in a 4.0 folder. Wednesday, April 02, 2008 11:38 AM 23166 {5a8355f1-e300- dd11-a47f-001aa03779ca} ...

Error- Project is used by another user -Please select another proj
Please see the subject line, when im trying to open a particular project. it gives me following error. I checked the current users, there are no users. Apart from me. There are some non PA users,i tried to remove them from activity. inspite of me removing them from activity, im geting the same error. Help !!! Ramakrishnan Hello Ram, There is a stranded user in PA000001 table. You need to clear this User/Project combination. Ajay "Ram" wrote: > Please see the subject line, when im trying to open a particular project. > it gives me following error. > > I che...

Conditional Formatting w/ a List/Icons
I am trying to allow someone to select "Green", "Yellow" or "Red" from a list and the cell to display a green/yellow/red icon appropriately. Or, if possible, the user could just select the icon (instead of selecting text). Is this possible? Use Data Validation for the list. Type in Red, Yellow, Green as the list. This give the user the list to select from. Use Conditional Formatting for the fill part. Set three conditions, If Cell Value-"Green" (select a green fill), etc.. -- If this helps, please remember to click yes. "...

Send/Receive Problem
I am using Outlook 2002 on an XP platform. I cannot get Outlook to check for Email at regular intervals. I have the my Outlook set to Send and Receive all my accounts every 10 minutes but nothing happens. The only way I can receive Emails is by manually using the Send/Recv button or pressing F9. Can anyone offer any help. In case it is relevant I am using Norton Internet Security 2003. PWS Not sure it it Yor problem, but Outlook has some problems with Noroton Antivirus runing and chekking e-mails. As far as I know, Outlook may stop recieving e-mails from POP3 servers due to very le...

Font problem with Office 2004 for mac
I have Tiger, whenever I try to Launch any Office program, as the the menu loads to 'optimizing font menu performance' It pops up with 'The font " " has been corrupted and should be removed'. It does this with MANY fonts, many of which I don't even have in my font folder. It does this every time during the program start, and most times even if I go through clicking ok 40-60 times it will sometimes freeze up anyway. Anyway to fix, get around this problem? Please email me at madefornothing@yahoo.com. Hi, this problem affects quite a few users, and there'...

Excel problem #3
I am attaching an excel file where i have a problem In the file are 2 sheets, Main & second I want to get data from second sheet to the main sheet by a formula by which the amount in the total column will be posted in the second sheet falling under various dates. I have done for 6 sept 2003 by way of example I do not know any formula by which i can do this automatically Please help me Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=444742 --- Message posted from http://www.ExcelForum.com/ Hi one way: ...

Sync net folder problem
I am sharing my calendar to my workmate with net folder. My PC is Win XP and Office 2000 and my workmate's is Win 98 and also Office 2000. I always find that My calender can't be updated from my workmate when I return office after I've taken my notebook for a few days. Can't net folder sync. data offline? Thx. your attention. Ken So Net Folders uses e-mail messages to send updates between computers so naturally you would have to be connected to your e-mail to get any updates. When you leave the office and are not connected you won't get any updates but as soon as ...

chart line style problem
I am making a scatter chart (with lines) in Excel 2007 under Vista. I can select a line style, for example, long dashes. However, if I try to change the axis (change from "automatic" to "fixed" on the horizontal axis), the line on the chart immediately becomes solid again. The legend still shows the proper dashing. I can get the dashing partly back by making the line thinner, but only where the variation is fastest - regions where the derivative is near zero are still solid even for thin lines. I'll appreciate any help! frank I was not able to reproduce this. Can you...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

auto forward problems
I setup a 'contact' for 5 existing users in Exchange 5.5 Administrator. I give the contacts the desired SMTP address where they want their mail forwarded to. I set the corresponding 'contact' as the 'alternate recipient' for each of the 5 as detailed in Q255697. 2 of 5 work, the other 3 do not. When sending to each of the 5, 3 return undeliverable stating "A configuration error in the e-mail system caused the message to bounce between two servers or to be forwarded between two recipients." Any ideas? -adam Adam SK wrote: > I setup a 'con...

Exchange 2003 with SP2 problems...
I installed the SP2 for exchange 2003 server tonight, and I'm getting some problems. I added the registry key to increase the DB size to 30GB per the instructions, but I don't get a confirmation in eventid 1216 as it says I should. In fact, the message in eventid 1216 looks mised up. See insert: The Exchange store '16384' is limited to First Storage Group\Mailbox Store (ATL-SBS) GB. The current physical size of this database (the ..edb file and the .stm file) is %3 GB. If the physical size of this database minus its logical free space exceeds the limit of First Storage Gro...

small problem
hi every body; i wrote a program that it has error ;plz help me :( using System; namespace ConsoleApplication45 { class Program { static void Main(string[] args) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("***********"); for (int i = 0; i < 1008; i++) { Console.BackgroundColor = ConsoleColor.DarkCyan; Console.Write(" "); } move(); // ***********error is for here***********************************************...

save as version 2003 problem
I'm working in vba in Access to create and save an Excel file. All's good except that one of the workstations this is runnign on is using Office 2007. I'm developing in 2003 and all the other workstatiosn they have are using 2003. It's very important that the files be saved in 2003 format. When I do this, it runs fine and saves as 97/95 objExcelBk.SaveAs sTempPath & sExcelFileName, xlExcel9795 ','56 = xl 2000/2003 I read online in a forum post that "56" is the correct code for saving as 2003 but that's when the code is written in 20...

Active Directory/Exchange problem
All, Before I joined my current employer the admin here upgraded from Exchange 5.5 to Exchange 2000(Box A) and then added another Exchange 2000 box to the organisation(Box B)and migrated the data in Box A to Box B. Box B is now the working exchange server and Box A is no longer used. The problem is that if I actually shut down Box A I can add a new user to Active Directory but I am unable to modify a users email/smtp details. All mail can still be transferred with no problems which would lead me to believe that Exchange is Ok but there is some sort of Active Directory link between the two bo...

Pulling counts out of query results
I have a query that has one field Type which is set to Count The query results are used in a report. The report has the fields in the detail section as TYPE and CountOfType (1 line only in the detail section) The report looks like this when displayed AS 28 AV 17 OR 5 I need to be able to get the individual AS No (28) and add it to another number elswhere in the report. How can I do this? I have tried using a textbox with an if function (if [type]="AS",CountOfType,0 but that did not work. Any help appreciated. Ray I think you will need to do it in the query. But try addi...

SetParameterFields in CCrystalCtrl Activex(Crystal Report)
Hi , I am trying to call a Crystal Report from my VC program.In that i am facing problem while trying to send a selection criteria for my report (the equivalant in VB is SelectionFormula) I want to make selection based on a Date feild and another extension filed (say date between '01/01/2003' and '01/01/2004' and extn_no = 100) How can i do that.Any idea???????????? Reny ...