No value returned

[mtagliaferri]

The query below has a field named TOT, for some reason it only returns the value where both fields have a value.
On the other side I have a form linked to the query and it looks much neater displaying no values on the form rather than a long list of 0,00 values (Hope it makes sense....)

SELECT tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate] AS [Duty Pay], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors], Sum(tblAdditionalAllowances.AmmountAdditionalAllowances) AS AdditionalAllowances, qryHotacBill.HotacBill, [TAFB]*[DutyPayRate]+[AmmountAdditionalAllowances] AS TOT
FROM ((tblTrip LEFT JOIN qryFlightTimeOperation ON tblTrip.IDTrip = qryFlightTimeOperation.IDTrip) LEFT JOIN tblAdditionalAllowances ON tblTrip.IDTrip = tblAdditionalAllowances.IDTrip) LEFT JOIN qryHotacBill ON tblTrip.IDTrip = qryHotacBill.IDTrip
GROUP BY tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors], qryHotacBill.HotacBill, [TAFB]*[DutyPayRate]+[AmmountAdditionalAllowances]
ORDER BY tblTrip.TripDate;

 

[Uncle Gizmo]

The query below has a field named TOT, for some reason it only returns the value where both fields have a value.
On the other side I have a form linked to the query and it looks much neater displaying no values on the form rather than a long list of 0,00 values (Hope it makes sense....)

SELECT tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate] AS [Duty Pay], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors], Sum(tblAdditionalAllowances.AmmountAdditionalAllowances) AS AdditionalAllowances, qryHotacBill.HotacBill, [TAFB]*[DutyPayRate]+[AmmountAdditionalAllowances] AS TOT
FROM ((tblTrip LEFT JOIN qryFlightTimeOperation ON tblTrip.IDTrip = qryFlightTimeOperation.IDTrip) LEFT JOIN tblAdditionalAllowances ON tblTrip.IDTrip = tblAdditionalAllowances.IDTrip) LEFT JOIN qryHotacBill ON tblTrip.IDTrip = qryHotacBill.IDTrip
GROUP BY tblTrip.IDTrip, tblTrip.TripDate, tblTrip.NDays, tblTrip.TAFB, tblTrip.DutyPayRate, [TAFB]*[DutyPayRate], tblTrip.PSR, tblTrip.DutyType, qryFlightTimeOperation.[Tot Flying Time], qryFlightTimeOperation.[Tot Sectors], qryFlightTimeOperation.[Non Operating Sectors], qryFlightTimeOperation.[Operating Sectors], qryHotacBill.HotacBill, [TAFB]*[DutyPayRate]+[AmmountAdditionalAllowances]
ORDER BY tblTrip.TripDate;

No, I don't understand....

Sent from my SM-G925F using Tapatalk

 

[plog]

Agree with Gizmo, and here's why--you generically reference things I have no frame of reference for:

it only returns the value where both fields have a value

Huh? What 'both fields'? Your query has at least 10 fields in it, both means nothing to me.

On the other side

'Other side'? Of what? Queries aren't a physical object, it doesn't have sides.

a form linked to the query

You mean the form's control source is the query you posted?

You posted this in the query sub-forum, you pasted query code in, so is your issue with a form or a query? Perhaps you can demonstrate your issue with data or a screenshot.

 

[JHB]

The query below has a field named TOT, for some reason it only returns the value where both fields have a value.

Maybe it helps to surround the fields in question by NZ.
http://www.consultdmw.com/access-null-values.htm

 

[mtagliaferri]

Apologies for the lack of information....
Hopefully the picture attached explain more than words can do!
the "Duty Pay" Columns will always have a value, "Additional Allowances" and "Hotel Bill" may or may not.
The columns "TOT" should return the same value as per "Duty Pay" or the Sum of "Duty Pay" + "Additional Allowances"
However I rather have an empty field rather than a visible 0.00 value as it makes the form look neater..
Hope this makes sense

 

[Frothingslosh]

I believe you're running afoul of NULL; any mathematical calculation involving a null returns null.

Also, you said that TOT should be Duty Pay + Additional Allowances, but your SQL statement is

[TAFB]*[DutyPayRate]+[AmmountAdditionalAllowances]

which will actually multiply DutyPayRate and TAFB before adding AmmountAdditionalAllowances (also, that spelling error is killing me).

Regardless, like JHB said, use the NZ function on AmmountAdditionalAllowances, resulting in either

[TAFB]*[DutyPayRate]+Nz([AmmountAdditionalAllowances],0)

if you intended to multiply DutyPayRate by TAFB, or

[DutyPayRate]+Nz([AmmountAdditionalAllowances])

if TOT really is supposed to just be DutyPayRate plus AmmountAdditionalAllowances.

Also, if there is any chance at all that TAFB or DutyPayRate will be null, then you should Nz them as well in that calculation.