Copying records that contain nulls

B

bcarter97

I have a access 2002 application that needs to copy existing records in
a database and insert then into the same database as new records with
some updated fields. I have it all working but there are several fields
that may or may not contain nulls and i have to copy them all to the
new record. Here it the code I am using but it keeps failing due to
the database validation rule.


SQL = "update providers " & _
"set county1 ='" & rs(24) & "',county2 ='" & rs(25) &
"',county3 ='" & rs(26) & "',county4 ='" & rs(27) & "',county5 ='" &
rs(28) & "',county6 ='" & rs(29) & "',county7 ='" & rs(30) & "',county8
='" & rs(31) & "',county9 ='" & rs(32) & _
"',county10 ='" & rs(33) & "',county11 ='" & rs(34) &
"',county12 ='" & rs(35) & "',county13 ='" & rs(36) & "',county14 ='" &
rs(37) & "',county15 ='" & rs(38) & "',county16 ='" & rs(39) &
"',county17 ='" & rs(40) & "',county18 ='" & rs(41) & _
"',county19 ='" & rs(42) & "',county20 ='" & rs(43) &
"',county21 ='" & rs(44) & "',county22 ='" & rs(45) & "',county23 ='" &
rs(46) & "',county24 ='" & rs(47) & "',county25 ='" & rs(48) &
"',county26 ='" & rs(49) & "',county27 ='" & rs(50) & _
"',county28 ='" & rs(51) & "',county29 ='" & rs(52) &
"',county30 ='" & rs(53) & "',county31 ='" & rs(54) & "',county32 ='" &
rs(55) & "',county33 ='" & rs(56) & "',county34 ='" & rs(57) &
"',county35 ='" & rs(58) & "',county36 ='" & rs(59) & _
"',county37 ='" & rs(60) & "',county38 ='" & rs(61) &
"',county39 ='" & rs(62) & "',county40 ='" & rs(63) & "',county41 ='" &
rs(64) & "',county42 ='" & rs(65) & "',county43 ='" & rs(66) &
"',county44 ='" & rs(67) & "',county45 ='" & rs(68) & _
"',county46 ='" & rs(69) & "',county47 ='" & rs(70) &
"',county48 ='" & rs(71) & "',county49 ='" & rs(72) & "',county50 ='" &
rs(73) & "',county51 ='" & rs(74) & "',county52 ='" & rs(75) &
"',county53 ='" & rs(76) & "',county54 ='" & rs(77) & _
"',county55 ='" & rs(78) & "',county56 ='" & rs(79) &
"',county57 ='" & rs(80) & "',county58 ='" & rs(81) & "',county59 ='" &
rs(82) & "',county60 ='" & rs(83) & "',county61 ='" & rs(84) &
"',county62 ='" & rs(85) & "',county63 ='" & rs(86) & _
"',county64 ='" & rs(87) & "',county65 ='" & rs(88) &
"',county66 ='" & rs(89) & "',county67 ='" & rs(90) & _
"' where (providers.[provider number] = '" & Temp & _
"');"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

validation rule on database is : ='99' Or (>'0' And <'68') Or Is Null

The first field always has data but the rest are normally null with
very few exceptions. Problem is I don't know when the fields are null
so I have to copy them all. As I step through the code, I verified at
runtime that the fields are Null but they will not pass the validation
rule on the table. I put the sql in an access sql view and it only
works if i put null as the value and not the '' sting.

Thanks for your time.
 
A

Allen Browne

You want the literal word Null without the quote mark delimiters if the
field is null.

