MS Access - 使用不带日期值的时间值

发布于 2024-12-10 14:20:17 字数 965 浏览 0 评论 0原文

使用表达式生成器，我构建了一个如下所示的 switch 语句：

Switch([Time]>=#12:00:00 AM# And [Time]<#7:00:00 AM#,"Before 7 am",
[Time]>=#7:00:00 AM# And [Time]<#10:00:00 AM#,"7 am - 9:59 am",
[Time]>=#10:00:00 AM# And [Time]<#5:00:00 PM#,"10 am - 4:59 pm",
[Time]>=#5:00:00 PM# And [Time]<=#9:00:00 PM#,"5 pm - 9 pm",
[Time]>#9:00:00 PM# And [Time]<#11:59:00 PM#,"After 9 pm")

但是，我注意到我的查询无法正常工作，因此我检查了 SQL。奇怪的是，它看起来像这样：

Switch([Time]>=#12/30/1899# And [Time]<#12/30/1899 7:0:0#,"Before 7 am",
[Time]>=#12/30/1899 7:0:0# And [Time]<#12/30/1899 10:0:0#,"7 am - 9:59 am",
[Time]>=#12/30/1899 10:0:0# And [Time]<#12/30/1899 17:0:0#,"10 am - 4:59 pm",
[Time]>=#12/30/1899 17:0:0# And [Time]<=#12/30/1899 21:0:0#,"5 pm - 9 pm",
[Time]>#12/30/1899 21:0:0# And [Time]<#12/30/1899 23:59:0#,"After 9 pm") 
AS Time_Range

因此，看起来 Access 正在将这些值转换为日期/时间值，但我只需要时间。有办法做到这一点吗？

原文

Using expression builder, I built a switch statement that looks like this:

Switch([Time]>=#12:00:00 AM# And [Time]<#7:00:00 AM#,"Before 7 am",
[Time]>=#7:00:00 AM# And [Time]<#10:00:00 AM#,"7 am - 9:59 am",
[Time]>=#10:00:00 AM# And [Time]<#5:00:00 PM#,"10 am - 4:59 pm",
[Time]>=#5:00:00 PM# And [Time]<=#9:00:00 PM#,"5 pm - 9 pm",
[Time]>#9:00:00 PM# And [Time]<#11:59:00 PM#,"After 9 pm")

But, I noticed my query wasn't working right, so I checked the SQL. Strangely, it looks like this:

Switch([Time]>=#12/30/1899# And [Time]<#12/30/1899 7:0:0#,"Before 7 am",
[Time]>=#12/30/1899 7:0:0# And [Time]<#12/30/1899 10:0:0#,"7 am - 9:59 am",
[Time]>=#12/30/1899 10:0:0# And [Time]<#12/30/1899 17:0:0#,"10 am - 4:59 pm",
[Time]>=#12/30/1899 17:0:0# And [Time]<=#12/30/1899 21:0:0#,"5 pm - 9 pm",
[Time]>#12/30/1899 21:0:0# And [Time]<#12/30/1899 23:59:0#,"After 9 pm") 
AS Time_Range

So, it looks like Access is converting these to Date/Time values, but I only need the time. Is there a way to do this?

分享到QQ

分享到微博

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

发布评论

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

盛装女皇 2024-12-17 14:20:17

如果您的 [Time] 字段值包含 0 作为整数（天数）部分，则该 SQL 应该有效。仔细检查您的 [时间] 值是否包含非零日部分。

SELECT Format([Time], "yyyy-mm-dd hh:nn:ss") AS formatted_date_time
FROM YourTable;

如果该查询显示的 [Time] 值包含 12/30/1899 以外的日期，则可以使用 TimeValue() 函数从这些值中提取时间部分。（有关详细信息，请参阅 Access 的帮助主题。）该函数实际上会为您提供 1899 年 12 月 30 日（即第 0 天）一天中的同一时间。正如 HK1 提到的，不存在时间数据类型（无论是对于 VBA 还是数据库引擎）；只有日期/时间，并且始终包括表示为自零天 (12/30/1899) 以来的整数天数的日期值。

编辑：如果您要为此使用 Switch() 函数，则可以简化它。

Switch([Time]<#12/30/1899 7:0:0#,"Before 7 am",
[Time]<#12/30/1899 10:0:0#,"7 am - 9:59 am",
[Time]<#12/30/1899 17:0:0#,"10 am - 4:59 pm",
[Time]<=#12/30/1899 21:0:0#,"5 pm - 9 pm",
[Time]<#12/30/1899 23:59:0#,"After 9 pm") 
AS Time_Range

IOW，您不需要将第二个条件写为 ...

[Time]>=#7:00:00 AM# And [Time]<#10:00:00 AM#

... 因为如果 [Time] 早于上午 7 点，则第一个条件将为 True，并且不会评估第二个条件。

Edit2：您可以使用时间括号表，而不是使用 Switch 语句将它们编码到 SQL 中。

bracket_start  bracket_end  bracket_label
12:00:00 AM     6:59:59 AM      Before 7 am
 7:00:00 AM     9:59:59 AM   7 am - 9:59 am
10:00:00 AM     4:59:59 PM  10 am - 4:59 pm
 5:00:00 PM     9:00:00 PM      5 pm - 9 pm
 9:00:01 PM    11:59:59 PM       After 9 pm

SELECT
    y.[Time],
    b.bracket_label
FROM
    YourTable AS y,
    time_brackets AS b
WHERE
        y.[Time] >= b.bracket_start
    AND y.[Time] <= b.bracket_end;

Edit3：与查询中的 Switch() 表达式相比，我更喜欢使用 time_brackets 表方法 (Edit2)。如果您有多个这样的查询，并且您决定更改时间括号和/或括号标签，那么表方法将更容易维护。只需更改 time_brackets 表，而不是修改每个查询中的 Switch() 表达式。

如果您只是针对单个查询执行此操作，那么这种考虑可能不会令人信服。然而，在这种情况下，我仍然更喜欢表方法，因为我发现编写无错误的复杂 Switch() 表达式更具挑战性。如果我们考虑嵌套 IIf() 表达式而不是 Switch()，同样的论点也适用（甚至更是如此！）。

最后，括号时间范围和标签构成数据。数据理应属于表。只要可行，我宁愿避免将数据编码到 SELECT 语句中。这意味着我寻找方法来避免查询中的 Switch() 或嵌套 IIf() 表达式。这里附带的一个好处是，查询设计器不会像您没有 Switch() 语句时那样重写您的 Switch() 语句。 :-)

That SQL should work if your [Time] field values include 0 as the whole number (day number) part. Double-check whether your [Time] values include a non-zero day component.

SELECT Format([Time], "yyyy-mm-dd hh:nn:ss") AS formatted_date_time
FROM YourTable;

If that query shows you [Time] values which include a date other than 12/30/1899, you can use the TimeValue() function to extract the time portions from those values. (See Access' help topic for details.) The function will actually give you the same time of day on 12/30/1899, which is day zero. As HK1 mentioned there is no such thing as a time data type (either for VBA or the db engine); there is only Date/Time, and that always includes a day value represented as a whole number of days since day zero (12/30/1899).

Edit: If you're going to use the Switch() function for this, you can simplify it.

Switch([Time]<#12/30/1899 7:0:0#,"Before 7 am",
[Time]<#12/30/1899 10:0:0#,"7 am - 9:59 am",
[Time]<#12/30/1899 17:0:0#,"10 am - 4:59 pm",
[Time]<=#12/30/1899 21:0:0#,"5 pm - 9 pm",
[Time]<#12/30/1899 23:59:0#,"After 9 pm") 
AS Time_Range

IOW, you don't need to write the second condition as ...

[Time]>=#7:00:00 AM# And [Time]<#10:00:00 AM#

... because if [Time] is earlier than 7 AM, the first condition would be True, and the second condition wouldn't get evaluated.

Edit2: You could use a table for the time brackets instead of using a Switch statement to code them into your SQL.

bracket_start  bracket_end  bracket_label
12:00:00 AM     6:59:59 AM      Before 7 am
 7:00:00 AM     9:59:59 AM   7 am - 9:59 am
10:00:00 AM     4:59:59 PM  10 am - 4:59 pm
 5:00:00 PM     9:00:00 PM      5 pm - 9 pm
 9:00:01 PM    11:59:59 PM       After 9 pm

SELECT
    y.[Time],
    b.bracket_label
FROM
    YourTable AS y,
    time_brackets AS b
WHERE
        y.[Time] >= b.bracket_start
    AND y.[Time] <= b.bracket_end;

Edit3: I would prefer the time_brackets table approach (Edit2) over the Switch() expression in your query. The table approach will be easier to maintain if you have more than one query like this in the event you ever decide to change the time brackets and/or the bracket labels. Simply change the time_brackets table instead of revising a Switch() expression in each query.

That consideration may not be compelling if you're only doing this for a single query. However, in that case, I would still prefer the table approach because I find it more challenging to write complex Switch() expressions error-free. The same argument would also apply (even more so!) if we were considering a nested IIf() expression instead of Switch().

Finally, the bracket time ranges and labels constitute data. And data rightfully belongs in tables. Wherever practical, I prefer to avoid coding data into SELECT statements. That means I look for ways to avoid Switch() or nested IIf() expressions in queries. And an incidental benefit here is that the query designer will not rewrite your Switch() statement as it did for you when you don't have a Switch() statement. :-)

回复收藏 0 原文

扶醉桌前 2024-12-17 14:20:17

这里有一些额外的建议...

如果您的 [Time] 值可能包含除零之外的整数（天）部分，您可以尝试 @HansUp 建议的以下变体：

Switch(Hour([Time])<7,"Before 7 am",
Hour([Time])<10,"7 am - 9:59 am",
Hour([Time])<17,"10 am - 4:59 pm",
Hour([Time])<=21,"5 pm - 9 pm",
Hour([Time])<=23,"After 9 pm") 
AS Time_Range

或者，如果您想强制执行 [ Time] 值不包含整数部分，您可以将基础表（如果有）中字段的“验证规则”属性设置为：

>=0 And <1

Here are some additional suggestions...

If your [Time] values may contain a whole number (day) component other than zero, you might try the following variation of @HansUp's suggestion:

Switch(Hour([Time])<7,"Before 7 am",
Hour([Time])<10,"7 am - 9:59 am",
Hour([Time])<17,"10 am - 4:59 pm",
Hour([Time])<=21,"5 pm - 9 pm",
Hour([Time])<=23,"After 9 pm") 
AS Time_Range

Alternatively, if you want to enforce the assumption that the [Time] values do not contain a whole number component, you might might set the "Validation Rule" property of the field in the underlying table (if there is one) to: