Totaling dates in a subform

[Joanne]

Hello,
I have a subform which keeps track of vacation days that employees have
requested. Each line in the subform has a begin date and an end date field.
At the end of each line is a text box, which is called txtTotalDays and it
totals the amount of days on the line.
At the bottom of the form (but still in the details section), I have another
text box that is supposed to total all the date counts in each txtTotalDays
box. The formula that I have in this text box is
=Format(Sum([TxtTotalCount]),"d"). I have also tried it in the footer but I
understand that calculated fields cannot be put in footers. I've tried it
without the format command, just the sum. Any way I try it, I get an error.
Can you tell me what I'm doing wrong?
Thank you for your help.

 

[Jeff Boyce]

Joanne

"I get an error" -- what does it say?

You describe your form, but forms in Access are based on tables (or on
queries that are based on tables). What is the data?

"I understand that calculated fields cannot be put in footers." Why not? I
do...

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Joanne

"I get an error" -- what does it say?

You describe your form, but forms in Access are based on tables (or on
queries that are based on tables). What is the data?

"I understand that calculated fields cannot be put in footers." Why not? I
do...

More specific description may lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Beetle]

I understand that calculated fields cannot be put in footers.

That's incorrect. The footer is where your calculated control for
the overall total *should* go. However, you cannot use another
calculated control as the basis for this calculation. You have to
perform the original calculation again and Sum that.
For example;

If the expression for your txtTotalCount control in the detail section is;

=DateDiff("d", [BeginDate], [EndDate])

Then the expression for the txtOverallTotal control in the footer
would be;

=Sum(DateDiff("d", [BeginDate], [EndDate]))

 

[Beetle]

I understand that calculated fields cannot be put in footers.

That's incorrect. The footer is where your calculated control for
the overall total *should* go. However, you cannot use another
calculated control as the basis for this calculation. You have to
perform the original calculation again and Sum that.
For example;

If the expression for your txtTotalCount control in the detail section is;

=DateDiff("d", [BeginDate], [EndDate])

Then the expression for the txtOverallTotal control in the footer
would be;

=Sum(DateDiff("d", [BeginDate], [EndDate]))

 

[Joanne]

Thank you so much Beetle! This works perfectly.

I understand that calculated fields cannot be put in footers.

That's incorrect. The footer is where your calculated control for
the overall total *should* go. However, you cannot use another
calculated control as the basis for this calculation. You have to
perform the original calculation again and Sum that.
For example;

If the expression for your txtTotalCount control in the detail section is;

=DateDiff("d", [BeginDate], [EndDate])

Then the expression for the txtOverallTotal control in the footer
would be;

=Sum(DateDiff("d", [BeginDate], [EndDate]))

--
_________

Sean Bailey

Hello,
I have a subform which keeps track of vacation days that employees have
requested. Each line in the subform has a begin date and an end date field.
At the end of each line is a text box, which is called txtTotalDays and it
totals the amount of days on the line.
At the bottom of the form (but still in the details section), I have another
text box that is supposed to total all the date counts in each txtTotalDays
box. The formula that I have in this text box is
=Format(Sum([TxtTotalCount]),"d"). I have also tried it in the footer but I
understand that calculated fields cannot be put in footers. I've tried it
without the format command, just the sum. Any way I try it, I get an error.
Can you tell me what I'm doing wrong?
Thank you for your help.

 

[Joanne]

Thank you so much Beetle! This works perfectly.

I understand that calculated fields cannot be put in footers.

That's incorrect. The footer is where your calculated control for
the overall total *should* go. However, you cannot use another
calculated control as the basis for this calculation. You have to
perform the original calculation again and Sum that.
For example;

If the expression for your txtTotalCount control in the detail section is;

=DateDiff("d", [BeginDate], [EndDate])

Then the expression for the txtOverallTotal control in the footer
would be;

=Sum(DateDiff("d", [BeginDate], [EndDate]))

--
_________

Sean Bailey

