Batch Update

[kimtp]

Am trying to perform a batch update (reset records to '' or Null) on my database. Have figured out how to do that with alpha/numeric fields but am having trouble with boolean. What I have so far is:

cn.Execute "Update mytablename" & _
" Set myfldname = Null" 'or
" Set myfldName = ''"

When I do the same for a boolean field, the field does not change in the database if it is checked.

Any help is appreciated.

Thanks

Kim

 

[hutchkc]

if boolean in db then null or "" wont change anything, bool values are by default regarded as false, so try updating the bool fields to false instead of null

 

[kimtp]

Thanx for the response. Should have mentioned that I had tried = False. Doesn't do the job.

Kim

 

[vb5prgrmr]

Try...
[tt]
SQLString = Update MyTableName Set MyFieldName = " & False
[/tt]

Notice the false outside of the string so you are not passing "False" but the value of false which is 0 (zero).

Good Luck

 

[kimtp]

Don't need a sql string for this batch update and when I tried what you suggest I get a data type mismatch error.

Kim

 

[kimtp]

I have two tables and just discovered that setting the value to either Null or False in one table works but not in the other. Here is the code for both:
cData.Execute "UPDATE Attending" & _
" Set numAttend = Null,MealAmount = Null, SouvenirNum = Null," & _
" SouvenirAmount = Null, GiftAmount = Null," & _
" Deceased = NULL, MailLabel = NULL, Souvenir = NULL"

cData.Execute "UPDATE Members" & _
" SET EventName = '', EventNum = Null, EventAmount = Null," & _
" EventAttend = False"

The Update Members works, Attending does not. It is a puzzle to me.

Thanx.

Kim

 

[kimtp]

Disregard all of this. Setting the boolean to either False or Null clears the check box in Access. My powers of observation need some honing. And this before the first homebrew.

Thanks to all for your input.

Kim