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

  • Thread starter Thread starter AGD
  • Start date Start date
A

AGD

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
 
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
 
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.
 
Back
Top