update query from vba

[Eric J Owens]

TIA for any help!

I have an a2k mdb front end using sql server 2k on backend. my form show
router port assignments, when an assignment is deleted, there is a button
that is supposed to set the fields to null. All my text fields, though, end
up being updated to a '0' or '-1' instead of a null value. The sql server is
set to allow nulls in these fields.
here is my code: (each docmd statement is on a single line)

Private Sub ClearAssignment_Click()

DoCmd.SetWarnings (0)
DoCmd.RunSQL ("UPDATE RouterPort SET RouterPort.Status = 4,
RouterPort.CircuitID = isnull(routerport.circuitid), RouterPort.IPSubnet =
isnull(RouterPort.IPSubnet), RouterPort.[Group] =
isnull(RouterPort.[Group]), RouterPort.MIP = isnull(RouterPort.MIP),
RouterPort.RouterCable_ID = IsNull([routerport].[routercable_id]),
RouterPort.RouterCableAdapter_ID =
IsNull([routerport].[routercableadapter_id]), RouterPort.RouterProtocol_ID =
IsNull([routerport].[routerprotocol_id]), RouterPort.RouterAppplication_ID =
IsNull([routerport].[routerappplication_id]), RouterPort.RouterSubnetMask_ID
= IsNull([routerport].[routersubnetmask_id]), RouterPort.Assignment_ID =
IsNull([routerport].[assignment_id]) WHERE
(((RouterPort.ID)=[Forms]![EngData (Site)]![EngData (Router
Ports)].[Form]![ID]));")
DoCmd.RunSQL ("UPDATE RouterPort SET RouterPort.Status = 4,
RouterPort.CircuitID = isnull(routerport.circuitid), RouterPort.IPSubnet =
isnull(RouterPort.IPSubnet), RouterPort.[Group] =
isnull(RouterPort.[Group]), RouterPort.MIP = isnull(RouterPort.MIP),
RouterPort.RouterCable_ID = IsNull([routerport].[routercable_id]),
RouterPort.RouterCableAdapter_ID =
IsNull([routerport].[routercableadapter_id]), RouterPort.RouterProtocol_ID =
IsNull([routerport].[routerprotocol_id]), RouterPort.RouterAppplication_ID =
IsNull([routerport].[routerappplication_id]), RouterPort.RouterSubnetMask_ID
= IsNull([routerport].[routersubnetmask_id]), RouterPort.Assignment_ID =
IsNull([routerport].[assignment_id]) WHERE
(((RouterPort.ID)=[Forms]![EngData (Site)]![EngData (Router
Ports)].[Form]![Assignment_ID]));")
SendKeys "^{F9}"
DoCmd.SetWarnings (-1)

End Sub

[Larry Linson]

Well, I don't doubt that your fields are being filled with -1 and 0, because
those represent True and Fals and those are the two values that can be
returned by the IsNull function. IsNull tests for a Null value, it does not
_return_ a Null value.

Here's a simple query, created in the Access Query Builder that sets a field
to Null in records that meet a particular criteria:

UPDATE tblSomeNumsForReportTesting SET tblSomeNumsForReportTesting.NumField
= Null
WHERE (((tblSomeNumsForReportTesting.RecordID)<3));

"Eric J Owens" <ej*****@att.com> wrote in message
news:bn**********@kcweb01.netnews.att.com...

TIA for any help!

I have an a2k mdb front end using sql server 2k on backend. my form show
router port assignments, when an assignment is deleted, there is a button
that is supposed to set the fields to null. All my text fields, though, end up being updated to a '0' or '-1' instead of a null value. The sql server is set to allow nulls in these fields.
here is my code: (each docmd statement is on a single line)

Private Sub ClearAssignment_Click()

DoCmd.SetWarnings (0)
DoCmd.RunSQL ("UPDATE RouterPort SET RouterPort.Status = 4,
RouterPort.CircuitID = isnull(routerport.circuitid), RouterPort.IPSubnet =
isnull(RouterPort.IPSubnet), RouterPort.[Group] =
isnull(RouterPort.[Group]), RouterPort.MIP = isnull(RouterPort.MIP),
RouterPort.RouterCable_ID = IsNull([routerport].[routercable_id]),
RouterPort.RouterCableAdapter_ID =
IsNull([routerport].[routercableadapter_id]), RouterPort.RouterProtocol_ID = IsNull([routerport].[routerprotocol_id]), RouterPort.RouterAppplication_ID = IsNull([routerport].[routerappplication_id]), RouterPort.RouterSubnetMask_ID = IsNull([routerport].[routersubnetmask_id]), RouterPort.Assignment_ID =
IsNull([routerport].[assignment_id]) WHERE
(((RouterPort.ID)=[Forms]![EngData (Site)]![EngData (Router
Ports)].[Form]![ID]));")
DoCmd.RunSQL ("UPDATE RouterPort SET RouterPort.Status = 4,
RouterPort.CircuitID = isnull(routerport.circuitid), RouterPort.IPSubnet =
isnull(RouterPort.IPSubnet), RouterPort.[Group] =
isnull(RouterPort.[Group]), RouterPort.MIP = isnull(RouterPort.MIP),
RouterPort.RouterCable_ID = IsNull([routerport].[routercable_id]),
RouterPort.RouterCableAdapter_ID =
IsNull([routerport].[routercableadapter_id]), RouterPort.RouterProtocol_ID = IsNull([routerport].[routerprotocol_id]), RouterPort.RouterAppplication_ID = IsNull([routerport].[routerappplication_id]), RouterPort.RouterSubnetMask_ID = IsNull([routerport].[routersubnetmask_id]), RouterPort.Assignment_ID =
IsNull([routerport].[assignment_id]) WHERE
(((RouterPort.ID)=[Forms]![EngData (Site)]![EngData (Router
Ports)].[Form]![Assignment_ID]));")
SendKeys "^{F9}"
DoCmd.SetWarnings (-1)

End Sub

[Eric J Owens]

Thanks, Larry. That was not my understanding of null values. I will need
to read up on them some more...

Gratefully,

Eric J Owens

Well, I don't doubt that your fields are being filled with -1 and 0, because those represent True and Fals and those are the two values that can be
returned by the IsNull function. IsNull tests for a Null value, it does not _return_ a Null value.

Here's a simple query, created in the Access Query Builder that sets a field to Null in records that meet a particular criteria:

UPDATE tblSomeNumsForReportTesting SET tblSomeNumsForReportTesting.NumField = Null
WHERE (((tblSomeNumsForReportTesting.RecordID)<3));