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]

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]

@Alireza355
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.
@Alireza355
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]

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

[Alireza355]

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]

Thank you so much for your help.

I will check it this afternoon.

Thanx a looooooooooooooooooooooot.