Dsum not showing data if currency amount is a negative

[GinaStar]

Am trying to do a Payroll Year-To-Date report. The report is showing everything except the expenses properly. The expense column can be a positive or negative cash value. The negative cash value can be for assorted deductions such as drug testing, tires that they have charged, etc. The positive cash value in the expence column would be for things like payroll adjustments where a price has increased, etc. The problem is, my DSum is not showing any 'negative' expenses like ($33.00) for a drug test, only the 'positive' ones like $14.23 for a job rate increase. The formula I am using is pulling from a query called ContractYTD and is as listed:

=DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Labor'&quot

Note: this is basically the same formula I am using for Health and Life, except the [strDeductType] value is either 'Health' or 'Life' based on which total I am looking for. Health and Life are working properly, but the values in them show as a positive number.

the text box I am using is formatted for currency.

the sql statement for the query is:
SELECT tblConpay.guidTKNO, tblDataTruckInfo.strTKOwner, tblConpay.datPayPeriod, tblConpay.guidDRID, tblDataDriver.strNAME, tblConpay.curHaul, tblConpay.guidPER, tblConpay.dblTicketQuan, tblConpay.curBroker, [dblTicketQuan]*[curHaul] AS gross, tblConpay.strDeductType, tblConpay.curDeductAmt, tblConpay.strDeductDesc
FROM tblDataTruckInfo INNER JOIN (tblDataDriver INNER JOIN tblConpay ON tblDataDriver.guidDRID = tblConpay.guidDRID) ON tblDataTruckInfo.guidTKNO = tblConpay.guidTKNO
ORDER BY tblConpay.guidTKNO, tblConpay.datPayPeriod;

all of the info is showing in the query, but when I go to see the report, it's not showing any data in that sum field if the value is a negative. I've never had this to come up before, so I am quite confused. The detail section is hidden, the subtotals by payperiod shows and the subtotals by truck owner shows.

The final report should look like this:

truck owner 1 Payperiod 1
Gross     Brokerage   Health  Life   Expense       Net
$2,000.00    $200.00  $150.00  $0.00 ($50.00)   $1,600.00

truck owner 1 Payperiod 2
Gross     Brokerage   Health  Life   Expense       Net
$3,000.00    $300.00    $0.00  $0.00  $15.00    $2,715.00

Grand Total Truck Owner 1
Gross     Brokerage   Health  Life   Expense       Net
$5,000.00    $500.00  $150.00  $0.00 ($35.00)   $4,315.00

Instead, it is looking like this:

truck owner 1 Payperiod 1
Gross     Brokerage   Health  Life   Expense       Net
$2,000.00    $200.00  $150.00  $0.00   $0.00    $1,600.00

truck owner 1 Payperiod 2
Gross     Brokerage   Health  Life   Expense       Net
$3,000.00    $300.00    $0.00  $0.00  $15.00    $2,715.00

Grand Total Truck Owner 1
Gross     Brokerage   Health  Life   Expense       Net
$5,000.00    $500.00  $150.00  $0.00   $0.00    $4,315.00

formula for Gross is =Sum([gross])

formula for Brokerage is =Sum([curBroker])

actual formula for health is =IIf(IsNull(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Health'&quot),0,Nz(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Health'&quot))

actual formula for life is =IIf(IsNull(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Life'&quot),0,Nz(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Life'&quot))

I have simplified the expense formula until I get it to working and then will add my IsNull and NZ to it

actual formula for net is =Sum([gross])-Sum([curBroker])- IIf(IsNull(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Health'&quot),0,Nz(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Health'&quot))
- IIf(IsNull(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Life'&quot),0,Nz(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=Reports!ContractYTD.[strTKOwner] and [strDeductType]='Life'&quot))
+ expense

Everything is working properly except the expenses. I am trying to get this done in order to attach the report with our contractors 1099's at the end of the year so any advice would be appreciated.

Regina

 

[PaulBricker]

Regina, where are these values being calculated? In a Group Footer or someplace else?

Paul

 

[PaulBricker]

Some other stuff to check.
1. You don't enclose your field names in ""'s and you don't reference your Reports object using proper syntax

=IIf(IsNull(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=[blue]'" & Reports!ContractYTD.[strTKOwner] & "'[/blue] and [strDeductType]='Life'&quot),0,Nz(DSum([curDeductAmt],'ContractYTD',"[strTKOwner]=[blue]'" & Reports!ContractYTD.[strTKOwner] & "'[/blue] And [strDeductType]='Life'&quot))

2. You check for nulls twice in your expression IIf(IsNull(DSum( and then Nz(DSum( Something is unusual about that because it should always pick up the null value in the first arugment and never need the Nz function. I would just use

=IIf(IsNull(DSum("[curDeductAmt]","ContractYTD","[strTKOwner]=[blue]'" & Reports!ContractYTD.[strTKOwner] & "'[/blue] and [strDeductType]='Life'&quot),0,DSum("[curDeductAmt]","ContractYTD","[strTKOwner]=[blue]'" & Reports!ContractYTD.[strTKOwner] & "'[/blue] And [strDeductType]='Life'&quot)

Paul

 

[GinaStar]

the reason that I'm having to check for nulls twice is because when a contractor has no values in that column (ie: health insurance)then just the nz was leaving a blank field. when I used the Iff(isnull()) then it was also leaving just a blank field for the empty columns on ones who had data for one or two payperiods during the year. When I combined the two, it then put $0.00 in the column even if the contractor had no values for the whole year and put $0.00 in the column if they had a value for one pay period but not the whole column. (sometimes, I think my computer hates me)

I will try enclosing the field names and correcting my normalization. I'm still learning the program and have a tendency to forget how to reference different things, and my reference book that I use for that is at the office. I do want to thank you for your help and I will let you know how it goes.