Dates confusions

[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

[Wayne Morgan]

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

[Lindie]

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

[Lindie]

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]

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]

"Lindie" <Li****@magichans.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

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.

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]

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

Lindie

[Lindie]

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]

Lindie <Li****@magichans.com> wrote:

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.
Have you tried issuing a Me.ReCalc (or Forms!frmParentForm.Recalc)?
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.

What's the code?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Wayne Morgan]

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" <Li****@magichans.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

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

[Lindie]

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("*","Sick1. 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]

> =DSum("Nz([ToDate], Date()) - [FromDate]","SickList","[ToDate] >= #" &

DateAdd("yyyy",-1,Date()) & "# And [ToDate] <= #" & Date() & "#
And[StaffID] = " & [txtStaffID])

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" <Li****@magichans.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com... 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("*","Sick1. 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.

[Lindie]

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]

Lindie <Li****@magichans.com> wrote:

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

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]

#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" <Li****@magichans.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

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

[Lindie]

Sensible replies but well, doesn't work for me. I have checked names,
altered them but no luck.

Main form: TxtStaffID bound to StaffID

subform: TxtFromDate TxtToDate bound to FromDate and ToDate

The earlier version was as below and this worked but only when both

From and T dates were enered. The second code should have solved this

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

New code
=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])

As I seem to be using the same names I cannot understand where I go
wrong. I guess with this more complicated code I should alter something
but my attemps by substituting TxtToDate" ect have failed.

Any illumination?
Lindie

[Lindie]

something else is driving me potty. the part

DateAdd("yyyy",-1,Date()) is fine but now I want to have 6 months
ago so

DateAdd("m",-6,Date()) should do it, I thought. Not so. Why, oh
Why??

desperate Lindie

[Br]

Lindie <Li****@magichans.com> wrote:

something else is driving me potty. the part

DateAdd("yyyy",-1,Date()) is fine but now I want to have 6
months ago so

DateAdd("m",-6,Date()) should do it, I thought. Not so. Why, oh
Why??

desperate Lindie

Try "mm" ?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Wayne Morgan]

> DateAdd("m",-6,Date()) should do it, I thought. Not so. Why, oh

What is it giving you? It gives me 6 months ago today (7 January 2005).

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

something else is driving me potty. the part

DateAdd("yyyy",-1,Date()) is fine but now I want to have 6 months
ago so

DateAdd("m",-6,Date()) should do it, I thought. Not so. Why, oh
Why??

desperate Lindie

[Wayne Morgan]

Is this code located on the parent form or subform?

--
Wayne Morgan
MS Access MVP
"Lindie" <Li****@magichans.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Sensible replies but well, doesn't work for me. I have checked names,
altered them but no luck.

Main form: TxtStaffID bound to StaffID

subform: TxtFromDate TxtToDate bound to FromDate and ToDate

The earlier version was as below and this worked but only when both

From and T dates were enered. The second code should have solved this

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

New code
=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])

As I seem to be using the same names I cannot understand where I go
wrong. I guess with this more complicated code I should alter something
but my attemps by substituting TxtToDate" ect have failed.

Any illumination?
Lindie

[Lindie]

The code is on the parent form. Would that be the trouble?
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

Lindie ps. I'll try "mm", and let you know what
happens.

[Wayne Morgan]

Yes, the location of these items makes a difference. If the values aren't
available to the main form, it won't find them and will give a #Name error.
It won't find them on the subform unless you tell it to look there. To refer
to a control on the subform, the syntax would be

NameOfSubformControl.Form.NameOfControlOnSubform

The NameOfSubformControl may or may not be the same as the name of your
subform. It is actually the name of the control that holds the subform. This
is the same control where you set the Link Master and Link Child properties.
However, it appears that in this case, the problem may just be that I left a
quote mark out. While the ToDate and FromDate are on the subform, there
aren't being used in this equation. The fields are being referred to
directly from the table.

So, to change what you have:

=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" <Li****@magichans.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

The code is on the parent form. Would that be the trouble?
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

Lindie ps. I'll try "mm", and let you know what
happens.

[Lindie]

I checked the " " and they all seem to be there. The references seem OK
too. The code on the mainform refers directly as you said to the main
table "SickList" which has the fields FromDate and ToDate. It should
work as far as I can see.

I am also still struggling with the date calculations. I just cannot
get the thing to work. and it looks so very simple. I am using General
Date so where oh where am am straying off? Enough to put one off
dating forever.

DateAdd("m",-6,Date())

Lindie

[Br]

Lindie <Li****@magichans.com> wrote:

I checked the " " and they all seem to be there. The references seem
OK too. The code on the mainform refers directly as you said to the
main table "SickList" which has the fields FromDate and ToDate. It
should work as far as I can see.

I am also still struggling with the date calculations. I just cannot
get the thing to work. and it looks so very simple. I am using
General Date so where oh where am am straying off? Enough to put one off dating forever.
Awww. You'll find the right guy some day ;)
DateAdd("m",-6,Date())

So what happens with this? It looks ok.

