MS Access - 使用没有日期值的时间值 [英] MS Access - Using time values without Date values

问题描述

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

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")

但是，我注意到我的查询不正常，所以我检查了 SQL.奇怪的是，它看起来像这样:

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

所以，看起来 Access 正在将这些转换为日期/时间值，但我只需要时间.有没有办法做到这一点?

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?

推荐答案

如果您的 [Time] 字段值包括 0 作为整数(天数)部分，那么该 SQL 应该可以工作.仔细检查您的 [Time] 值是否包含非零日组件.

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;

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

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).

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

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，你不需要把第二个条件写成...

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

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

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

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

Edit2:您可以使用表格作为时间括号，而不是使用 Switch 语句将它们编码到您的 SQL 中.

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:我更喜欢 time_brackets 表方法 (Edit2) 而不是查询中的 Switch() 表达式.如果在您决定更改时间括号和/或括号标签的情况下有多个这样的查询，则表格方法将更容易维护.只需更改 time_brackets 表，而不是修改每个查询中的 Switch() 表达式.

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.

如果您仅针对单个查询执行此操作，则该考虑可能不具有吸引力.但是，在那种情况下，我仍然更喜欢表格方法，因为我发现编写无错误的复杂 Switch() 表达式更具挑战性.如果我们考虑嵌套的 IIf() 表达式而不是 Switch()，同样的论点也适用(更是如此！).

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().

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

这篇关于MS Access - 使用没有日期值的时间值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！