Set a binary field to NULL ?

[AlexT]

Folks,

A pretty basic question... how do I set a binary field to NULL ?

Simple assignement is clearly not correct, I get the following message:

You tried to assign the Null value to a variable that is not a Variant
data type.

Thanks

--alexT

 

[Sylvain Lafontaine]

Binary field or Binary variable? You should show us a copy of the code that
you are trying to do.

 

[Pat Hartman\(MVP\)]

Binary fields support only two values - true and false. Null would be a
third state.

 

[Robert Morley]

Binary fields support only two values - true and false. Null would be a
third state.

That would be a "bit" field, and in SQL Server, bit fields can be True,
False, or Null if nulls are allowed.

A binary field in SQL Server is like the Memo field in Access. As to how to
set it to Null, I think it should just be a matter of doing a SET
MyTable!MyField = Null, assuming you're in VBA.

We'll have to wait for the original poster to clarify what the problem is, I
think. (OP: as Sylvain suggested, post a few lines of code around where
you're error is happening.)



Rob

 

[AlexT]

Folks

I was referring to field defined in SQL server as binary, so my
offending code looks like

rs![mybinaryfield] = Null

I'd like to set mybinaryfield to NULL. It's probably trivial but I
don't see the right way of doing this.

Thanks for your help

-alexT

 

[giorgio rancati]

Hi alexT

Set the [mybinaryfield] field for accepts null values, open the table in
Design View and check its *Allow Nulls*
or execute a T-SQL command.

 

[Brendan Reynolds]

As others have indicated, if the definition of the field permits Null
values, you should be able to simply assign the value. Here's an example. I
started this code yesterday, when there was some debate about whether the
field in question was binary or bit, so it includes both ...

Public Sub TestSub()

Dim rst As ADODB.Recordset

With CurrentProject.Connection
On Error Resume Next
.Execute "ALTER TABLE Employees DROP COLUMN TestBit"
.Execute "ALTER TABLE Employees DROP COLUMN TestBinary"
On Error GoTo 0
.Execute "ALTER TABLE Employees ADD TestBit bit Null"
.Execute "ALTER TABLE Employees ADD TestBinary binary Null"
.Execute "UPDATE Employees SET TestBit = Null WHERE LastName =
'Davolio'"
.Execute "UPDATE Employees SET TestBinary = Null WHERE LastName =
'Davolio'"
End With

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM Employees WHERE LastName = 'Fuller'"
.Open
.Fields("TestBit") = Null
.Fields("TestBinary") = Null
.Update
.Close
End With

End Sub

 

[Robert Morley]

I've never actually worked with binary fields in SQL Server, so I can't
really give you any firm guidance here. The two other responses to the post
sort of indicate the following, just let me spell it out, and add my own two
cents as well.

First and foremost, make sure the field supports Null values.

Second, try using the full syntax, rather than an abbreviated one that
relies on default properties. In other words, use the .Fields collection,
and the .Value property. It's rare that these are required, but will
occasionally fix an otherwise problematic piece of code, typically when your
field name happens to be a reserved word.

rs.Fields("mybinaryfield").Value = Null

Everything I know says that this SHOULD work, but binary fields are a bit of
a special case, so maybe all my typing here is for nothing.



Rob

 

[Brendan Reynolds]

If you're still having problems with this, Alex - what happens when you
execute that code? Is there an error message?

 

[AlexT]

Ok folks I did not check that the field was defined not to accepts
NULLs...

Sorry

--alexT