How big is the DB ZIPed? If you're really stuck I can take a look for
you..... (bradleyATcomcenDOTcomDOTau). Sometimes it's easier to get
hands on.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Wayne Morgan]

Lindie, what localization are you using? What format is "normal" for dates
in your area? Will you zip a copy of the file and send it to me at
co*****@hotmail.com? Place a note in the message telling me where I need to
look.

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

I checked the " " and they all seem to be there. The references seem OK
too. The code on the mainform refers directly as you said to the main
table "SickList" which has the fields FromDate and ToDate. It should
work as far as I can see.

I am also still struggling with the date calculations. I just cannot
get the thing to work. and it looks so very simple. I am using General
Date so where oh where am am straying off? Enough to put one off
dating forever.

DateAdd("m",-6,Date())

Lindie

[Lindie]

Thanks, will email the file.
We are in GB so dd/mm/yyyy is normal .
Lindie

[Br]

Lindie <Li****@magichans.com> wrote:

Thanks, will email the file.
We are in GB so dd/mm/yyyy is normal .
Lindie

I love that... "normal". hehehehe
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Wayne Morgan]

I'll look for the file. I know the format of the date affects some items,
such as query filters, but I don't think it affects the DateAdd() function.
As a test, you may want to try:

DateAdd("m", -6, Format(Date(), "mm/dd/yyyy"))

--
Wayne Morgan
MS Access MVP
"Lindie" <Li****@magichans.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Thanks, will email the file.
We are in GB so dd/mm/yyyy is normal .
Lindie

[Wayne Morgan]

Lindie,

If you've sent the file, it never got here.

--
Wayne Morgan
MS Access MVP
"Lindie" <Li****@magichans.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Thanks, will email the file.
We are in GB so dd/mm/yyyy is normal .
Lindie

[Lindie]

Sent again; first one must have been cyberjacked.
L

[Wayne Morgan]

Lindie,

Let's try it one more time. I had the "junk email filter" set to auto
delete. It must have been picking your message up as "junk". I've reset it
to send "junk" to the junk mail folder, so even if it is picked up that way,
I ought to get it.

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

Sent again; first one must have been cyberjacked.
L

[Lindie]

Gone again; don't blame you for having spam filtering; we get lots of
junk all part of that wonderful cyber experience.
L

[Br]

Lindie <Li****@magichans.com> wrote:

Gone again; don't blame you for having spam filtering; we get lots of
junk all part of that wonderful cyber experience.
L

Nah, I don't get any junk at all. It's all about smart use of your email
addresses (note plural:)

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Wayne Morgan]

Lindie,

I sent the corrected database back to you in a reply to the email you sent
me. The "Name" error was the qoutation marks needing a little work. Once
that was fixed, there was another error saying that the equation had to
start with a =. It turned out that there was a space in front of the =.

Corrected equation:
=DSum("Nz([ToDate], Date()) -
IIf([FromDate]<DateAdd(""yyyy"", -1,Date()),DateAdd(""yyyy"", -1, Date()),
[FromDate]) + 1","SickList","(([ToDate] >= #" & DateAdd("yyyy",-1,Date()) &
"# And [ToDate] <= #" & Date() & "#) Or IsNull([ToDate])) And [StaffID] = "
& [txtStaffID])

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

Gone again; don't blame you for having spam filtering; we get lots of
junk all part of that wonderful cyber experience.
L

[Lindie]

Wow, that did the trick allright! I have been looking where these " "
were missing, still looking.
I like the extra -+1- that's very neat entering just the one date. What
I cannot grasp it that in one part there is ""yyyy"" whereas in an
other bit just "yyyy" What is the difference?

Must get myself some more email addresses if that will clam down the
junk. My Dad claims that if everyone who gets an invite to buy viagra
would order some then there wouldn't be enough of the stuff in any
universe to go around.

Lindie

[Wayne Morgan]

The difference between ""yyyy"" and "yyyy" is where they are located. DSum
uses strings as its parameters, so each item is between quotes. To enter
quotes inside a quoted string, you have to double them. This tells VBA to
insert a quote instead of it being the end of the string. The second one is
actually outside the quotes because were are concatenating in a value from
the form. The quotes won't be in the string, just the value from the form.
In the first one (the one with the doubled quotes), DSum actually runs the
DateAdd() calculation instead of the value being concatenated in. It needs
to be done this way because in the first one the value in the DateAdd() is
coming from the current record of the table as DSum steps through the
records.

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

Wow, that did the trick allright! I have been looking where these " "
were missing, still looking.
I like the extra -+1- that's very neat entering just the one date. What
I cannot grasp it that in one part there is ""yyyy"" whereas in an
other bit just "yyyy" What is the difference?

Must get myself some more email addresses if that will clam down the
junk. My Dad claims that if everyone who gets an invite to buy viagra
would order some then there wouldn't be enough of the stuff in any
universe to go around.

Lindie

[Lindie]

Thanks for all your help and patience Wayne and Br@dley. I'm now
studying all the above in the hope I learn more. Have bought myself a
simple book on VBA as bedtime reading. Don't think it will improve the
mind a lot but may get me to sleep quicker.

Lindie