Null Field question

[Kurt6905]

Hi,

This is probably a beginner question for this site. The following program will not work because I do not actually have "0" in the Site2 and Site3 columns; if there is no 1, 2 or 3, there is no value.

What do I use in place of "=0"? Thanks,

Curt



Function DoSiteConversion()
Const table As String = "[2008 Golf Raw Table]"
'make sure CropActive columns are created before running

DoCmd.RunSQL "update " & table & " set [Site1Active] = [Active] where [Site2]=0"

DoCmd.RunSQL "update " & table & " set [Site1Active] = [Active]* 0.5 where [Site2]>0 and [Site3]=0"

DoCmd.RunSQL "update " & table & " set [Site2Active] = [Active]* 0.5 where [Site2]>0 and [Site3]=0"

DoCmd.RunSQL "update " & table & " set [Site1Active] = [Active]* 0.333 where [Site3]>0"

DoCmd.RunSQL "update " & table & " set [Site2Active] = [Active]* 0.333 where [Site3]>0"

DoCmd.RunSQL "update " & table & " set [Site3Active] = [Active]* 0.333 where [Site3]>0"

End Function

 

[dhookom]

I would question the normalization of your table. However, you can convert a Null to 0 using the Nz() function.

DoCmd.RunSQL "update " & table & " set [Site1Active] = [Active] where Nz([Site2],0)=0"

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd use something like this:

DoCmd.RunSQL "UPDATE " & table & " SET Site1Active=Active,Site2Active=Null,Site3Active=Null WHERE Site2 Is Null"
DoCmd.RunSQL "UPDATE " & table & " SET Site1Active=Active/2,Site2Active=Active/2,Site3Active=Null WHERE Site2>0 AND Site3 Is Null"
DoCmd.RunSQL "UPDATE " & table & " SET Site1Active=Active/3,Site2Active=Active/3,Site3Active=Active/3 WHERE Site3>0"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886