Hello,
I have a subform which keeps track of vacation days that employees have
requested. Each line in the subform has a begin date and an end date field.
At the end of each line is a text box, which is called txtTotalDays and it
totals the amount of days on the line.
At the bottom of the form (but still in the details section), I have another
text box that is supposed to total all the date counts in each txtTotalDays
box. The formula that I have in this text box is
=Format(Sum([TxtTotalCount]),"d"). I have also tried it in the footer but I
understand that calculated fields cannot be put in footers. I've tried it
without the format command, just the sum. Any way I try it, I get an error.
Can you tell me what I'm doing wrong?
Thank you for your help.

 

[Joanne]

Sorry, I spoke to soon. Thanks to Beetle I no longer get an error and I
understand about calculated fields, but it is not totaling the number of days
correctly. As an example, we have one employee, John, who has taken only one
vacation day so far. There is a text box called "OLP Begin Date" and the
value is 3/10/2009. Then there is a text box called OLP End Date and the
value is 3/11/2009. The calculated field ( a text box called
"txttotalcount") at the end of that line correctly displays the number 1. The
code in that field is "=DateDiff("d",[OLP Begin Date],[OLP End Date])". Then
in the footer of the form, I want to sum all values of txttotalcount. In
this case there is only one day but in the text box in the footer it displays
the number 3. The formula is =Sum(DateDiff("d",[OLP Begin Date],[OLP End
Date])). Can you tell me what's wrong? I don't understand this. Thank you
very much.

I understand that calculated fields cannot be put in footers.

That's incorrect. The footer is where your calculated control for
the overall total *should* go. However, you cannot use another
calculated control as the basis for this calculation. You have to
perform the original calculation again and Sum that.
For example;

If the expression for your txtTotalCount control in the detail section is;

=DateDiff("d", [BeginDate], [EndDate])

Then the expression for the txtOverallTotal control in the footer
would be;

=Sum(DateDiff("d", [BeginDate], [EndDate]))

--
_________

Sean Bailey

Hello,
I have a subform which keeps track of vacation days that employees have
requested. Each line in the subform has a begin date and an end date field.
At the end of each line is a text box, which is called txtTotalDays and it
totals the amount of days on the line.
At the bottom of the form (but still in the details section), I have another
text box that is supposed to total all the date counts in each txtTotalDays
box. The formula that I have in this text box is
=Format(Sum([TxtTotalCount]),"d"). I have also tried it in the footer but I
understand that calculated fields cannot be put in footers. I've tried it
without the format command, just the sum. Any way I try it, I get an error.
Can you tell me what I'm doing wrong?
Thank you for your help.

 

[Joanne]

Sorry, I spoke to soon. Thanks to Beetle I no longer get an error and I
understand about calculated fields, but it is not totaling the number of days
correctly. As an example, we have one employee, John, who has taken only one
vacation day so far. There is a text box called "OLP Begin Date" and the
value is 3/10/2009. Then there is a text box called OLP End Date and the
value is 3/11/2009. The calculated field ( a text box called
"txttotalcount") at the end of that line correctly displays the number 1. The
code in that field is "=DateDiff("d",[OLP Begin Date],[OLP End Date])". Then
in the footer of the form, I want to sum all values of txttotalcount. In
this case there is only one day but in the text box in the footer it displays
the number 3. The formula is =Sum(DateDiff("d",[OLP Begin Date],[OLP End
Date])). Can you tell me what's wrong? I don't understand this. Thank you
very much.

I understand that calculated fields cannot be put in footers.

That's incorrect. The footer is where your calculated control for
the overall total *should* go. However, you cannot use another
calculated control as the basis for this calculation. You have to
perform the original calculation again and Sum that.
For example;

If the expression for your txtTotalCount control in the detail section is;

=DateDiff("d", [BeginDate], [EndDate])

Then the expression for the txtOverallTotal control in the footer
would be;

=Sum(DateDiff("d", [BeginDate], [EndDate]))

--
_________

Sean Bailey

