I am putting together a database with the fields:
1. Number of Projects
2. Project (Name)
3. Project Contact
4. Company
5. Location (City, State)
6. Last Contact Date
7. Mw/g
8. Project Type
9. Total Project Capital
10. Expected Final Close (Date)
11. Financing Type (this will be either 1, 2, 3, or 4)
12. Score
13. Loan Amount (this amount will vary)
I would like to run a query that would add a column called the TRV Capital. I would like TRV Capital to calculate "11. Financing Type (this will be either 1, 2, 3, or 4)" and "13. Loan Amount (this amount will vary)" under a few circumstances.
The circumstances would be:
If "Financing Type" = 1 or 2 THEN the TRV Capital would be 20% * "Loan Amount" OR If "Financing Type" = 3 or 4 THEN the TRV Capital would be 100% * "Loan Amount"
I tried putting in the espression as:
TRV Capital: IIf([Financing Type]="1",[Loan Amount]*0.2,IIf([Financing Type]="2",[Loan Amount]*0.2,IIf([Financing Type]="3",[Loan Amount]*1,IIf([Financing Type]="4",[Loan Amount]*1))))
But it is not working. Please HELP!!!!!
9 1508 Rabbit 12,516
Recognized Expert Moderator MVP
Is financing type a number or text? Because with the quotes, you're telling Access that it's text.
Financing Type is a number. Ok. I took away the quotes. But I still get this error message that says," You tried to execute a query that does not include specified expression IIf([TRV Main Pipeline].[Financing Type]=1,[Loan Amount]*0.2,IIf([Financing Type]=2,[Loan Amount]*0.2,IIf([Financing Type]=3,[Loan Amount]*1,IIf([Financing Type]=4,[Loan Amount]*1)))) as part of an aggregate function."
What does that mean?
Rabbit 12,516
Recognized Expert Moderator MVP
Then you need to get rid of the quotes.
Ok. I took away the quotes. But I still get this error message that says," You tried to execute a query that does not include specified expression IIf([TRV Main Pipeline].[Financing Type]=1,[Loan Amount]*0.2,IIf([Financing Type]=2,[Loan Amount]*0.2,IIf([Financing Type]=3,[Loan Amount]*1,IIf([Financing Type]=4,[Loan Amount]*1)))) as part of an aggregate function."
What does that mean?
Then you need to get rid of the quotes.
Rabbit 12,516
Recognized Expert Moderator MVP
Ok. I took away the quotes. But I still get this error message that says," You tried to execute a query that does not include specified expression IIf([TRV Main Pipeline].[Financing Type]=1,[Loan Amount]*0.2,IIf([Financing Type]=2,[Loan Amount]*0.2,IIf([Financing Type]=3,[Loan Amount]*1,IIf([Financing Type]=4,[Loan Amount]*1)))) as part of an aggregate function."
What does that mean?
It means you're using a totals query but you're not using that field as part of the calculation. What's the whole SQL string? What is the query supposed to do?
SQL String is:
SELECT DISTINCTROW [TRV Main Pipeline].Project, [TRV Main Pipeline].[Project Contact], [TRV Main Pipeline].Company, [TRV Main Pipeline].[Location (City, State)], [TRV Main Pipeline].[Last Contact Date], [TRV Main Pipeline].[Mw/g], [TRV Main Pipeline].[Project Type], [TRV Main Pipeline].[Expected Final Close], [TRV Main Pipeline].[Financing Type], [TRV Main Pipeline].[TRV Person (Initials)], Sum([TRV Main Pipeline].[Number of Projects]) AS [Sum Of Number of Projects], Sum([TRV Main Pipeline].[Total Project Capital]) AS [Sum Of Total Project Capital], Sum([TRV Main Pipeline].[Loan Amount]) AS [Sum Of Loan Amount], Count(*) AS [Count Of TRV Main Pipeline], [TRV Main Pipeline].Score, IIf([TRV Main Pipeline].[Financing Type]=1,[Loan Amount]*0.2,IIf([Financing Type]=2,[Loan Amount]*0.2,IIf([Financing Type]=3,[Loan Amount]*1,IIf([Financing Type]=4,[Loan Amount]*1)))) AS [TRV Capital]
FROM [TRV Main Pipeline]
GROUP BY [TRV Main Pipeline].Project, [TRV Main Pipeline].[Project Contact], [TRV Main Pipeline].Company, [TRV Main Pipeline].[Location (City, State)], [TRV Main Pipeline].[Last Contact Date], [TRV Main Pipeline].[Mw/g], [TRV Main Pipeline].[Project Type], [TRV Main Pipeline].[Expected Final Close], [TRV Main Pipeline].[Financing Type], [TRV Main Pipeline].[TRV Person (Initials)], [TRV Main Pipeline].Score;
I would like the TRV Capital to calculate that If "Financing Type" = 1 or 2 THEN the TRV Capital would be 20% * "Loan Amount" OR If "Financing Type" = 3 or 4 THEN the TRV Capital would be 100% * "Loan Amount"
It means you're using a totals query but you're not using that field as part of the calculation. What's the whole SQL string? What is the query supposed to do?
Rabbit 12,516
Recognized Expert Moderator MVP
I would like the TRV Capital to calculate that If "Financing Type" = 1 or 2 THEN the TRV Capital would be 20% * "Loan Amount" OR If "Financing Type" = 3 or 4 THEN the TRV Capital would be 100% * "Loan Amount"
That's only one of the things you want to do. What do you want the query to do?
That is all I want the query to do. As I input the Financial Type and the Loan Amount, I need the TRV Capital to take what I input and give me the calculated amount. I would like the actual table to do it, but I don't think it would, so I chose to try in query form.
TRV Capital: IIf([Financing Type]=1,[Loan Amount]*0.2,IIf([Financing Type]=2,[Loan Amount]*0.2,IIf([Financing Type]=3,[Loan Amount]*1,IIf([Financing Type]=4,[Loan Amount]*1)))) (??)
That's only one of the things you want to do. What do you want the query to do?
Rabbit 12,516
Recognized Expert Moderator MVP
That is all I want the query to do. As I input the Financial Type and the Loan Amount, I need the TRV Capital to take what I input and give me the calculated amount. I would like the actual table to do it, but I don't think it would, so I chose to try in query form.
TRV Capital: IIf([Financing Type]=1,[Loan Amount]*0.2,IIf([Financing Type]=2,[Loan Amount]*0.2,IIf([Financing Type]=3,[Loan Amount]*1,IIf([Financing Type]=4,[Loan Amount]*1)))) (??)
Well, you have all these SUM functions in there. Get rid of those and get rid of the entire GROUP BY statement.
If all you need is the TRV Capital then you don't need the SUMs and the GROUP BY.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Sims |
last post by:
Hi,
I need some help to split data using regular expression
Consider the string
'1,2,3', I can split it using, preg_split("/,/", '1,2,3') and i correctly
get =1, =2,=3.
Now if i have
|
by: |
last post by:
I am trying to run the following agregate function in a parameterized query
on Access2000:
Min(.*sqr(./.)/)
The query saved OK, but an attempt to run it results in the message:
The expression is typed incorrectly or it is too complex to be evaluated
If the sintax correct? Perhaps it is, otherwise it would not save.
What can be done about it?
|
by: Gálos Zsuzsa |
last post by:
Hi all,
I need to calculate Formulas.
For example:
dim strFormula as string="25*(12-6)"
I ned a DotNet Function to calculate this Formula (25*(12-6)=150):
dim dblValue as double = 150
|
by: talin at acm dot org |
last post by:
I've been reading about how "lambda" is going away in Python 3000 (or
at least, that's the stated intent), and while I agree for the most
part with the reasoning, at the same time I'd be sad to see the notion
of "anonymous functions" go - partly because I use them all the time.
Of course, one can always create a named function. But there are a lot
of cases, such as multimethods / generics and other scenarios where
functions are treated...
|
by: frankh |
last post by:
Got earlier very valuable feedback from this group, so I am trying
again.
Given a string with an arithmetic expression containing variables, e.g.
"a+b*sin(x)", how can I calculate its value within my program for given
variable values? (Think of the string as a user input.) Is there an
easier way than writing an interpreter myself? Mathematical notation is
preferred, but C# syntax ("Sin" instead of "sin") would be acceptable
too.
| |
by: Klaus Jensen |
last post by:
Hi!
I am currently converting an ASP/SQL Server application to VB.Net/Access.
The application performs calculations based on which products are selected.
Therefore i need to be able to store formulas in the database, replace
placeholdes with values and execute the calculation.
Earlier this was done by executing the formula on SQL Server and retrieving
the result, but that is no longer possible - or desireable for that matter.
|
by: sarathy |
last post by:
Hi,
Can anyone just help me with what exactly is constant
expression. I read the section on Constant expression in K&R2. i am not
fully clear with it.
|
by: Carles Company Soler |
last post by:
Hello,
I want to calculate the value of an attribute. For example <rect x="2+3"
y="12"and be <rect x="5" y="12">. Is it possible using XSLT?
Thanks!
|
by: Libber39 |
last post by:
Hi everyone,
Have a query on how to calculate the amount of weeks and days contained in a number in an access query. ie: the difference in days between 2 dates amounts to 17 days. I want to now calculate in the query the amount of weeks and days within the 17 days to show 2 weeks 3 days.
I can create the function within excel, (A1 as cell with the days within it) as:
=INT(A1/7) & " Weeks, " & MOD(A1,7) & " days"
but cant seem to...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |