By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

update query from vba

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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));

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.