By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

Update some columns using code

P: 86
Dear all,

Is there a way I can update all of the numbers in a certain column of a certain table that are <0 to NULL using access VBA code?

Thanx a lot
Apr 22 '09 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 489
Using SQL you could do it like this.

Expand|Select|Wrap|Line Numbers
  1. Dim stSQL as string
  2. stSQL = "UPDATE YourTableName SET YourTableName.YourFieldName = Null WHERE (((YourTableName.YourFieldName)<0));"
  3. DoCmd.SetWarnings False
  4. DoCmd.RunSql stSQL
  5. DoCmd.SetWarnings True
Change YourTableName and YourFieldName to the appropiate names of your table and field you wish use.

The SetWarnings are to stop the warning messages you get when running an update query and to turn them back on again after the update has run. If you wish to have the warnings present, just remove lines 3 and 5 from the code.
Apr 22 '09 #2

P: 86
Thank you so much fo your kind support.

If I get it right, I can convert every query into code this way...

Does it make them run faster???????????????
Apr 23 '09 #3

Expert Mod 15k+
P: 31,709
Absolutely. You can also do more flexible things, putting known values into the string to make a SQL query bespoke for your current requirements. Every QueryDef also contains the SQL associated with it too. This can be used sometimes as a start point if you simply want to change a (some) known item(s).

There are times though (see below) when it is not too good an idea to use SQL string in place of a saved QueryDef.

NB. For maintenance purposes, it is clearer what a QueryDef is doing than some string hidden in code somewhere in one of your modules.
Slower actually.

A QueryDef is saved with some optimisation info (at least after it's been run the first time). A SQL string is not optimised, and needs this step to be performed before getting down to the work it does.

I would say that it's rare for this to make such a difference that it would put someone off though.
Apr 23 '09 #4

Expert 100+
P: 489
You're quite welcome. See NeoPa's post for an answer to your question.
Apr 23 '09 #5

P: 86
Thank you so much for your kind support.

Now that I am told that Queries run faster than their equivalent VBA codes, I would like to ask this question also:

Is there a way to update all records in more than one column of a table that are <0 to NULL using a single query?
Apr 25 '09 #6

Expert Mod 15k+
P: 31,709
Yes there is Ali.

The following code is a template, so will need to be amended for your requirements, but it goes something like :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Table]
  3. SET    [FieldX]=IIf([FieldX]<0,Null,[FieldX]),
  4.        [FieldY]=IIf([FieldY]<0,Null,[FieldY]),
  5.        [FieldZ]=IIf([FieldZ]<0,Null,[FieldZ]),
  6.        ...
  8. WHERE  ([FieldX]<0),
  9.    OR  ([FieldY]<0),
  10.    OR  ([FieldZ]<0),
  11.    OR  ...
Apr 25 '09 #7

P: 86
Thank you so much for your help.

I will check it this afternoon.

Thanx a looooooooooooooooooooooot.
Apr 26 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.