Dates confusions

**Wayne Morgan** · Nov 13 '05, 12:17 PM

Re: Dates confusions

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" <Lindie@magicha ns.com> wrote in message
news:1119561647 .038297.145950@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> 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
>[/color]

**Lindie** · Nov 13 '05, 12:17 PM

Re: Dates confusions

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

**Lindie** · Nov 13 '05, 12:24 PM

Re: Dates confusions

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

**Lindie** · Nov 13 '05, 12:24 PM

Re: Dates confusions

Wayne,

on the second lines of code, by using just one book and two dates
out/return 01/01/2005///02/01/2005 I get a total number of days as
791.

something is amis!
Lindie

**Larry Linson** · Nov 13 '05, 12:26 PM

Re: Dates confusions

"Lindie" <Lindie@magicha ns.com> wrote in message
news:1119998748 .117689.68310@z 14g2000cwz.goog legroups.com...[color=blue]
> 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.[/color]

As Wayne and I use similar naming conventions, almost certainly txtBookID
refers to a Text Box Control on a Form that displays and/or into which the
user will enter the value of the Field named BookID. Oh, just went back and
read his post in detail, and he states exactly that... "book's record as
displayed in the _textbox_ txtBookID" (emphaisis mine on textbox).

Larry Linson
Microsoft Access MVP

**Lindie** · Nov 13 '05, 12:26 PM

Re: Dates confusions

I feel sooo stupid. many thanks; works a treat.

Lindie[color=blue]
>[/color]

**Lindie** · Nov 13 '05, 12:44 PM

Re: Dates confusions

There I am having contructed a Mainform with a subform. The Main form
has the nice DCount and Dsum as above. But, when I enter new data into
the subform the counts simply will not update.
the subform is based on my Maintable and this is updated. So why on
earth does the code refuse to update my DCount and DSum?

Well, I am truly stuck up a gum tree.

Lindie

**Br@dley** · Nov 13 '05, 12:44 PM

Re: Dates confusions

Lindie <Lindie@magicha ns.com> wrote:[color=blue]
> There I am having contructed a Mainform with a subform. The Main form
> has the nice DCount and Dsum as above. But, when I enter new data into
> the subform the counts simply will not update.[/color]

Have you tried issuing a Me.ReCalc (or Forms!frmParent Form.Recalc)?
[color=blue]
> the subform is based on my Maintable and this is updated. So why on
> earth does the code refuse to update my DCount and DSum?
>
> Well, I am truly stuck up a gum tree.[/color]

What's the code?
--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response

**Wayne Morgan** · Nov 13 '05, 12:44 PM

Re: Dates confusions

Lindie,

You've entered the new data in the main form, but have you saved the record?
The DCount and DSum are getting their data from the table. If the record
hasn't been saved, the data isn't in the table.

If you have saved the record, then a Recalc, as mentioned by Bradley, should
help.

--
Wayne Morgan

"Lindie" <Lindie@magicha ns.com> wrote in message
news:1120520467 .307369.72250@g 14g2000cwa.goog legroups.com...[color=blue]
> There I am having contructed a Mainform with a subform. The Main form
> has the nice DCount and Dsum as above. But, when I enter new data into
> the subform the counts simply will not update.
> the subform is based on my Maintable and this is updated. So why on
> earth does the code refuse to update my DCount and DSum?
>
> Well, I am truly stuck up a gum tree.
>
> Lindie
>[/color]

**Lindie** · Nov 13 '05, 12:45 PM

Re: Dates confusions- getting somewhere!

Thanks for the guidance guys. I have looked at your solutions am
getting somewhere.
I have now moved from books to staff sickness as the same principles
seem to apply there.

1. The first Calculation[ total number of absences in last year] works
OK, the second one [total number od days over the last year]does not.
It only works when I enter FromDate and ToDate.

2. I also need to enter From: 2/5/05 to 2/5/05
rather than 2/5/05 To 3/5/05 if there has been just one
day off sick. How should I change the code for that?

I have two tables:
tblDetails tblSickList both linked
One-To-Many via StaffID

two forms:

FrmDetailssick linked to tblDetails
subDetailssick linked to tblSickList

the mainform FrmDetailssick has two calculated textboxes:

=DCount("*","Si ck1. List","[FromDate] >= #" & DateAdd("yyyy",-1,Date())
& "#And [FromDate] <= #" & Date() & "# And [StaffID] = " &
[txtStaffID])

this should give me the total number of absences over thee last year

