Solved

How to incorporate Criteria in MS Access Query for a report

Posted on 2011-02-22
20
269 Views
Last Modified: 2012-05-11
I have a report in a Access database called Invoices. How and where do I incorporate the following criteria in the query to meet these requirements. I am new to access.

Sub Accounts
BTT = 763679
BTI = 763665
STATS = 762925
IDS = 763919


Credit Obj Code
If DivisionCode=”BTT” or ”BTI” or “Stats” then it is = 473
Else DivisionCode =”IDS” which is 693
End If

Charge Obj Code
If DivisionCode=”BTT” or “BTI” or “Stats” then it is = 274
Else DivisionCode=”IDS” which is = 224
End if

0
Comment
Question by:Chrisjack001
  • 11
  • 7
  • 2
20 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34952595
maybe this:

where
[Credit Obj Code] = iif(DivisionCode="BTT" or DivisionCode="BTI" or DivisionCode="Stats", 473, 693)
and
[Credit Obj Code] = iif(DivisionCode="BTT" or DivisionCode="BTI" or DivisionCode="Stats", 274, 224)

0
 

Author Comment

by:Chrisjack001
ID: 34952705
How about the Sub Accounts and how and where can I put that in the design view. I Am new to this
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34952774
post the query for report...
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34953053
Better still, post the database.  This kind of criteria tweaking needs real data to work with.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34953056
You will need to set up multiple criteria rows to accommodate the various combinations of criteria.
0
 

Author Comment

by:Chrisjack001
ID: 34954073
This is the query for the report.

SELECT Invoices.*, [Study Details].*, [InvoiceDetail Subtable].*, Divisions.DivisionName, [Invoices Max of Print Date].Total, [Invoices Max of Print Date]![MaxOfPrintDate] AS PrintDate FROM [InvoiceDetail Subtable] RIGHT JOIN ((Divisions INNER JOIN (Invoices INNER JOIN [Study Details] ON Invoices.Study = [Study Details].StudyID) ON Divisions.DivisionCode = Invoices.DivisionCode) INNER JOIN [Invoices Max of Print Date] ON Invoices.InvoiceID = [Invoices Max of Print Date].InvoiceID) ON [InvoiceDetail Subtable].InvoiceID = Invoices.InvoiceID;
0
 

Author Comment

by:Chrisjack001
ID: 34955071
Attached is the database.
Main-Clinical-Sciences-Accountin.accdb
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34955153
i cannot open the db.. it gives invalid path for o:\biostats\....\main accounting original_be.accde
0
 

Author Comment

by:Chrisjack001
ID: 34955677
Attached is the database
Main-Clinical-Sciences-Accountin.accdb
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34955826
I get similar error when trying to open it

c:\documents and settings\cjac11\desktop\main accounting original_be.accde" is not a valid path... dont know why it is checking that folder? do you have some external code/data in that acdde file?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 51

Expert Comment

by:HainKurt
ID: 34955838
oops, there are lots of linked tables pointing to that file
0
 

Author Comment

by:Chrisjack001
ID: 34955876
It is the Object Report thats called Invoices. Thats the one that should meet that requirement when you open it.
0
 

Author Comment

by:Chrisjack001
ID: 34955890
What should I do to send this DB to you. I thought I should just attach it.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34955913
try adding this to the query of report

where
tablename.[Credit Obj Code] = iif(Invoices.DivisionCode in ("BTT","BTI","Stats"), 473, 693)
and
tablename.[Charge Obj Code] = iif(Invoices.DivisionCode in ("BTT","BTI","Stats"), 274, 224)
0
 

Author Comment

by:Chrisjack001
ID: 34956142
Should I put this directly after the last line in this query.

SELECT Invoices.*, [Study Details].*, [InvoiceDetail Subtable].*, Divisions.DivisionName, [Invoices Max of Print Date].Total, [Invoices Max of Print Date]![MaxOfPrintDate] AS PrintDate
FROM [InvoiceDetail Subtable] RIGHT JOIN ((Divisions INNER JOIN (Invoices INNER JOIN [Study Details] ON Invoices.Study = [Study Details].StudyID) ON Divisions.DivisionCode = Invoices.DivisionCode) INNER JOIN [Invoices Max of Print Date] ON Invoices.InvoiceID = [Invoices Max of Print Date].InvoiceID) ON [InvoiceDetail Subtable].InvoiceID = Invoices.InvoiceID;
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 250 total points
ID: 34956239
yes but modify the table name first... which table has these columns

[Credit Obj Code]
[Charge Obj Code]

you should modify the tablename before using the query below
SELECT Invoices.*, [Study Details].*, [InvoiceDetail Subtable].*, Divisions.DivisionName, [Invoices Max of Print Date].Total, [Invoices Max of Print Date]![MaxOfPrintDate] AS PrintDate
FROM [InvoiceDetail Subtable] RIGHT JOIN ((Divisions INNER JOIN (Invoices INNER JOIN [Study Details] ON Invoices.Study = [Study Details].StudyID) ON Divisions.DivisionCode = Invoices.DivisionCode) INNER JOIN [Invoices Max of Print Date] ON Invoices.InvoiceID = [Invoices Max of Print Date].InvoiceID) ON [InvoiceDetail Subtable].InvoiceID = Invoices.InvoiceID
where
tablename.[Credit Obj Code] = iif(Invoices.DivisionCode in ("BTT","BTI","Stats"), 473, 693)
and
tablename.[Charge Obj Code] = iif(Invoices.DivisionCode in ("BTT","BTI","Stats"), 274, 224)

Open in new window

0
 

Author Comment

by:Chrisjack001
ID: 34956616
The Credit Obj Code and Charge Obj code are not from any specific table. It should just meet that criteria. Where do I incorporate the Sub Accounts from the requirement

Sub Accounts
BTT = 763679
BTI = 763665
STATS = 762925
IDS = 763919
0
 

Author Comment

by:Chrisjack001
ID: 34956896
I dont want the user to be prompted when trying to open the report. Based on your recommendation thats what it is doing now. I also noticed that IDS which is 693 and 224 is missing from the query.
0
 

Author Comment

by:Chrisjack001
ID: 34956898
Please help
0
 

Author Closing Comment

by:Chrisjack001
ID: 35977383
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now