Result of Expression Isn't Showing Up in Query

  • Thread starter Thread starter XcentricRecluse
  • Start date Start date
X

XcentricRecluse

I've entered the following expression in a query:

TotalPay: ([Pay/CH]*[CreditHours])+[TravelAllowance]+[PayAdjustment]

The query doesn't return the resulting calculation. What am I doing wrong?
 
Yes, I have.
--
Chris


Proko said:
Have you checked the "show" checkbox in the query grid for the TotalPay field?

XcentricRecluse said:
I've entered the following expression in a query:

TotalPay: ([Pay/CH]*[CreditHours])+[TravelAllowance]+[PayAdjustment]

The query doesn't return the resulting calculation. What am I doing wrong?
Click to expand...
Click to expand...
 
The field [Pay/CH] is more likely the cause. Rename it to something without
the "/" symbol in the underlying table.

XcentricRecluse said:
Yes, I have.
--
Chris


Proko said:
Have you checked the "show" checkbox in the query grid for the TotalPay field?

XcentricRecluse said:
I've entered the following expression in a query:

TotalPay: ([Pay/CH]*[CreditHours])+[TravelAllowance]+[PayAdjustment]

The query doesn't return the resulting calculation. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Do all four fields have a value? If any one of them is null (blank)
then the answer will be null. You can handle that by forcing a null
value to zero with the NZ function or by using an IIF expression

TotalPay: (Nz([Pay/CH],0) * Nz([CreditHours],0)) +
NZ([TravelAllowance],0) + NZ([PayAdjustment],0)

An alternative is to use something like the following.
TotalPay: (IIF([Pay/CH] is Null, 0,[Pay/Ch])
* IIF([CreditHours] is Null, 0, [CreditHours]))
+ IIF([TravelAllowance] Is Null,0,[TravelAllowance])
+ IIF([PayAdjustment] is Null, 0, [PayAdjustment])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Unfortunately, I simplified the expression even further than you suggested,
leaving only [TravelAllowance]+[PayAdjusment]. The resulting sum is still
not showing up in the query. I'm baffled.
--
Chris


Proko said:
The field [Pay/CH] is more likely the cause. Rename it to something without
the "/" symbol in the underlying table.

XcentricRecluse said:
Yes, I have.
--
Chris


Proko said:
Have you checked the "show" checkbox in the query grid for the TotalPay field?

:

I've entered the following expression in a query:

TotalPay: ([Pay/CH]*[CreditHours])+[TravelAllowance]+[PayAdjustment]

The query doesn't return the resulting calculation. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Bingo!

Thank you very much for your assistance!
--
Chris


John Spencer said:
Do all four fields have a value? If any one of them is null (blank)
then the answer will be null. You can handle that by forcing a null
value to zero with the NZ function or by using an IIF expression

TotalPay: (Nz([Pay/CH],0) * Nz([CreditHours],0)) +
NZ([TravelAllowance],0) + NZ([PayAdjustment],0)

An alternative is to use something like the following.
TotalPay: (IIF([Pay/CH] is Null, 0,[Pay/Ch])
* IIF([CreditHours] is Null, 0, [CreditHours]))
+ IIF([TravelAllowance] Is Null,0,[TravelAllowance])
+ IIF([PayAdjustment] is Null, 0, [PayAdjustment])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I've entered the following expression in a query:

TotalPay: ([Pay/CH]*[CreditHours])+[TravelAllowance]+[PayAdjustment]

The query doesn't return the resulting calculation. What am I doing wrong?
Click to expand...
Click to expand...
 
Thank you for your help, but your suggestion didn't help. Mr. Spencer's
suggestion which follows solved the problem.


--
Chris


Proko said:
The field [Pay/CH] is more likely the cause. Rename it to something without
the "/" symbol in the underlying table.

XcentricRecluse said:
Yes, I have.
--
Chris


Proko said:
Have you checked the "show" checkbox in the query grid for the TotalPay field?

:

I've entered the following expression in a query:

TotalPay: ([Pay/CH]*[CreditHours])+[TravelAllowance]+[PayAdjustment]

The query doesn't return the resulting calculation. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top