Calculation into a column

[phineas629]

Hi all,

I'm updating fall pay dates which is half of the award total. I'm also entering a date. This is all going into the same table.


However, I'm getting '0' in the table for everything when performing the following query. The data types are right also.

Any ideas?

  

strSql = " UPDATE Award SET Award.Amount_FallPay = Award.AWARD_Total/2," & _
                " Award.AWRD_AMOUNT_FallPayDate = #" & Me.txtFallPaymentDate & " #" & _
                " WHERE Award.AWARD_Ready = True and AWARD_Season ='Fall'"
        CurrentDb.Execute strSql

[June7]

Saving calculated data is often bad idea. Is the FallPay always half AWARD_Total? Why saving such simple calculated value? Award_Total is total of what - several fields of same record?

Provide example data for the Award table.

[phineas629]

The total is always split in half. I want to show it on a user form. That amount will also be going into a linked table in another database where it is normalized.

client	award_total	fallpay	springpay
johnny	1000	500	500
sue	500	250	250

Saving calculated data is often bad idea. Is the FallPay always half AWARD_Total? Why saving such simple calculated value? Award_Total is total of what - several fields of same record?

Provide example data for the Award table.

[June7]

How is the Award_Total field populated? Is it by data entry?

I really can't see reason for the UPDATE to fail.

Next step is to provide db for analysis. Follow instructions at bottom of my post.

[phineas629]

Thank you for checking my syntax. I needed so bad for someone to confirm. I compacted and then redid my odbc connection and now it works. I had a sql backend, the database and then the front-end interface. not sure where it went wrong. The code went through but displayed all zeros. any ideas what could've caused this?

[June7]

No idea. Never used SQL backend.