Hello,
I have a subform which keeps track of vacation days that employees have
requested. Each line in the subform has a begin date and an end date field.
At the end of each line is a text box, which is called txtTotalDays and it
totals the amount of days on the line.
At the bottom of the form (but still in the details section), I have another
text box that is supposed to total all the date counts in each txtTotalDays
box. The formula that I have in this text box is
=Format(Sum([TxtTotalCount]),"d"). I have also tried it in the footer but I
understand that calculated fields cannot be put in footers. I've tried it
without the format command, just the sum. Any way I try it, I get an error.
Can you tell me what I'm doing wrong?
Thank you for your help.

 

[Joanne]

Hi,
Is there anyone out there who can help me on this? I'm really stuck.

Sorry, I spoke to soon. Thanks to Beetle I no longer get an error and I
understand about calculated fields, but it is not totaling the number of days
correctly. As an example, we have one employee, John, who has taken only one
vacation day so far. There is a text box called "OLP Begin Date" and the
value is 3/10/2009. Then there is a text box called OLP End Date and the
value is 3/11/2009. The calculated field ( a text box called
"txttotalcount") at the end of that line correctly displays the number 1. The
code in that field is "=DateDiff("d",[OLP Begin Date],[OLP End Date])". Then
in the footer of the form, I want to sum all values of txttotalcount. In
this case there is only one day but in the text box in the footer it displays
the number 3. The formula is =Sum(DateDiff("d",[OLP Begin Date],[OLP End
Date])). Can you tell me what's wrong? I don't understand this. Thank you
very much.

I understand that calculated fields cannot be put in footers.

That's incorrect. The footer is where your calculated control for
the overall total *should* go. However, you cannot use another
calculated control as the basis for this calculation. You have to
perform the original calculation again and Sum that.
For example;

If the expression for your txtTotalCount control in the detail section is;

=DateDiff("d", [BeginDate], [EndDate])

Then the expression for the txtOverallTotal control in the footer
would be;

=Sum(DateDiff("d", [BeginDate], [EndDate]))

--
_________

Sean Bailey

Hello,
I have a subform which keeps track of vacation days that employees have
requested. Each line in the subform has a begin date and an end date field.
At the end of each line is a text box, which is called txtTotalDays and it
totals the amount of days on the line.
At the bottom of the form (but still in the details section), I have another
text box that is supposed to total all the date counts in each txtTotalDays
box. The formula that I have in this text box is
=Format(Sum([TxtTotalCount]),"d"). I have also tried it in the footer but I
understand that calculated fields cannot be put in footers. I've tried it
without the format command, just the sum. Any way I try it, I get an error.
Can you tell me what I'm doing wrong?
Thank you for your help.

 

[Joanne]

Hi,
Is there anyone out there who can help me on this? I'm really stuck.

Sorry, I spoke to soon. Thanks to Beetle I no longer get an error and I
understand about calculated fields, but it is not totaling the number of days
correctly. As an example, we have one employee, John, who has taken only one
vacation day so far. There is a text box called "OLP Begin Date" and the
value is 3/10/2009. Then there is a text box called OLP End Date and the
value is 3/11/2009. The calculated field ( a text box called
"txttotalcount") at the end of that line correctly displays the number 1. The
code in that field is "=DateDiff("d",[OLP Begin Date],[OLP End Date])". Then
in the footer of the form, I want to sum all values of txttotalcount. In
this case there is only one day but in the text box in the footer it displays
the number 3. The formula is =Sum(DateDiff("d",[OLP Begin Date],[OLP End
Date])). Can you tell me what's wrong? I don't understand this. Thank you
very much.

I understand that calculated fields cannot be put in footers.

That's incorrect. The footer is where your calculated control for
the overall total *should* go. However, you cannot use another
calculated control as the basis for this calculation. You have to
perform the original calculation again and Sum that.
For example;

If the expression for your txtTotalCount control in the detail section is;

=DateDiff("d", [BeginDate], [EndDate])