=DSum("Nz([ToDate], Date()) - [FromDate]","SickList ","[ToDate] >= #" &
DateAdd("yyyy",-1,Date()) & "# And [ToDate] <= #" & Date() & "#
And[StaffID] = " & [txtStaffID])

this the total number of days off

The subDetailssick is continuous.

**Wayne Morgan** · Nov 13 '05, 12:46 PM

Re: Dates confusions- getting somewhere!

> =DSum("Nz([ToDate], Date()) - [FromDate]","SickList ","[ToDate] >= #" &[color=blue]
> DateAdd("yyyy",-1,Date()) & "# And [ToDate] <= #" & Date() & "#
> And[StaffID] = " & [txtStaffID])[/color]

As you acknowledge by the use of Nz in the first part of the equation,
ToDate may be Null. If it is, it will fail in the criteria part of the
equation. Also, since you are only wanting the number of days taken from 1
year ago to today, you need to modify the equation in the first part. In the
first part, FromDate should be either FromDate or 1 year ago today in order
to get the dates that are only within the last year for sick leave already
in progress at that time.

See if this helps:
=DSum("Nz([ToDate], Date()) - IIf([FromDate]< DateAdd("yyyy", -1, Date()),
DateAdd("yyyy", -1, Date()), [FromDate]), "SickList", "(([ToDate] >= #" &
DateAdd("yyyy", -1, Date()) & "# And [ToDate] <= #" & Date() & "#) Or
[ToDate] Is Null) And [StaffID] = " & [txtStaffID])

--
Wayne Morgan
MS Access MVP

"Lindie" <Lindie@magicha ns.com> wrote in message
news:1120576949 .909805.43680@g 14g2000cwa.goog legroups.com...[color=blue]
> Thanks for the guidance guys. I have looked at your solutions am
> getting somewhere.
> I have now moved from books to staff sickness as the same principles
> seem to apply there.
>
> 1. The first Calculation[ total number of absences in last year] works
> OK, the second one [total number od days over the last year]does not.
> It only works when I enter FromDate and ToDate.
>
> 2. I also need to enter From: 2/5/05 to 2/5/05
> rather than 2/5/05 To 3/5/05 if there has been just one
> day off sick. How should I change the code for that?
>
> I have two tables:
> tblDetails tblSickList both linked
> One-To-Many via StaffID
>
> two forms:
>
> FrmDetailssick linked to tblDetails
> subDetailssick linked to tblSickList
>
> the mainform FrmDetailssick has two calculated textboxes:
>
> =DCount("*","Si ck1. List","[FromDate] >= #" & DateAdd("yyyy",-1,Date())
> & "#And [FromDate] <= #" & Date() & "# And [StaffID] = " &
> [txtStaffID])
>
> this should give me the total number of absences over thee last year
>
> =DSum("Nz([ToDate], Date()) - [FromDate]","SickList ","[ToDate] >= #" &
> DateAdd("yyyy",-1,Date()) & "# And [ToDate] <= #" & Date() & "#
> And[StaffID] = " & [txtStaffID])
>
> this the total number of days off
>
> The subDetailssick is continuous.
>[/color]

**Lindie** · Nov 13 '05, 12:47 PM

Re: Dates confusions- getting somewhere!

this is becomming a challenging tutorial for me. I'm very grateful for
this all. However, when I enter the code as suggested I get
"#Name?"
I have looked at which part is wrong but the more I change bits the
more of a mess I get.

I have looked all over the groups on the Nz() and find this most
illuminating but the "#Name?" ???

Lindie

**Br@dley** · Nov 13 '05, 12:48 PM

Re: Dates confusions- getting somewhere!

Lindie <Lindie@magicha ns.com> wrote:[color=blue]
> this is becomming a challenging tutorial for me. I'm very grateful for
> this all. However, when I enter the code as suggested I get
> "#Name?"
> I have looked at which part is wrong but the more I change bits the
> more of a mess I get.
>
> I have looked all over the groups on the Nz() and find this most
> illuminating but the "#Name?" ???
>
> Lindie[/color]

That just means Access can't interpret the information your giving it.
(eg. setting a text control's controlsource to an invalid fieldname).
--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response

**Wayne Morgan** · Nov 13 '05, 12:49 PM

Re: Dates confusions- getting somewhere!

#Name means Access can't resolve a name in the equation. The reason for this
can be as simple as a textbox and it's field having the same name and Access
doesn't know which of the two to use. If that is the case, just rename the
textbox. For example, if the field is called MyField, change the name of the
textbox to txtMyField so that the names are different.

--
Wayne Morgan
MS Access MVP

"Lindie" <Lindie@magicha ns.com> wrote in message
news:1120685471 .107650.60480@g 43g2000cwa.goog legroups.com...[color=blue]
> this is becomming a challenging tutorial for me. I'm very grateful for
> this all. However, when I enter the code as suggested I get
> "#Name?"
> I have looked at which part is wrong but the more I change bits the
> more of a mess I get.
>
> I have looked all over the groups on the Nz() and find this most
> illuminating but the "#Name?" ???
>
> Lindie
>[/color]

Dates confusions

Dates confusions

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment