A
Alex
I have some problem in the following query in the WHERE criteria:
SELECT tblAdjustedData.PayrollPunchIn AS [Day],
IIf([OT1Hrs]>0,[OT1Hrs],IIf([PayrollWorkedTime]-10>0 And
[PayrollWorkedTime]-10<=1 And [OT2Hrs]=0,[PayrollWorkedTime]-10,0)) AS
OTHrsRate1,
IIf([OT2Hrs]>0,[OT2Hrs],IIf([PayrollWorkedTime]-10>1,[PayrollWorkedTime]-10,0))
AS OTHrsRate2, tblAdjustedData.PayrollWorkedTime,
tblAdjustedData.RateToBeUsed, [RateToBeUsed]*[OTHrsRate1]*0.5 AS PayOT1,
[RateToBeUsed]*[OTHrsRate2] AS PayOT2, [PayOT1]+[PayOT2] AS TotalPay,
[PayrollWorkedTime]*[RateToBeUsed] AS RegPay, tblAdjustedData.AdditionCode
FROM tblAdjustedData
WHERE
(((tblAdjustedData.PayrollPunchIn)>=[forms]![frmLabourAnalysisPeriod]![cboStartDay]
And
(tblAdjustedData.PayrollPunchIn)<=[forms]![frmLabourAnalysisPeriod]![cboEndDay]+1)
AND ((tblAdjustedData.AdditionCode)<>"L" And
(tblAdjustedData.AdditionCode)<>"P" And (tblAdjustedData.AdditionCode)<>"W"));
I'm doing the calculation of the sum of PayrollWorkedTime field:
Without any criteria on the AdditionCode it calculates 6,810.5 (which is
correct).
With shown criteria for AdditionCode it gives 6,645.25.
But, if I'm entering AdditionCode only = "L", I have 65.5; AdditionCode only
= "P", I have 88; and "W" - 0. So, it should be 6,657 (6,810.5 - 65.5 - 88 -
0), it's a correct number. The difference is 11.75.
If I'm entering only <> "L", it gives me 6,733.25 instead of 6,810.5 - 65.5
= 6,745 (the same 11.75 difference). If I'm entering only <> "P", it again
gives the same 11.75 difference.
I've checked the result of the query without WHERE in Excel, calculating "L"
and "P" separately there. Everything is correct. It should be 6,810.5 for
all, 65.5 for "L", and 88 for "P". But, with the criteria <>"L" And <> "P"
it's not working in Access.
Could anybody clarify how it's possible?
Thanks
SELECT tblAdjustedData.PayrollPunchIn AS [Day],
IIf([OT1Hrs]>0,[OT1Hrs],IIf([PayrollWorkedTime]-10>0 And
[PayrollWorkedTime]-10<=1 And [OT2Hrs]=0,[PayrollWorkedTime]-10,0)) AS
OTHrsRate1,
IIf([OT2Hrs]>0,[OT2Hrs],IIf([PayrollWorkedTime]-10>1,[PayrollWorkedTime]-10,0))
AS OTHrsRate2, tblAdjustedData.PayrollWorkedTime,
tblAdjustedData.RateToBeUsed, [RateToBeUsed]*[OTHrsRate1]*0.5 AS PayOT1,
[RateToBeUsed]*[OTHrsRate2] AS PayOT2, [PayOT1]+[PayOT2] AS TotalPay,
[PayrollWorkedTime]*[RateToBeUsed] AS RegPay, tblAdjustedData.AdditionCode
FROM tblAdjustedData
WHERE
(((tblAdjustedData.PayrollPunchIn)>=[forms]![frmLabourAnalysisPeriod]![cboStartDay]
And
(tblAdjustedData.PayrollPunchIn)<=[forms]![frmLabourAnalysisPeriod]![cboEndDay]+1)
AND ((tblAdjustedData.AdditionCode)<>"L" And
(tblAdjustedData.AdditionCode)<>"P" And (tblAdjustedData.AdditionCode)<>"W"));
I'm doing the calculation of the sum of PayrollWorkedTime field:
Without any criteria on the AdditionCode it calculates 6,810.5 (which is
correct).
With shown criteria for AdditionCode it gives 6,645.25.
But, if I'm entering AdditionCode only = "L", I have 65.5; AdditionCode only
= "P", I have 88; and "W" - 0. So, it should be 6,657 (6,810.5 - 65.5 - 88 -
0), it's a correct number. The difference is 11.75.
If I'm entering only <> "L", it gives me 6,733.25 instead of 6,810.5 - 65.5
= 6,745 (the same 11.75 difference). If I'm entering only <> "P", it again
gives the same 11.75 difference.
I've checked the result of the query without WHERE in Excel, calculating "L"
and "P" separately there. Everything is correct. It should be 6,810.5 for
all, 65.5 for "L", and 88 for "P". But, with the criteria <>"L" And <> "P"
it's not working in Access.
Could anybody clarify how it's possible?
Thanks