Results 1 to 2 of 2
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    IIF statement not working in all fields on Report

    I am attempting to do a dlookup for a price based on the number of pages printed. I have created a report with the number of pages printed [CountPage] from a query. I also have a query that has the price matrix based on the number of pages printed. I have made a subreport based on the pricing matrix query. I then added a field in the report that contains an iif statement to look up the correct based on the subreport. The Control source has the expression =IIf([CountPage] Between [qryPricePrint_LM subreport].[Report]![ProductVolumeTierLow] And [qryPricePrint_LM subreport].[Report]![ProductVolumeTierHigh],[qryPricePrint_LM subreport].[Report]![ProductPrice]).



    Problem: the price does not show up in all the rows. Currently, there are only two rows with different page counts, but only one of the rows shows a price and it is correct. The other field is blank. Please see below.

    Click image for larger version. 

Name:	Pricing Matrix Report.PNG 
Views:	17 
Size:	5.9 KB 
ID:	44765

    Ideas??

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I don't think you can refer to report quantities in that way.
    The report sets up it's own internal version of the query for sorting and grouping purposes, so I suspect the result will only be available once the entire report is formatted.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 05-08-2018, 07:27 AM
  2. Replies: 5
    Last Post: 01-21-2018, 02:34 PM
  3. If Statement Not Working
    By conradqb in forum Macros
    Replies: 4
    Last Post: 06-22-2015, 01:13 PM
  4. IIF Statement isn't working
    By djclntn in forum Queries
    Replies: 9
    Last Post: 08-21-2013, 09:40 AM
  5. IIF Statement not working...
    By LanieB in forum Queries
    Replies: 6
    Last Post: 01-05-2012, 12:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums