将日期插入 MS Access vba 查询

发布于 2025-01-11 07:28:03 字数 476 浏览 0 评论 0原文

我正在尝试使用 MS Acess 中的 VBA 将当前日期插入到表中。无论我如何尝试，日期都会插入为 12/30/1899。有什么建议可以解决这个问题吗？这是我的代码

Dim StrSQL As String
Dim db As Database
Dim InID As Integer
Dim Inputcasedate As String
Dim InputCaseType_Id As Integer

InID = Me.PgmPart_ID
Inputcasedate = Date
InputCaseType_Id = 1

StrSQL = "INSERT INTO CaseNotes (PgmPart_ID,CaseType_ID,CaseDate) " & _
     "VALUES (" & InID & "," & InputCaseType_Id & "," & Inputcasedate & ")"

原文

I'm trying to insert the current date into a table using VBA in MS Acess. The date gets inserted as 12/30/1899 No matter what I try. Any advise to fix this? Here is my code

Dim StrSQL As String
Dim db As Database
Dim InID As Integer
Dim Inputcasedate As String
Dim InputCaseType_Id As Integer

InID = Me.PgmPart_ID
Inputcasedate = Date
InputCaseType_Id = 1

StrSQL = "INSERT INTO CaseNotes (PgmPart_ID,CaseType_ID,CaseDate) " & _
     "VALUES (" & InID & "," & InputCaseType_Id & "," & Inputcasedate & ")"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

划一舟意中人 2025-01-18 07:28:03

在 MS Access 中，文字日期值必须由井号/数字/井号标签符号分隔：#。

StrSQL = "INSERT INTO CaseNotes (PgmPart_ID,CaseType_ID,CaseDate) " & _
         "VALUES (" & InID & ", " & InputCaseType_Id & ", #" & Inputcasedate & "#)"

但是，与 VBA 一样，Access SQL 维护 Date() 函数。因此，请在 SQL 语句内使用此表达式并避免连接 VBA 变量。

StrSQL = "INSERT INTO CaseNotes (PgmPart_ID,CaseType_ID,CaseDate) " & _
         "VALUES (" & InID & ", " & InputCaseType_Id & ", Date())"

但是，请考虑使用参数化 SQL< 的行业最佳实践/a> MS Access 的 DAO 通过 QueryDefs.Parameters。这样做不需要值分隔符，例如字符串的引号或日期的数字，并在应用程序层 (VBA) 和数据库之间对齐数据类型。

Dim StrSQL As String
Dim db As Database
Dim qdef As QueryDef

' PREPARED STATEMENT (NO VBA VARIABLES)
StrSQL = "PARAMETERS ParamInID Long, ParamInputCaseType_Id Long; " & _
         "INSERT INTO CaseNotes (PgmPart_ID, CaseType_ID, CaseDate) " & _
         "VALUES (ParamInID, ParamInputCaseType_Id, Date());"

' INITIALIZE DAO OBJECTS
Set db = CurrentDb
qdef = db.CreateQueryDef("", StrSQL)

' BIND PARAMETERS
qdef!ParamInID = Me.PgmPart_ID
qdef!ParamInputCaseType_Id = 1

' EXECUTE ACTION
qdef.Execute

Set qdef = Nothing: Set db = Nothing

In MS Access, literal date values must be delimited by pound/numeral/hashtag symbols: #.

StrSQL = "INSERT INTO CaseNotes (PgmPart_ID,CaseType_ID,CaseDate) " & _
         "VALUES (" & InID & ", " & InputCaseType_Id & ", #" & Inputcasedate & "#)"

However, like VBA, Access SQL maintains the Date() function. So use this expression inside the SQL statement and avoid the concatenated VBA variable.

StrSQL = "INSERT INTO CaseNotes (PgmPart_ID,CaseType_ID,CaseDate) " & _
         "VALUES (" & InID & ", " & InputCaseType_Id & ", Date())"

However, consider the industry best practice with parameterized SQL which MS Access's DAO supports via QueryDefs.Parameters. Doing so, requires no value delimiters like quotes for strings or numerals for dates and aligns data types between app layer (VBA) and database.

Dim StrSQL As String
Dim db As Database
Dim qdef As QueryDef

' PREPARED STATEMENT (NO VBA VARIABLES)
StrSQL = "PARAMETERS ParamInID Long, ParamInputCaseType_Id Long; " & _
         "INSERT INTO CaseNotes (PgmPart_ID, CaseType_ID, CaseDate) " & _
         "VALUES (ParamInID, ParamInputCaseType_Id, Date());"

' INITIALIZE DAO OBJECTS
Set db = CurrentDb
qdef = db.CreateQueryDef("", StrSQL)

' BIND PARAMETERS
qdef!ParamInID = Me.PgmPart_ID
qdef!ParamInputCaseType_Id = 1

' EXECUTE ACTION
qdef.Execute

Set qdef = Nothing: Set db = Nothing

回复收藏 0 原文

~没有更多了~