从单独的日期和时间条目创建日期/时间字段 [英] Create date/time field from separate date and time entries

问题描述

只是一个简单的问题......我正在运行一个跟踪单个表中约会的MS Access 2007数据库。日期和时间在短日期（mm / dd / yyyy）和短时间（hh：mm）格式中作为单独的字段输入。我一直在按日期运行查询，这一直在运行，但有时我也必须根据时间运行查询。 （例如，返回在2009年12月15日下午1:00之后和2009年12月22日下午1:00之前发生的所有地点）。无论时间是否作为搜索参数输入，我都需要查询才能工作。我一直在玩IIF语句，涉及开始/结束日期和时间的可能组合，但是它的方式比我认为它需要。另外，到目前为止我还没有工作。


我觉得最简单的事情就是将日期和时间字段中的值转换为单个日期/时间字段，然后将IIF函数基于此新值。必须有一个功能，让你这样做，我只是没有弄清楚它是什么。


任何帮助？


JS

解决方案

这似乎有效：

展开 | 选择 | Wrap | 行号

谢谢，ChipR，但该表达式从文本字符串返回一个日期 - 我想要做的是组合将日期字段和时间字段放入日期/时间字段，然后我可以在IIF语句中使用该字段在查询参数中设置开始日期/时间和结束日期/时间。我试图在CDate函数中使用''[Date]''和''[Time]''作为文本，如下所示：

展开 | 选择 | Wrap | 行号

您好。如果日期和时间值存储在日期字段中，那么简单的答案是将两者一起添加以形成单个日期/时间值。日期/时间值的日期部分是存储值的整数分量，时间部分是小数部分。添加这些应该没有任何问题，导致类型日期/时间的值。进行这种比较的虚拟IIF如下所示：

展开 | 选择 | Wrap | 行号

Just a quick question...I''m running an MS Access 2007 db that tracks appointments in a single table. Date and time are entered as separate fields in short date (mm/dd/yyyy) and short time (hh:mm) format. I''ve been running queries by date alone, which has been working, but there are times when I have to run queries based on times as well. (e.g. return all apointments that occurred after 1:00 PM on 12/15/2009 and before 1:00 PM on 12/22/2009). I need the query to work whether time is input as a search parameter or not, and I''ve been playing around with IIF statements involving the possible combinations of start/end dates and times, but it''s getting WAY more complicated than I think it needs to be. Plus, I haven''t gotten it to work so far.

It occurs to me that the easiest thing to do would be to convert the values in both the date and time fields into a single date/time field, then base the IIF function on this new value. There''s got to be a function that lets you do this, I just haven''t figured out what it is.

Any help?

JS

解决方案

This seems to work:

Expand|Select|Wrap|Line Numbers

Thanks, ChipR, but that expression returns a date from the text string--what I want to do is combine the date field and the time field into a date/time field that I can then use in an IIF statement to set the start date/time and end date/time in my query parameters. I tried to use both ''[Date]'' and ''[Time]'' as text in the CDate function, like this:

Expand|Select|Wrap|Line Numbers

Hi. If the date and time values are stored in date fields then the simple answer is to add the two together to form a single date/time value. The date part of a date/time value is the integer component of the value stored, and the time part the decimal component. Addition of these should work without any problem, resulting in a value of type date/time. A dummy IIF that does this kind of comparison is shown below :

Expand|Select|Wrap|Line Numbers

这篇关于从单独的日期和时间条目创建日期/时间字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！