Then the expression for the txtOverallTotal control in the footer
would be;

=Sum(DateDiff("d", [BeginDate], [EndDate]))

--
_________

Sean Bailey

Hello,
I have a subform which keeps track of vacation days that employees have
requested. Each line in the subform has a begin date and an end date field.
At the end of each line is a text box, which is called txtTotalDays and it
totals the amount of days on the line.
At the bottom of the form (but still in the details section), I have another
text box that is supposed to total all the date counts in each txtTotalDays
box. The formula that I have in this text box is
=Format(Sum([TxtTotalCount]),"d"). I have also tried it in the footer but I
understand that calculated fields cannot be put in footers. I've tried it
without the format command, just the sum. Any way I try it, I get an error.
Can you tell me what I'm doing wrong?
Thank you for your help.

 

[John W. Vinson]

Sorry, I spoke to soon. Thanks to Beetle I no longer get an error and I
understand about calculated fields, but it is not totaling the number of days
correctly. As an example, we have one employee, John, who has taken only one
vacation day so far. There is a text box called "OLP Begin Date" and the
value is 3/10/2009. Then there is a text box called OLP End Date and the
value is 3/11/2009. The calculated field ( a text box called
"txttotalcount") at the end of that line correctly displays the number 1. The
code in that field is "=DateDiff("d",[OLP Begin Date],[OLP End Date])". Then
in the footer of the form, I want to sum all values of txttotalcount. In
this case there is only one day but in the text box in the footer it displays
the number 3. The formula is =Sum(DateDiff("d",[OLP Begin Date],[OLP End
Date])). Can you tell me what's wrong? I don't understand this. Thank you
very much.

A Sum in a Footer cannot refer to a *textbox* - only to a field in the Query.

Edit the Query that you're using as the recordsource for the field, and put
the datediff expression in a vacant Field cell:

Totalcount: DateDiff("d",[OLP Begin Date],[OLP End Date])

You can then bind txtTotalCount to this field, and put

=Sum([Totalcount])

as the control source of a textbox in the footer.

 

[John W. Vinson]

Sorry, I spoke to soon. Thanks to Beetle I no longer get an error and I
understand about calculated fields, but it is not totaling the number of days
correctly. As an example, we have one employee, John, who has taken only one
vacation day so far. There is a text box called "OLP Begin Date" and the
value is 3/10/2009. Then there is a text box called OLP End Date and the
value is 3/11/2009. The calculated field ( a text box called
"txttotalcount") at the end of that line correctly displays the number 1. The
code in that field is "=DateDiff("d",[OLP Begin Date],[OLP End Date])". Then
in the footer of the form, I want to sum all values of txttotalcount. In
this case there is only one day but in the text box in the footer it displays
the number 3. The formula is =Sum(DateDiff("d",[OLP Begin Date],[OLP End
Date])). Can you tell me what's wrong? I don't understand this. Thank you
very much.

A Sum in a Footer cannot refer to a *textbox* - only to a field in the Query.

Edit the Query that you're using as the recordsource for the field, and put
the datediff expression in a vacant Field cell:

Totalcount: DateDiff("d",[OLP Begin Date],[OLP End Date])

You can then bind txtTotalCount to this field, and put

=Sum([Totalcount])

as the control source of a textbox in the footer.

 

[Joanne]

