You are here:

Using MS Access/Multiple criteria in control on report

Advertisement


Question
QUESTION: Hi Scott,

I wrote you a couple of days ago with a question regarding subreports, but I have since figured out my mistake.  I feel so out of touch with this lately.  

Anyway, I did have another question.  I have a control on a report that I need to add criteria to.  So far I have =IIf(IsError([PURCHASE DETAILS subreport].[Report]![COST]),0,[PURCHASE DETAILS subreport].[Report]![COST]).  

This works perfectly, but I have to base this calculation on the contents of a bound textbox.  I need to say something like IIF text1 = “LOL” followed by the rest of the code to perform the calculation.  I just don’t know where to place the IIf part of this line of code.  Or maybe it has to be a “where” instead of an “IIf”.  Any and all help is greatly appreciated as always.  Thank you in advance for your assistance.  
Carla

ANSWER: Sounds like a Nested IIF. The question here is what happens if text1 is not "LOL"

Scott<>

---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

The codes are actually T31, T52, T51 and T60.  These codes represent each budget that the expenditures are being charged against.  Every time a purchase order is entered, one of these codes are used to identify which budget will be charged.  I really am not sure how to write it.  Thanks so much for your help, I really appreciate it.  Cheers, Carla

ANSWER: I'm still not clear what you are trying to show. Based on the Budget code you want to show something, but I'm not clear what.

Scott<>

---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

My apologies for the poor descriptions of what I am trying to achieve. I haven’t used Access for years up until recently.  

We have a budget that is divided into four sections.  We use codes to identify each section.  Different types of expenditures get coded to each section.  For example, food and beverage purchases will get coded to T51, Appliances such as a stove would get coded to T52, clothing is coded to T60 and vehicles would get coded to T31.

=IIf(IsError([PURCHASE DETAILS subreport].[Report]![COST]),0,[PURCHASE DETAILS subreport].[Report]![COST]).

The above code is on a control in a main report that is used to calculate a total of expenditures that are on a subreport.  The Cost field is a calculated unbound field that totals the purchased items.  On each purchase, the budget code is used as part of the record to identify where the expenditure should show.  

The MAIN REPORT shows the divided budget amounts.  So if the expenditure is coded to T31, it will be added to the total on the line where the T31 budget is.  It kind of looks like this..

CODE      BUDGET   EXPENDITURES   REMAINING BUDGET
T31      $5000.00   $200.00      $4800.00
T52      $11000.00   $5000.00   $6000.00


Then the SUBREPORT lists the expenditures in detail such as this:

CODE      INVOICE #   SUPPLIER   DATE      INVOICE TOTAL
T31      12345      ABC  LTD.   JAN-01-2005   $200.00
T52      64789      DEF LTD.   JAN-30-2005   $5000.00


Any ideas??  Thanks so much for your help.  Carla  :)

Answer
Yes I do. I just realized that I never sent a reply to you previous question. I started to wrote one, then must have been interrupted. I don't see why you are using a subreport here.

I'm assuming that you have 2 tables here: Budget and Expenditures. Something like this:

tblBudget
Code
BudgetDescription
BudgetAmount

tblExpenditure
ExpeditureID (PK Autonumber)
BudgetCode (FK)
InvoiceNum
SupplierID (FK)
InvDate
InvTotal

So I would create a query that joins the 2 tables (along with the Supplier table) and returns the following columns:

Code
BudgetAmount
InvoiceNum
SupplierName
InvDate
InvTotal

Then I would use that query as the source of your report, grouping on Code. In the Group Footer (or Header, I would SUM the InvTotal, then I would add a control to calculate the remaining budget by subtracting the total form the budget.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.