Calculate Expression w/ circumstances

[Desyree]

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!!!!!

[Rabbit]

Is financing type a number or text? Because with the quotes, you're telling Access that it's text.

[Desyree]

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]

Then you need to get rid of the quotes.

[Desyree]

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]

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?

[Desyree]

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]

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?

[Desyree]

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]

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.