如何使用 VBA 在整数字段中插入空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4089540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I insert null into an integer field with VBA?
提问by dmr
I have an MS Access ADP with a SQL server backend. I want to take a bunch of values from a form and insert them into a table. Some of the values can be null. How do I use vba to insert null into an integer field in the SQL Server table?
我有一个带有 SQL 服务器后端的 MS Access ADP。我想从表单中取出一堆值并将它们插入到表格中。一些值可以为空。如何使用 vba 将空值插入 SQL Server 表中的整数字段？
I tried
我试过
"insert...values(" & nz(me.myInt, null) & ",..."
and
和
"insert...values(" & nz(me.myInt, "null") & ",..."
Neither worked.
都没有工作。
回答by Kevin O'Donovan
IF the field will accept nulls then simply leave it out of the list of fields and it will get a null value. So given a table with Name, Address, phone, doing the equivalent of
如果该字段将接受空值，则只需将其排除在字段列表之外，它将获得一个空值。所以给定一个包含姓名、地址、电话的表格，做相当于
INSERT table(Name, Address) VALUES('fred','Toytown')
will leave the phone number with a null value
将电话号码保留为空值
回答by phoog
your second example looks good
你的第二个例子看起来不错
("insert...values(" & nz(me.myInt, "null") & ",...")
("插入...值(" & nz(me.myInt, "null") & ",...")
but only if me.myInt is a variant, a control, or a field. If myInt is an integer or a long, then it cannot be null.
但前提是 me.myInt 是变体、控件或字段。如果 myInt 是整数或长整数，则它不能为空。
回答by Patrick
what error are you getting when you run your code?
运行代码时遇到什么错误？
say we have table... [test] with columns [id], [test] and [test 2].
假设我们有表... [test] 带有 [id]、[test] 和 [test 2] 列。
you could write;
你可以写；
insert into [test] ([test], [test 2]) values ( null, null)
make sure that your string that you are passing the command in on shows the null w/o any kind of decoration... no single or double quotes.
确保您传递命令的字符串显示空值，没有任何类型的装饰......没有单引号或双引号。
