Update query giving blank results - Access 2003

[Ann Scharpf]

I am trying to do an update query. I have a payroll table that has pertinent
dates, an annual salary etc. I have a standard variables table that has info
like the cost of living allowance increase. (US fed employees, so I have to
calculate the salary for Oct - Dec, then apply the COLA for Jan - Sep.)

If I create a make table query, my formulas work fine and calculate the
proper salary amounts for each part of the year. But, if I try to make this
an update table, my datasheet view shows the calculated field names but all
the values are null.

Here is the SQL view of my query. I'd greatly appreciate any help you can
give me.

UPDATE StandardVariables, [Civilian Payroll - Update Test Version] SET
[Civilian Payroll - Update Test Version].CY1Salary =
DateDiff("d",[FYStart],[CY1End])/365*[Salary], [Civilian Payroll - Update
Test Version].CY2Salary = DateDiff("d",[CY2Start],[FYEnd])/365*[Salary],
[Civilian Payroll - Update Test Version].BenefitsCost =
([CY1Salary]+[CY2Salary])*[BenefitsPercent]
WHERE ((([Civilian Payroll - Update Test Version].CY1Salary) Is Null) AND
(([Civilian Payroll - Update Test Version].CY2Salary) Is Null) AND
(([Civilian Payroll - Update Test Version].BenefitsCost) Is Null) AND
(([CY1Salary]+[CY2Salary]+[BenefitsCost]+[Bonus]) Is Null));

 

[SteveS]

I am trying to do an update query. I have a payroll table that has pertinent
dates, an annual salary etc. I have a standard variables table that has info
like the cost of living allowance increase. (US fed employees, so I have to
calculate the salary for Oct - Dec, then apply the COLA for Jan - Sep.)

If I create a make table query, my formulas work fine and calculate the
proper salary amounts for each part of the year. But, if I try to make this
an update table, my datasheet view shows the calculated field names but all
the values are null.

Here is the SQL view of my query. I'd greatly appreciate any help you can
give me.

UPDATE StandardVariables, [Civilian Payroll - Update Test Version] SET
[Civilian Payroll - Update Test Version].CY1Salary =
DateDiff("d",[FYStart],[CY1End])/365*[Salary], [Civilian Payroll - Update
Test Version].CY2Salary = DateDiff("d",[CY2Start],[FYEnd])/365*[Salary],
[Civilian Payroll - Update Test Version].BenefitsCost =
([CY1Salary]+[CY2Salary])*[BenefitsPercent]
WHERE ((([Civilian Payroll - Update Test Version].CY1Salary) Is Null) AND
(([Civilian Payroll - Update Test Version].CY2Salary) Is Null) AND
(([Civilian Payroll - Update Test Version].BenefitsCost) Is Null) AND
(([CY1Salary]+[CY2Salary]+[BenefitsCost]+[Bonus]) Is Null));

Ann,

It is returning the correct data. In the WHERE clause, you are selecting NULLs!
Any calculation with a NULL returns a NULL.


HTH

 

[SteveS]

I am trying to do an update query. I have a payroll table that has
pertinent dates, an annual salary etc. I have a standard variables
table that has info like the cost of living allowance increase. (US
fed employees, so I have to calculate the salary for Oct - Dec, then
apply the COLA for Jan - Sep.)

If I create a make table query, my formulas work fine and calculate
the proper salary amounts for each part of the year. But, if I try to
make this an update table, my datasheet view shows the calculated
field names but all the values are null.

Here is the SQL view of my query. I'd greatly appreciate any help you
can give me.

UPDATE StandardVariables, [Civilian Payroll - Update Test Version] SET
[Civilian Payroll - Update Test Version].CY1Salary =
DateDiff("d",[FYStart],[CY1End])/365*[Salary], [Civilian Payroll -
Update Test Version].CY2Salary =
DateDiff("d",[CY2Start],[FYEnd])/365*[Salary], [Civilian Payroll -
Update Test Version].BenefitsCost =
([CY1Salary]+[CY2Salary])*[BenefitsPercent]
WHERE ((([Civilian Payroll - Update Test Version].CY1Salary) Is Null)
AND (([Civilian Payroll - Update Test Version].CY2Salary) Is Null) AND
(([Civilian Payroll - Update Test Version].BenefitsCost) Is Null) AND
(([CY1Salary]+[CY2Salary]+[BenefitsCost]+[Bonus]) Is Null));

Ann,

It is returning the correct data. In the WHERE clause, you are selecting
NULLs! Any calculation with a NULL returns a NULL.


HTH

Oops, somehow sent it before I was thru....

For a record where CY1Salary IS NULL, CY2Salary IS NULL and BenefitsCost IS
NULL, BenefitsCost will be NULL because at the time of the update, CY1Salary
and CY2Salary are still NULL.

Maybe you should break the Update Query into three update queries.

What is "StandardVariables"? Does the update work without "StandardVariables"
in the query?