日期范围在带有 MS Access 数据库的 VB 中不起作用

发布于 2024-11-09 15:55:02 字数 403 浏览 0 评论 0原文

我需要更新 pdate （日期字段）不在当月和当年的 1 日到 20 日之间的行。

我正在使用下面编写的代码，但它给出了一个错误，指出“预期参数太少 1”。我使用 MS Access 2007 作为数据库。

cn.Execute "update water set prel = (prel + (mmt * (tx / 100))) where pdate not between 1-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "  and 20-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & " "

原文

I need to update the rows where pdate (a date field) is not between the 1st and 20th day of current month and year.

I am using the code written below, but it gives an error saying "too few parameters expected 1". I am using MS Access 2007 as database.

cn.Execute "update water set prel = (prel + (mmt * (tx / 100))) where pdate not between 1-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "  and 20-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & " "

分享到QQ

分享到微博

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

发布评论

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

心不设防 2024-11-16 15:55:02

您面临的直接问题是缺少#个日期分隔符，正如 @MicSim 向您展示的那样。不过，我建议您考虑对 WHERE 子句采用不同的方法。

添加 # 分隔符后的 WHERE 子句与此类似（包含今天的日期）。

Debug.Print "WHERE pdate not between #1-" & _
    Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & _
    "# and #20-" & Format$(Now, "MMM") & "-" & _
    Format$(Now, "YYYY") & "#"
WHERE pdate not between #1-May-2011# and #20-May-2011#

一个重要的问题是您的所有 pdate 值是否都包含午夜作为时间部分。（日期/时间值始终包含时间部分。）这可能很重要的原因是，对于 2011 年 5 月 20 日上午 10:18:15 的更新，本月会发生什么？您的 WHERE 子句将导致更新。但那个日期仍然是 5 月 20 日……这是你想要的吗？

我认为修改后的 WHERE 子句产生意外后果的风险较小。

Debug.Print "WHERE pdate < " & _
    Format(DateSerial(Year(Date), Month(Date), 1), "\#yyyy-mm-dd#\") & _
    " OR pdate >= " & _
    Format(DateSerial(Year(Date), Month(Date), 21), "\#yyyy-mm-dd#\")
WHERE pdate < #2011-05-01# OR pdate >= #2011-05-21#

您的问题被标记为 vb6。 DateSerial()、Year()、Month()、Date() 和 Format() 函数都应该可以从数据库引擎的沙箱模式中使用。（请参阅Microsoft 关于沙盒模式功能的页面）。

编辑：感谢@Brian Camire 提出的建议。

Debug.Print "WHERE pdate < " & _
    "DateSerial(Year(Date), Month(Date), 1)" & _
    " OR pdate >= " & _
    "DateSerial(Year(Date), Month(Date), 21)"

Your immediate issue was the missing # date delimiters, as @MicSim showed you. However I'll suggest you consider a different approach for your WHERE clause.

Your WHERE clause, after adding the # delimiters, is similar to this (with today's date).

Debug.Print "WHERE pdate not between #1-" & _
    Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & _
    "# and #20-" & Format$(Now, "MMM") & "-" & _
    Format$(Now, "YYYY") & "#"
WHERE pdate not between #1-May-2011# and #20-May-2011#

An important issue is whether or not all your pdate values include midnight as the time component. (Date/Time values always include a time component.) The reason this could be significant is what should happen this month for a pdate of 5/20/2011 10:18:15 AM? Your WHERE clause would result in an update. But that date is still May 20th ... is that what you want?

I think there is less risk of unintended consequences with a revised WHERE clause.

Debug.Print "WHERE pdate < " & _
    Format(DateSerial(Year(Date), Month(Date), 1), "\#yyyy-mm-dd#\") & _
    " OR pdate >= " & _
    Format(DateSerial(Year(Date), Month(Date), 21), "\#yyyy-mm-dd#\")
WHERE pdate < #2011-05-01# OR pdate >= #2011-05-21#

Your question is tagged vb6. The DateSerial(), Year(), Month(), Date(), and Format() functions should all be available from the database engine's sandbox mode. (See Microsoft's page about sandbox mode functions).

Edit: Thanks to @Brian Camire for this suggestion.

Debug.Print "WHERE pdate < " & _
    "DateSerial(Year(Date), Month(Date), 1)" & _
    " OR pdate >= " & _
    "DateSerial(Year(Date), Month(Date), 21)"

回复收藏 0 原文

吐个泡泡 2024-11-16 15:55:02

您必须使用散列或单引号来分隔日期，具体取决于您使用的数据库接口。对 ANSI 92 或 ADO/OLEDB 使用哈希值 (#)：

cn.Execute "update water set prel = (prel + (mmt * (tx / 100))) 
where pdate not between 
    #1-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "#  
        and #20-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "# "

不过，我建议使用通用格式 yyyy-mm-dd hh:mm:ss 或 yyyy在本例中为 -mm-dd。

You have to delimit your dates with hashes or single quotes, depending on which database interface you are using. Use hashes (#) for ANSI 92 or ADO/OLEDB:

cn.Execute "update water set prel = (prel + (mmt * (tx / 100))) 
where pdate not between 
    #1-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "#  
        and #20-" & Format$(Now, "MMM") & "-" & Format$(Now, "YYYY") & "# "

Though, I recommend using the general format yyyy-mm-dd hh:mm:ss or yyyy-mm-dd in this case.

回复收藏 0 原文

~没有更多了~