Update some columns using code

[Alireza355]

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

[DonRayner]

Using SQL you could do it like this.

Dim stSQL as string
stSQL = "UPDATE YourTableName SET YourTableName.YourFieldName = Null WHERE (((YourTableName.YourFieldName)<0));"
DoCmd.SetWarnings False
DoCmd.RunSql stSQL
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.

[Alireza355]

Thanx a lot

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????????? ??????

[NeoPa]

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

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.

Does it make them run faster????????? ??????

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.

[DonRayner]

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????????? ??????

You're quite welcome. See NeoPa's post for an answer to your question.

[Alireza355]

Thanx a lot

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?

[NeoPa]

Yes there is Ali.

The following code is a template, so will need to be amended for your requirements, but it goes something like :

UPDATE [Table]

SET    [FieldX]=IIf([FieldX]<0,Null,[FieldX]),
       [FieldY]=IIf([FieldY]<0,Null,[FieldY]),
       [FieldZ]=IIf([FieldZ]<0,Null,[FieldZ]),
       ...

WHERE  ([FieldX]<0),
   OR  ([FieldY]<0),
   OR  ([FieldZ]<0),
   OR  ...

[Alireza355]

Thanx a lot

Thank you so much for your help.

I will check it this afternoon.

Thanx a loooooooooooooo ooooooooot.