日期混淆 [英] Dates confusions

问题描述

我读的越多，我就越困惑。日期计算太多了

小组。


我需要知道一本书在过去一年中被借出的频率 -

52周。

我的桌子有


预订

DateOut

总计

DateReturn


总计应该显示过去一年借出书的次数

。

我以为在特定书上做了一个查询：给所有日期

然后以某种方式做另一个查询只给那些日期说

（23/06 / 2005年减去52周）给出过去一年的日期

然后计算这样获得的日期数：比如说6

就在那里。

过去一年的总天数也不错，但是

会结冰......


但是怎么样？

Lindie

解决方案

首先，您是否只想知道结账时间
指定（即你想在指定的时间段开始时包括书的数量

吗？


你不会把总计字段在你的桌子上。当需要

时，你会计算出来。如果您想要的是该书在指定时间内开始检查

期间的次数，则将DateOut日期限制为

指定时间并计算该数字或者返回的记录。


使用VBA（这可以用作计算文本框的控制源）：

= DCount（" *"， MyTable，[DateOut]> =＃"& DateAdd（" yyyy"，-1，Date（））&"＃

和[DateOut]< ; =＃"& Date（）&"＃And [BookID] ="& txtBookID）


这会检查DateOut是否在今天和1年前之间，还使用文本框txtBookID中显示的

book'记录中书籍ID字段的值来限制当前图书的计数。如果ID不是

数，则需要调整语法。这可以在表格或

a报告上完成。


要获得总天数，可以使用DSum（）。


= DSum（" Nz（[DateReturn]，Date（）） - [DateOut]"，" MyTable"，" [DateOut]> =＃"&

DateAdd（" yyyy"，-1，Date（））&"＃And [DateOut]< =＃"& Date（）&" #and

[BookID] ="& txtBookID）


Nz（）函数是为了捕获最后一个DateReturn，如果这本书没有

还没回来，将用今天的日期替换那个Null值。


-

Wayne Morgan

MS Access MVP

" Lindie" <黎**** @ magichans.com>在消息中写道

news：11 ********************** @ g44g2000cwa.googlegr oups.com ...

我读的越多，我就越困惑。群体中的日期计数太多了。

我需要知道过去一年中一本书被借出的频率 -
52周。
我的表已经

预订
DateOut
总计
DateReturn
总计应显示该书借出的次数我想在一本特定的书上做一个查询：给所有日期
然后以某种方式做另一个查询只给那些日期说
（23/06/2005减去52周）给出过去一年的日期
然后计算这样获得的日期数量：说6
就在那里。
过去的总天数会很高兴一年，但那将是锦上添花......

但是怎么样？
Lindie

我我非常感谢Wayne并将尝试这个tomorow

L

非常感谢Wayne。我为什么要使用两个ID字段？ TxtBookID和

BookID？一个人就足够了。表达式上写着-I think-

，TxtBookID应与BookID相同。


Lindie

The more I read the more confused I get. Too much on dates calulations
in the groups.

I need to know how often a book has been loaned out over the past year-
52 weeks.
My table has

Book
DateOut
Total
DateReturn

the Total should show the number of times the book has been loaned over
the past year.
I thought doing a querie on a particular book: gives all dates
then somehow do another querie to give only those dates from say
(23/06/2005 minus 52 weeks) giving the dates over the past year
then to count the number of dates thus obtained: say 6
and there it is.
Would be nice to have the total days over the past year too but that
would be icing..

But how?
Lindie

解决方案

First, do you want to know only if the check out occurred during the time
specified (i.e. do you want to include a count for the book being out
already when the specified time period started)?

You wouldn''t put a Total field in your table. You would calculate this when
needed. If all you want is the number of times the book began a checked out
period during the specified time, then limit the DateOut date to the
specified time and count the number or records returned.

Using VBA (this could be used as a Control Source for a calculated textbox):
=DCount("*", "MyTable", "[DateOut] >= #" & DateAdd("yyyy", -1, Date()) & "#
And [DateOut] <= #" & Date() & "# And [BookID] = " & txtBookID)

This checks for DateOut being between today and 1 year ago and also limits
the count to the current book using the value of the book''s ID field in the
book''s record as displayed in the textbox txtBookID. If the ID isn''t a
number, the syntax will need to be adjusted. This could be done on a form or
a report.

To get the total number of days out, you could use DSum().

=DSum("Nz([DateReturn], Date()) - [DateOut]", "MyTable", "[DateOut] >= #" &
DateAdd("yyyy", -1, Date()) & "# And [DateOut] <= #" & Date() & "# And
[BookID] = " & txtBookID)

The Nz() function is to catch the last DateReturn if the book hasn''t been
returned yet and will replace that Null value with today''s date.

--
Wayne Morgan
MS Access MVP
"Lindie" <Li****@magichans.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

The more I read the more confused I get. Too much on dates calulations
in the groups.

I need to know how often a book has been loaned out over the past year-
52 weeks.
My table has

Book
DateOut
Total
DateReturn

the Total should show the number of times the book has been loaned over
the past year.
I thought doing a querie on a particular book: gives all dates
then somehow do another querie to give only those dates from say
(23/06/2005 minus 52 weeks) giving the dates over the past year
then to count the number of dates thus obtained: say 6
and there it is.
Would be nice to have the total days over the past year too but that
would be icing..

But how?
Lindie

I''m grateful for that Wayne and will try this out tomorow
L

Many thanks Wayne. Why should I be using two ID fields ? TxtBookID and
BookID? One would suffice me nicely. The expression says -I think-
that the TxtBookID should be the same as the BookID.

Lindie

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