Increase the value of a field by X amount

[RockNRoll]

Access VBA Gurus,

I am in dire need of some assistance.

I need to write some code that will loop through each record in my
"tbl_Employees" table and increase a currency field within that record by
$1,300.

Thank you in advance for your help.

Regards,

-Dave

 

[Cheryl Fischer]

You can paste the following in the SQL View of a new query:

UPDATE [MyTable] SET [MyTable].MyAmount = NZ([MyAmount],0)+1300

or, if you want to do your update from a command button, you can use the
following:

CurrentDB.Execute "UPDATE [MyTable] SET [MyTable].MyAmount =
NZ([MyAmount],0)+1300", dbFailOnError

hth,

 

[RockNRoll]

Thanks, that helps a lot.

What if I want to increase the value for each record based on the value of
another field in my table? So, each field could be increasing by a
different amount.

Thanks,

You can paste the following in the SQL View of a new query:

UPDATE [MyTable] SET [MyTable].MyAmount = NZ([MyAmount],0)+1300

or, if you want to do your update from a command button, you can use the
following:

CurrentDB.Execute "UPDATE [MyTable] SET [MyTable].MyAmount =
NZ([MyAmount],0)+1300", dbFailOnError

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Access VBA Gurus,

I am in dire need of some assistance.

I need to write some code that will loop through each record in my
"tbl_Employees" table and increase a currency field within that record by
$1,300.

Thank you in advance for your help.

Regards,

-Dave

 

[RockNRoll]

I solved the problem myself - with an update query. Turns out I didn't need
any code.

 

[Tim Ferguson]

What if I want to increase the value for each record based on the
value of another field in my table? So, each field could be
increasing by a different amount.

update mytable
set totalsofar = totalsofar + sumtobeadded
where totalsofar is not null
and sumtobeadded is not null

But this is starting to sound like a Serious Design Flaw...


Tim F