访问VBA和SQL日期格式 [英] Access VBA & SQL date formats

问题描述

我在SQL语句中使用日期格式时遇到问题

I'm having problems with date format in an SQL statement

CurrentTime = Format(CurrentTime, "dd/mm/yyyy hh:mm")
SQL = "SELECT Count([ID]) AS Booked FROM [Appointment Slots] WHERE [Appointment Slots].Time=#" & CurrentTime & "#"

这里的奇怪之处是，有时当我运行代码时此方法有效。其他时候，并且没有更改代码，它没有，但是当我将日期格式更改为mm / dd / yyyy hh：mm时，它可以工作，然后工作一段时间，然后停止工作，直到我将其更改回dd / mm / yyyy hh：mm

The bizarre thing here is that sometimes when I run the code this works. Other times, and without changing the code, it doesn't, but then it works when I change the date format to mm/dd/yyyy hh:mm then it works for a while then stops working until I change it back to dd/mm/yyyy hh:mm

显然，区域日期设置及其存储日期的方式出了问题，但是我无法确定解决方案。是否无法比较Access SQL中与格式无关的日期？

Clearly something is going wrong with the regional date settings and how it's storing dates but I can't pin down a solution to this. Is there no way to compare a date in Access SQL that is independent of format?

推荐答案

是有没有办法在Access SQL中比较独立于
格式的日期？

Is there no way to compare a date in Access SQL that is independent of format?

考虑一个带有参数的查询其 WHERE 子句，类似于此...

Consider a query with a parameter in its WHERE clause, similar to this ...

WHERE [Appointment Slots].Time=[Enter Appointment Time]

您还可以在SQL语句的开头添加 PARAMETERS 子句，但这不是绝对必需的...

You can also add a PARAMETERS clause at the beginning of your SQL statement, but it's not absolutely required ...

PARAMETERS [Enter Appointment Time] DateTime;

因此，当查询需要Date / Time参数时，只需为其提供Date / Time值即可。

So when the query expects a Date/Time parameter, you just give it a Date/Time value.

参数查询可以避免日期格式问题，也可以避免在SQL语句中使用＃日期分隔符。

A parameter query avoids date format issues, and also avoids the need for # date delimiters in the SQL statement.

这篇关于访问VBA和SQL日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！