Thank you very much for your response, but I am very stupid when it comes to
Access databases and I can't ever skip a step. I'm not using a query
anywhere. All I have is a subform, with entries for vacation days on each
line and at the end of each line a calculated field with the formula:
=DateDiff("d",[OLP Begin Date],[OLP End Date]). It is correctly calculating.
But the calculated field in the foote: =Sum(DateDiff("d", [BeginDate],
[EndDate]) is not correct. I thought a calculated field had to be an unbound
text box. I just don't understand when calculations are allowed and what
they can refer to. It's arcane. Where do I put the query? In the first
calculated field? In the footer? Is it in addition to what I've got? I'm
sorry. I'm just so confused. It seems like this should be easier even for
dopes like me.

Sorry, I spoke to soon. Thanks to Beetle I no longer get an error and I
understand about calculated fields, but it is not totaling the number of days
correctly. As an example, we have one employee, John, who has taken only one
vacation day so far. There is a text box called "OLP Begin Date" and the
value is 3/10/2009. Then there is a text box called OLP End Date and the
value is 3/11/2009. The calculated field ( a text box called
"txttotalcount") at the end of that line correctly displays the number 1. The
code in that field is "=DateDiff("d",[OLP Begin Date],[OLP End Date])". Then
in the footer of the form, I want to sum all values of txttotalcount. In
this case there is only one day but in the text box in the footer it displays
the number 3. The formula is =Sum(DateDiff("d",[OLP Begin Date],[OLP End
Date])). Can you tell me what's wrong? I don't understand this. Thank you
very much.

A Sum in a Footer cannot refer to a *textbox* - only to a field in the Query.

Edit the Query that you're using as the recordsource for the field, and put
the datediff expression in a vacant Field cell:

Totalcount: DateDiff("d",[OLP Begin Date],[OLP End Date])

You can then bind txtTotalCount to this field, and put

=Sum([Totalcount])

as the control source of a textbox in the footer.

 

[Joanne]

Thank you very much for your response, but I am very stupid when it comes to
Access databases and I can't ever skip a step. I'm not using a query
anywhere. All I have is a subform, with entries for vacation days on each
line and at the end of each line a calculated field with the formula:
=DateDiff("d",[OLP Begin Date],[OLP End Date]). It is correctly calculating.
But the calculated field in the foote: =Sum(DateDiff("d", [BeginDate],
[EndDate]) is not correct. I thought a calculated field had to be an unbound
text box. I just don't understand when calculations are allowed and what
they can refer to. It's arcane. Where do I put the query? In the first
calculated field? In the footer? Is it in addition to what I've got? I'm
sorry. I'm just so confused. It seems like this should be easier even for
dopes like me.

Sorry, I spoke to soon. Thanks to Beetle I no longer get an error and I
understand about calculated fields, but it is not totaling the number of days
correctly. As an example, we have one employee, John, who has taken only one
vacation day so far. There is a text box called "OLP Begin Date" and the
value is 3/10/2009. Then there is a text box called OLP End Date and the
value is 3/11/2009. The calculated field ( a text box called
"txttotalcount") at the end of that line correctly displays the number 1. The
code in that field is "=DateDiff("d",[OLP Begin Date],[OLP End Date])". Then
in the footer of the form, I want to sum all values of txttotalcount. In
this case there is only one day but in the text box in the footer it displays
the number 3. The formula is =Sum(DateDiff("d",[OLP Begin Date],[OLP End
Date])). Can you tell me what's wrong? I don't understand this. Thank you
very much.

A Sum in a Footer cannot refer to a *textbox* - only to a field in the Query.

Edit the Query that you're using as the recordsource for the field, and put
the datediff expression in a vacant Field cell:

Totalcount: DateDiff("d",[OLP Begin Date],[OLP End Date])

You can then bind txtTotalCount to this field, and put

=Sum([Totalcount])

as the control source of a textbox in the footer.

 

[John W. Vinson]

Thank you very much for your response, but I am very stupid when it comes to
Access databases and I can't ever skip a step. I'm not using a query
anywhere.

All Forms (main or subforms) are based on Queries. Every single one! It might
not be a query you wrote, but if not, it's one that Access created for you.

Open the form (the subform) in design view. View its Properties (right
mouseclick the little square at the upper left intersection of the rulers, and
select Properties). Find the form's Recordsource property. What's there? If
it's a table name, click the ... icon by it and accept Access' offer to open
it as a query design window.

All I have is a subform, with entries for vacation days on each
line and at the end of each line a calculated field with the formula:
=DateDiff("d",[OLP Begin Date],[OLP End Date]). It is correctly calculating.
But the calculated field in the foote: =Sum(DateDiff("d", [BeginDate],
[EndDate]) is not correct. I thought a calculated field had to be an unbound
text box. I just don't understand when calculations are allowed and what
they can refer to. It's arcane. Where do I put the query? In the first
calculated field? In the footer? Is it in addition to what I've got?

Just add the calculated field to the Recordsource query (as above), close the
query design window, and accept Access' offer to replace the recordsource.

 

[John W. Vinson]

Thank you very much for your response, but I am very stupid when it comes to
Access databases and I can't ever skip a step. I'm not using a query
anywhere.

All Forms (main or subforms) are based on Queries. Every single one! It might
not be a query you wrote, but if not, it's one that Access created for you.

Open the form (the subform) in design view. View its Properties (right
mouseclick the little square at the upper left intersection of the rulers, and
select Properties). Find the form's Recordsource property. What's there? If
it's a table name, click the ... icon by it and accept Access' offer to open
it as a query design window.

All I have is a subform, with entries for vacation days on each
line and at the end of each line a calculated field with the formula:
=DateDiff("d",[OLP Begin Date],[OLP End Date]). It is correctly calculating.
But the calculated field in the foote: =Sum(DateDiff("d", [BeginDate],
[EndDate]) is not correct. I thought a calculated field had to be an unbound
text box. I just don't understand when calculations are allowed and what
they can refer to. It's arcane. Where do I put the query? In the first
calculated field? In the footer? Is it in addition to what I've got?

Just add the calculated field to the Recordsource query (as above), close the
query design window, and accept Access' offer to replace the recordsource.

 

[Joanne]

IThank you so much for your help. This worked perfectly.

Thank you very much for your response, but I am very stupid when it comes to
Access databases and I can't ever skip a step. I'm not using a query
anywhere.

All Forms (main or subforms) are based on Queries. Every single one! It might
not be a query you wrote, but if not, it's one that Access created for you.

Open the form (the subform) in design view. View its Properties (right
mouseclick the little square at the upper left intersection of the rulers, and
select Properties). Find the form's Recordsource property. What's there? If
it's a table name, click the ... icon by it and accept Access' offer to open
it as a query design window.

All I have is a subform, with entries for vacation days on each
line and at the end of each line a calculated field with the formula:
=DateDiff("d",[OLP Begin Date],[OLP End Date]). It is correctly calculating.
But the calculated field in the foote: =Sum(DateDiff("d", [BeginDate],
[EndDate]) is not correct. I thought a calculated field had to be an unbound
text box. I just don't understand when calculations are allowed and what
they can refer to. It's arcane. Where do I put the query? In the first
calculated field? In the footer? Is it in addition to what I've got?

Just add the calculated field to the Recordsource query (as above), close the
query design window, and accept Access' offer to replace the recordsource.

 

[Joanne]

IThank you so much for your help. This worked perfectly.

Thank you very much for your response, but I am very stupid when it comes to
Access databases and I can't ever skip a step. I'm not using a query
anywhere.

All Forms (main or subforms) are based on Queries. Every single one! It might
not be a query you wrote, but if not, it's one that Access created for you.

Open the form (the subform) in design view. View its Properties (right
mouseclick the little square at the upper left intersection of the rulers, and
select Properties). Find the form's Recordsource property. What's there? If
it's a table name, click the ... icon by it and accept Access' offer to open
it as a query design window.

All I have is a subform, with entries for vacation days on each
line and at the end of each line a calculated field with the formula:
=DateDiff("d",[OLP Begin Date],[OLP End Date]). It is correctly calculating.
But the calculated field in the foote: =Sum(DateDiff("d", [BeginDate],
[EndDate]) is not correct. I thought a calculated field had to be an unbound
text box. I just don't understand when calculations are allowed and what
they can refer to. It's arcane. Where do I put the query? In the first
calculated field? In the footer? Is it in addition to what I've got?

Just add the calculated field to the Recordsource query (as above), close the
query design window, and accept Access' offer to replace the recordsource.