CODEWITHSUNDEEP
ms-accessms-access-2013calculated-field
LetsDoThis
LetsDoThis

Reputation: 29

Using a calculated field in a DSum function in a query in Access

I am trying to run DSum with a calculated field, CurrentCuotaPaid, within the function, but I am not sure if it is possible because there is no table to refer to in the DSum syntax.

Is it possible to run DSum with a calculated field within the function and refer to the query itself instead of the table?

Below is my full Select expression:

SELECT LlamadoDeCapital.ID, LlamadoDeCapital.TotalCapitalCommitted, LlamadoDeCapital.CapitalCallRequestDate, LlamadoDeCapital.CapitalCallDate, LlamadoDeCapital.CurrentCapitalCall, DSum("CurrentCapitalCall","LlamadoDeCapital","CapitalCallDate <=" & CLng([CapitalCallDate])) AS TotalCapitalCalls, [CurrentCapitalCall]/50000000 AS [%CurrentCuotaPaid], DSum("CurrentCuotaPaid","LlamadoDeCapitalQuery","CapitalCallDate <=" & CLng([CapitalCallDate])) AS [% Cumulative Cuota Paid]
FROM LlamadoDeCapital;

Here is the sample of the DSum function where I am using the calculated field: 

DSum("CurrentCuotaPaid","LlamadoDeCapitalQuery","CapitalCallDate <=" & CLng([CapitalCallDate])) AS [% Cumulative Cuota Paid]

When I run the query, it gives completes the query, but in the fields "#Error" is repeated throughout the entire column instead of performing the calculation correctly.

Upvotes: 1

Views: 848

Answers (1)

LetsDoThis
LetsDoThis

Reputation: 29

I was able to create another query and added the fields from the "LlamadoDeCapitalQuery". Once I did this, I was able to call "LlamadoDeCapitalQuery" in the DSum syntax. 

SELECT LlamadoDeCapitalQuery.TotalCapitalCommitted, LlamadoDeCapitalQuery.CapitalCallRequestDate, LlamadoDeCapitalQuery.CapitalCallDate, LlamadoDeCapitalQuery.CurrentCapitalCall, LlamadoDeCapitalQuery.TotalCapitalCalls, LlamadoDeCapitalQuery.[%CurrentCuotaPaid], Format (DSum("[%CurrentCuotaPaid]","LlamadoDeCapitalQuery","CapitalCallDate <=" & CLng([CapitalCallDate])), "Percent") AS [%CumulativeCuotaPaid]
FROM LlamadoDeCapitalQuery;

This was the resolution.

Upvotes: 1

Related Questions