Create Calculated Field in Query using multiple fields. 1

[monkeysee]

Good Afternoon,
I think this is a very simple problem, but I just can't get it correct.
I have a query that has the following fields: Fee, PaymentAmt and Adjustment
I wish the query to calculate the balance after all payments and adjustments have been applied.
This is what I have so far: Balance: [fee]-[Adjustment]-Nz([paymentamt],0)
When I run the query, it returns NO records.
What am I missing?

Thanks in advance!

 

[dhookom]

It sounds like you might have put your expression in the Criteria rather than the Field row. Can you share the SQL view of your query? It should be something like:

SELECT Fee, PaymentAmt, Adjustment, [fee]-[Adjustment]-Nz([paymentamt],0) as Balance
FROM [YourTableNameHere]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

Here it is (there are more fields than I stated above, but aren't relevant to the calculation problem)- I also removed the 'adjustment field out of the expression so that I could have the query produce records until I got the complete expression correct. Thanks again for your help!

SELECT Clients.ClientId, [Lastname] & " " & [FirstName] AS Name, Visit.VisitDate, Service.ProcedureCode, Service.Modifier, Service.Fee, Clients.Primary, Clients.Secondary, VisitDetails1.Adjustment, VisitDetails1.PaymentAmt, VisitDetails1.PaymentDate, VisitDetails1.PaymentComments, [fee]-Nz([paymentamt],0) AS Balance
FROM ((Clients LEFT JOIN Visit ON Clients.ClientId = Visit.Client_ID) LEFT JOIN VisitDetails1 ON Visit.VisitID = VisitDetails1.VisitID) LEFT JOIN Service ON VisitDetails1.ServiceID = Service.ServiceTypeID
WHERE (((Visit.VisitDate) Is Not Null) AND ((Clients.Primary) Is Not Null) AND (([fee]-Nz([paymentamt],0))<>0))
ORDER BY [Lastname] & " " & [FirstName];

 

[monkeysee]

I got it to work! What I did: Instead of using the expression builder, I went directly to the SQL view and entered the formula there. It was the exact same formula that I used in the expression builder!
So for some reason, the expression builder had a glitch.
Thank so much for reinforcing my thought process.