在MS Access中使用SQL更新空字段 [英] Update Null Fields with SQL in MS Access

问题描述

我试图在Access VBA中运行一些SQL命令，以使用从表单的组合框获得的值更新列中的空白(空)字段.

I'm trying to run some SQL commands in Access VBA to update the blank (null) fields in a column with the value obtained from a combobox in the form.

目前我正在接收

运行时错误'3061'参数太少.预期为1

Run time Error '3061' Too Few Parameters. Expected 1

但它似乎格式正确.

我正在使用的代码如下.用户将作为字符串传递，例如-"Joe Bloggs".

The code I'm using is below. User will be passed as a string, e.g. - "Joe Bloggs".

Public Sub testSub(user as string)
Dim db as DAO.Database
Dim sqlstr as String
set db as CurrentDB

sqlstr = "UPDATE tTable1 SET Field1 = [" & user & "] WHERE Field1 IS NULL;"

db.Execute sqlstr

End Sub

推荐答案

我认为您需要在用户中使用单引号将用户作为'string'.另外，我一直使用set db = currentdb而不是使用as

I think you need to have user as a 'string' in your query using single quotes. Also, I have always used set db = currentdb instead of using as

Public Sub testSub(user as string)
Dim db as DAO.Database
Dim sqlstr as String
set db = CurrentDB

sqlstr = "UPDATE tTable1 SET Field1 = '" & user & "' WHERE Field1 IS NULL;"

db.Execute sqlstr

End Sub

正如@jarlh所说，我同意，我认为方括号不是必需的

As @jarlh has mentioned I agree, I don't think that the square parentheses are required

这篇关于在MS Access中使用SQL更新空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！