SQL日期查询中的VBA变量 [英] VBA variable in SQL date query

问题描述

我正在尝试在 SQL 数据库中查询日期在用户输入给出的日期之后的所有行.当我用#"包围我的日期时，我遇到了各种错误，从附近的语法不正确"到将表达式转换为算术溢出错误".我当前的代码如下所示:

I'm trying to query the SQL database for all lines where the date is after a date given through user input. I've run into a variety of errors from "incorrect syntax near" when I surround my date with "#" to "arithmetic overflow error converting expression to". My current code looks like this:

inputdate = InputBox("Please enter the starting date (mm/dd/yyyy)")
Debug.Print inputdate

querydate = "(DTG > " & Format(inputdate, "MMDDYYYY") & ")"

select StationID, DTG, CeilingFeet from SurfaceOb where " & querydate & " and StationID = 'NZWD'"

DTG 是 SQL 数据库中日期时间组的列名.知道我哪里出错了吗?在过去的几天里，我已经尝试了我能找到的所有解决方案，但都没有运气.提前致谢.

DTG is the column name for the date-time group in the SQL database. Any idea where I am going wrong? I've tried every solution I could find over the past few days without luck. Thank you in advance.

推荐答案

主要问题是日期必须用单引号括起来.这是一个完整的工作示例(减去有效的连接字符串)，它应该解释如何实现您的目标.请注意，您还需要切换到 ISO 日期格式，其顺序为年-月-日 (YYYY-MM-DD).

The primary issue is that dates must be enclosed in single-quotes. Here is a complete working example (minus a valid connection string) that should explain how to achieve your objective. Note that you will also want to switch to the ISO date format, in which the order is Year-Month-Day (YYYY-MM-DD).

Sub UpdateRecords()
    Dim connection As New ADODB.connection
    Dim recordset As New ADODB.recordset
    Dim connectionString As String
    Dim query As String
    Dim inputdate As Date
    Dim querydate As String

    inputdate = InputBox("Please enter the starting date (mm/dd/yyyy)")
    querydate = "(DTG > '" & Format(inputdate, "yyyy-mm-dd") & "')"
    query = "select StationID, DTG, CeilingFeet from SurfaceOb where " & querydate & " and StationID = 'NZWD'"

    connectionString = "..."        
    connection.Open connectionString
    recordset.Open query, connection
    ActiveSheet.Range("A1").CopyFromRecordset recordset
End Sub

这篇关于SQL日期查询中的VBA变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！