The syntax will look like this:
"UPDATE providers SET county1 = " & IIf(IsNull(rs(24)), "Null", """" &
rs(24) & """") & ", ...

Of course, the core of the difficulty here is the non-normalized field
structure. Whenever you have repeating fields like County1, County2, etc, it
alway means you need to have a related table where there are many records
instead of having many fields. Perhaps you are importing data that arrives
in this unnormalized format, but it would be worthwhile setting up a
properly designed tables for storing it in, i.e. appending a record to a
related table for each record where CountyXX is not null.

You are probably also aware that you don't get any error message if the data
does not update correctly when you turn SetWarnings off. For an alternative,
see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a access 2002 application that needs to copy existing records in
a database and insert then into the same database as new records with
some updated fields. I have it all working but there are several fields
that may or may not contain nulls and i have to copy them all to the
new record. Here it the code I am using but it keeps failing due to
the database validation rule.


SQL = "update providers " & _
"set county1 ='" & rs(24) & "',county2 ='" & rs(25) &
"',county3 ='" & rs(26) & "',county4 ='" & rs(27) & "',county5 ='" &
rs(28) & "',county6 ='" & rs(29) & "',county7 ='" & rs(30) & "',county8
='" & rs(31) & "',county9 ='" & rs(32) & _
"',county10 ='" & rs(33) & "',county11 ='" & rs(34) &
"',county12 ='" & rs(35) & "',county13 ='" & rs(36) & "',county14 ='" &
rs(37) & "',county15 ='" & rs(38) & "',county16 ='" & rs(39) &
"',county17 ='" & rs(40) & "',county18 ='" & rs(41) & _
"',county19 ='" & rs(42) & "',county20 ='" & rs(43) &
"',county21 ='" & rs(44) & "',county22 ='" & rs(45) & "',county23 ='" &
rs(46) & "',county24 ='" & rs(47) & "',county25 ='" & rs(48) &
"',county26 ='" & rs(49) & "',county27 ='" & rs(50) & _
"',county28 ='" & rs(51) & "',county29 ='" & rs(52) &
"',county30 ='" & rs(53) & "',county31 ='" & rs(54) & "',county32 ='" &
rs(55) & "',county33 ='" & rs(56) & "',county34 ='" & rs(57) &
"',county35 ='" & rs(58) & "',county36 ='" & rs(59) & _
"',county37 ='" & rs(60) & "',county38 ='" & rs(61) &
"',county39 ='" & rs(62) & "',county40 ='" & rs(63) & "',county41 ='" &
rs(64) & "',county42 ='" & rs(65) & "',county43 ='" & rs(66) &
"',county44 ='" & rs(67) & "',county45 ='" & rs(68) & _
"',county46 ='" & rs(69) & "',county47 ='" & rs(70) &
"',county48 ='" & rs(71) & "',county49 ='" & rs(72) & "',county50 ='" &
rs(73) & "',county51 ='" & rs(74) & "',county52 ='" & rs(75) &
"',county53 ='" & rs(76) & "',county54 ='" & rs(77) & _
"',county55 ='" & rs(78) & "',county56 ='" & rs(79) &
"',county57 ='" & rs(80) & "',county58 ='" & rs(81) & "',county59 ='" &
rs(82) & "',county60 ='" & rs(83) & "',county61 ='" & rs(84) &
"',county62 ='" & rs(85) & "',county63 ='" & rs(86) & _
"',county64 ='" & rs(87) & "',county65 ='" & rs(88) &
"',county66 ='" & rs(89) & "',county67 ='" & rs(90) & _
"' where (providers.[provider number] = '" & Temp & _
"');"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

validation rule on database is : ='99' Or (>'0' And <'68') Or Is Null

The first field always has data but the rest are normally null with
very few exceptions. Problem is I don't know when the fields are null
so I have to copy them all. As I step through the code, I verified at
runtime that the fields are Null but they will not pass the validation
rule on the table. I put the sql in an access sql view and it only
works if i put null as the value and not the '' sting.

Thanks for your time.
Click to expand...
 
B

bcarter97

Thanks Allen! That did the trick. Thanks for the help and the error
message suggestion. The non-normalized field is one I just have to
live with for now because of the way the application is setup.

Allen said:
You want the literal word Null without the quote mark delimiters if the
field is null.

The syntax will look like this:
"UPDATE providers SET county1 = " & IIf(IsNull(rs(24)), "Null", """" &
rs(24) & """") & ", ...

Of course, the core of the difficulty here is the non-normalized field
structure. Whenever you have repeating fields like County1, County2, etc, it
alway means you need to have a related table where there are many records
instead of having many fields. Perhaps you are importing data that arrives
in this unnormalized format, but it would be worthwhile setting up a
properly designed tables for storing it in, i.e. appending a record to a
related table for each record where CountyXX is not null.

You are probably also aware that you don't get any error message if the data
does not update correctly when you turn SetWarnings off. For an alternative,
see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a access 2002 application that needs to copy existing records in
a database and insert then into the same database as new records with
some updated fields. I have it all working but there are several fields
that may or may not contain nulls and i have to copy them all to the
new record. Here it the code I am using but it keeps failing due to
the database validation rule.


SQL = "update providers " & _
"set county1 ='" & rs(24) & "',county2 ='" & rs(25) &
"',county3 ='" & rs(26) & "',county4 ='" & rs(27) & "',county5 ='" &
rs(28) & "',county6 ='" & rs(29) & "',county7 ='" & rs(30) & "',county8
='" & rs(31) & "',county9 ='" & rs(32) & _
"',county10 ='" & rs(33) & "',county11 ='" & rs(34) &
"',county12 ='" & rs(35) & "',county13 ='" & rs(36) & "',county14 ='" &
rs(37) & "',county15 ='" & rs(38) & "',county16 ='" & rs(39) &
"',county17 ='" & rs(40) & "',county18 ='" & rs(41) & _
"',county19 ='" & rs(42) & "',county20 ='" & rs(43) &
"',county21 ='" & rs(44) & "',county22 ='" & rs(45) & "',county23 ='" &
rs(46) & "',county24 ='" & rs(47) & "',county25 ='" & rs(48) &
"',county26 ='" & rs(49) & "',county27 ='" & rs(50) & _
"',county28 ='" & rs(51) & "',county29 ='" & rs(52) &
"',county30 ='" & rs(53) & "',county31 ='" & rs(54) & "',county32 ='" &
rs(55) & "',county33 ='" & rs(56) & "',county34 ='" & rs(57) &
"',county35 ='" & rs(58) & "',county36 ='" & rs(59) & _
"',county37 ='" & rs(60) & "',county38 ='" & rs(61) &
"',county39 ='" & rs(62) & "',county40 ='" & rs(63) & "',county41 ='" &
rs(64) & "',county42 ='" & rs(65) & "',county43 ='" & rs(66) &
"',county44 ='" & rs(67) & "',county45 ='" & rs(68) & _
"',county46 ='" & rs(69) & "',county47 ='" & rs(70) &
"',county48 ='" & rs(71) & "',county49 ='" & rs(72) & "',county50 ='" &
rs(73) & "',county51 ='" & rs(74) & "',county52 ='" & rs(75) &
"',county53 ='" & rs(76) & "',county54 ='" & rs(77) & _
"',county55 ='" & rs(78) & "',county56 ='" & rs(79) &
"',county57 ='" & rs(80) & "',county58 ='" & rs(81) & "',county59 ='" &
rs(82) & "',county60 ='" & rs(83) & "',county61 ='" & rs(84) &
"',county62 ='" & rs(85) & "',county63 ='" & rs(86) & _
"',county64 ='" & rs(87) & "',county65 ='" & rs(88) &
"',county66 ='" & rs(89) & "',county67 ='" & rs(90) & _
"' where (providers.[provider number] = '" & Temp & _
"');"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

validation rule on database is : ='99' Or (>'0' And <'68') Or Is Null

The first field always has data but the rest are normally null with
very few exceptions. Problem is I don't know when the fields are null
so I have to copy them all. As I step through the code, I verified at
runtime that the fields are Null but they will not pass the validation
rule on the table. I put the sql in an access sql view and it only
works if i put null as the value and not the '' sting.

Thanks for your time.
Click to expand...
Click to expand...
 
Top