CodeNeed

[Guest]

I have 48 records On a sub form.
Fields
Date Physical Occupational Speech
10/1/04 25 10 30
10/2/04 30 25 15
10/3/04 10 10
10/4/04 15 25
10/5/04 25 25
10/6/04 15
10/7/04 25 25 25

There will probably be a command button which will ask the
user what date he wants. After typing in date, I need code
that will total numbers for that date plus the previous 6
dates. I don't know code and if button should be on main
form or sub form or even if button should be used.
Any help would be appreciated.

 

[Marshall Barton]

I have 48 records On a sub form.
Fields
Date Physical Occupational Speech
10/1/04 25 10 30
10/2/04 30 25 15
10/3/04 10 10
10/4/04 15 25
10/5/04 25 25
10/6/04 15
10/7/04 25 25 25

There will probably be a command button which will ask the
user what date he wants. After typing in date, I need code
that will total numbers for that date plus the previous 6
dates. I don't know code and if button should be on main
form or sub form or even if button should be used.

Not sure you even need any code. You can add text boxes to
the form's header or footer section using expressions like:

=Sum(IIF([DateField] Between txtdate And txtDate - 6,
Physical, 0))

where txtDate is the name of the text box where the usere
enters their date of interest.

 

[Guest]

That makes sense, however, question: I put text box named
"EnterDate" and a label the same in the foot of the Main
Form. I placed another text box with the suggested
expression as the Control Source. I get an error message
in the second text box. What am I doing wrong?

-----Original Message-----

I have 48 records On a sub form.
Fields
Date Physical Occupational Speech
10/1/04 25 10 30
10/2/04 30 25 15
10/3/04 10 10
10/4/04 15 25
10/5/04 25 25
10/6/04 15
10/7/04 25 25 25

There will probably be a command button which will ask the
user what date he wants. After typing in date, I need code
that will total numbers for that date plus the previous 6
dates. I don't know code and if button should be on main
form or sub form or even if button should be used.

Not sure you even need any code. You can add text boxes to
the form's header or footer section using expressions like:

=Sum(IIF([DateField] Between txtdate And txtDate - 6,
Physical, 0))

where txtDate is the name of the text box where the usere
enters their date of interest.

 

[Marshall Barton]

You'll have to be more explicit and provide the ***exact***
details of what you did. What is the name of the two text
box controls on the form? What are the names of the fields
in the table? What expression did you use? If possible,
please use Copy/Paste from your program into your post so
there is no possibility of a typo.

At this point all I can do is suggest that you double check
the names of everything making sure that you used your own
in place of the ones I guessed at.
--
Marsh
MVP [MS Access]

That makes sense, however, question: I put text box named
"EnterDate" and a label the same in the foot of the Main
Form. I placed another text box with the suggested
expression as the Control Source. I get an error message
in the second text box. What am I doing wrong?

-----Original Message-----

I have 48 records On a sub form.
Fields
Date Physical Occupational Speech
10/1/04 25 10 30
10/2/04 30 25 15
10/3/04 10 10
10/4/04 15 25
10/5/04 25 25
10/6/04 15
10/7/04 25 25 25

There will probably be a command button which will ask the
user what date he wants. After typing in date, I need code
that will total numbers for that date plus the previous 6
dates. I don't know code and if button should be on main
form or sub form or even if button should be used.

Not sure you even need any code. You can add text boxes to
the form's header or footer section using expressions like:

=Sum(IIF([DateField] Between txtdate And txtDate - 6,
Physical, 0))

where txtDate is the name of the text box where the usere
enters their date of interest.

 

[Guest]

Expression: =Sum(IIf([txtEnterDate] Between [TherapyDate]
And [TherapyDate]-6,[PT],0))
In foot of Main form I added two boxes. 1)txtEnterDate
2)txtResults. The above expression is in the Control
Source property of the txtResults text box.
The subform named tblTherapyMinutes has a Date txtbox named
"TherapyDate"; a text box named "PT" which holds numbers;
the table has 48 records with the Dates filled in in
succession. The PT field has numbers in some but not all
records. When user enters a specific date they want to sum
any figures from that date back 6 days in the PT field.
Be patient with my less than complete communication of info.
Thanks

-----Original Message-----
You'll have to be more explicit and provide the ***exact***
details of what you did. What is the name of the two text
box controls on the form? What are the names of the fields
in the table? What expression did you use? If possible,
please use Copy/Paste from your program into your post so
there is no possibility of a typo.

At this point all I can do is suggest that you double check
the names of everything making sure that you used your own
in place of the ones I guessed at.
--
Marsh
MVP [MS Access]

That makes sense, however, question: I put text box named
"EnterDate" and a label the same in the foot of the Main
Form. I placed another text box with the suggested
expression as the Control Source. I get an error message
in the second text box. What am I doing wrong?

-----Original Message-----

I have 48 records On a sub form.
Fields
Date Physical Occupational Speech
10/1/04 25 10 30
10/2/04 30 25 15
10/3/04 10 10
10/4/04 15 25
10/5/04 25 25
10/6/04 15
10/7/04 25 25 25

There will probably be a command button which will ask the
user what date he wants. After typing in date, I need code
that will total numbers for that date plus the previous 6
dates. I don't know code and if button should be on main
form or sub form or even if button should be used.


Not sure you even need any code. You can add text boxes to
the form's header or footer section using expressions like:

=Sum(IIF([DateField] Between txtdate And txtDate - 6,
Physical, 0))

where txtDate is the name of the text box where the usere
enters their date of interest.

.

 

[Marshall Barton]

I apologize for making a gross mistake. Let's start over
with this.

The first rule of using aggregate functions is that they
only work with fields in the (sub)form/report's record
source table/query. This implies that the aggregate
functions are unaware of controls, (I ignored this earlier).
This means that the txtResults text box must be in the
subform's header or footer section, not in the main form.
Leave the txtEnterDate text box in the main form.

Note: do not confuse fields in a table/query with the
form/report controls that are used to display values. You
have explained the names of the controls, but not the names
of the fields they are bound to, so for now I will use
fldTherapyDate and fldPT to distinguish the fields from your
text box controls.

With that in mind, we have to find a way to write an
expression that does not refer to any text box controls.
Since you need to compare the value in a field to the value
in a control, we will need to create a public function in a
standard module to do the comparison. Here's an air code
example:

Public Function ChkRange(dt As Date, num As Variant) As
Variant
If dt >= Forms!theform.txtEnterDate - 6 _
And dt <= Forms!theform.txtEnterDate _
Then ChkRange = num
End Function

Once that is in place, the txtResults text box (in the
subform) can use the expression:
=Sum(ChkRange(fldTherapyDate, fldPT))

Next, we have to deal with the issue of getting the
expression to be re-evaluated whenever a user changes the
value in the txtEnterDate text box. This can be done by
adding a line of code to the txtEnterDate text box's
AfterUpdate event procedure:
Me.Recalc

Good luck unraveling all that,
--
Marsh
MVP [MS Access]

Expression: =Sum(IIf([txtEnterDate] Between [TherapyDate]
And [TherapyDate]-6,[PT],0))
In foot of Main form I added two boxes. 1)txtEnterDate
2)txtResults. The above expression is in the Control
Source property of the txtResults text box.
The subform named tblTherapyMinutes has a Date txtbox named
"TherapyDate"; a text box named "PT" which holds numbers;
the table has 48 records with the Dates filled in in
succession. The PT field has numbers in some but not all
records. When user enters a specific date they want to sum
any figures from that date back 6 days in the PT field.

 

[Guest]

I must be doing something wrong. Does it make a difference
that the subform is in datasheet view. When I type a date
in the txtEnterDate (on main form) the txtResults box does
not even appear anywhere.
Is it possible to send the database to you somehow?

-----Original Message-----
I apologize for making a gross mistake. Let's start over
with this.

The first rule of using aggregate functions is that they
only work with fields in the (sub)form/report's record
source table/query. This implies that the aggregate
functions are unaware of controls, (I ignored this earlier).
This means that the txtResults text box must be in the
subform's header or footer section, not in the main form.
Leave the txtEnterDate text box in the main form.

Note: do not confuse fields in a table/query with the
form/report controls that are used to display values. You
have explained the names of the controls, but not the names
of the fields they are bound to, so for now I will use
fldTherapyDate and fldPT to distinguish the fields from your
text box controls.

With that in mind, we have to find a way to write an
expression that does not refer to any text box controls.
Since you need to compare the value in a field to the value
in a control, we will need to create a public function in a
standard module to do the comparison. Here's an air code
example:

Public Function ChkRange(dt As Date, num As Variant) As
Variant

If dt >= Forms!theform.txtEnterDate - 6 _And dt <= Forms!theform.txtEnterDate _

Then ChkRange = num
End Function

Once that is in place, the txtResults text box (in the
subform) can use the expression:

=Sum(ChkRange(fldTherapyDate, fldPT))

Next, we have to deal with the issue of getting the
expression to be re-evaluated whenever a user changes the
value in the txtEnterDate text box. This can be done by
adding a line of code to the txtEnterDate text box's
AfterUpdate event procedure:
Me.Recalc

Good luck unraveling all that,
--
Marsh
MVP [MS Access]

Expression: =Sum(IIf([txtEnterDate] Between [TherapyDate]
And [TherapyDate]-6,[PT],0))
In foot of Main form I added two boxes. 1)txtEnterDate
2)txtResults. The above expression is in the Control
Source property of the txtResults text box.
The subform named tblTherapyMinutes has a Date txtbox named
"TherapyDate"; a text box named "PT" which holds numbers;
the table has 48 records with the Dates filled in in
succession. The PT field has numbers in some but not all
records. When user enters a specific date they want to sum
any figures from that date back 6 days in the PT field.

-----Original Message-----
I have 48 records On a sub form.
Fields
Date Physical Occupational Speech
10/1/04 25 10 30
10/2/04 30 25 15
10/3/04 10 10
10/4/04 15 25
10/5/04 25 25
10/6/04 15
10/7/04 25 25 25

There will probably be a command button which will ask the
user what date he wants. After typing in date, I need code
that will total numbers for that date plus the previous 6
dates. I don't know code and if button should be on main
form or sub form or even if button should be used.

.

 

[Marshall Barton]

For all we know, the subform toal may be correct. Since
datasheet forms do not display their header/footer, we will
have to retrieve the total back to the main form. Add a
text box to the main form and set its expression to:

=subformcontrol.Form.txtResults
--
Marsh
MVP [MS Access]

I must be doing something wrong. Does it make a difference
that the subform is in datasheet view. When I type a date
in the txtEnterDate (on main form) the txtResults box does
not even appear anywhere.
Is it possible to send the database to you somehow?

-----Original Message-----
I apologize for making a gross mistake. Let's start over
with this.

The first rule of using aggregate functions is that they
only work with fields in the (sub)form/report's record
source table/query. This implies that the aggregate
functions are unaware of controls, (I ignored this earlier).
This means that the txtResults text box must be in the
subform's header or footer section, not in the main form.
Leave the txtEnterDate text box in the main form.

Note: do not confuse fields in a table/query with the
form/report controls that are used to display values. You
have explained the names of the controls, but not the names
of the fields they are bound to, so for now I will use
fldTherapyDate and fldPT to distinguish the fields from your
text box controls.

With that in mind, we have to find a way to write an
expression that does not refer to any text box controls.
Since you need to compare the value in a field to the value
in a control, we will need to create a public function in a
standard module to do the comparison. Here's an air code
example:

Public Function ChkRange(dt As Date, num As Variant) As
Variant

If dt >= Forms!theform.txtEnterDate - 6 _ And dt <= Forms!theform.txtEnterDate _

Then ChkRange = num
End Function

Once that is in place, the txtResults text box (in the
subform) can use the expression:

=Sum(ChkRange(fldTherapyDate, fldPT))

Next, we have to deal with the issue of getting the
expression to be re-evaluated whenever a user changes the
value in the txtEnterDate text box. This can be done by
adding a line of code to the txtEnterDate text box's
AfterUpdate event procedure:
Me.Recalc

Expression: =Sum(IIf([txtEnterDate] Between [TherapyDate]
And [TherapyDate]-6,[PT],0))
In foot of Main form I added two boxes. 1)txtEnterDate
2)txtResults. The above expression is in the Control
Source property of the txtResults text box.
The subform named tblTherapyMinutes has a Date txtbox named
"TherapyDate"; a text box named "PT" which holds numbers;
the table has 48 records with the Dates filled in in
succession. The PT field has numbers in some but not all
records. When user enters a specific date they want to sum
any figures from that date back 6 days in the PT field.
-----Original Message-----
I have 48 records On a sub form.
Fields
Date Physical Occupational Speech
10/1/04 25 10 30
10/2/04 30 25 15
10/3/04 10 10
10/4/04 15 25
10/5/04 25 25
10/6/04 15
10/7/04 25 25 25

There will probably be a command button which will ask the
user what date he wants. After typing in date, I need code
that will total numbers for that date plus the previous 6
dates. I don't know code and if button should be on main
form or sub form or even if button should be used.

 

[Guest]

Like I said, I must be doing something wrong. I added the
new text box to main form and placed expression. I get an
error message.

-----Original Message-----
For all we know, the subform toal may be correct. Since
datasheet forms do not display their header/footer, we will
have to retrieve the total back to the main form. Add a
text box to the main form and set its expression to:

=subformcontrol.Form.txtResults
--
Marsh
MVP [MS Access]

I must be doing something wrong. Does it make a difference
that the subform is in datasheet view. When I type a date
in the txtEnterDate (on main form) the txtResults box does
not even appear anywhere.
Is it possible to send the database to you somehow?

-----Original Message-----
I apologize for making a gross mistake. Let's start over
with this.

The first rule of using aggregate functions is that they
only work with fields in the (sub)form/report's record
source table/query. This implies that the aggregate
functions are unaware of controls, (I ignored this earlier).
This means that the txtResults text box must be in the
subform's header or footer section, not in the main form.
Leave the txtEnterDate text box in the main form.

Note: do not confuse fields in a table/query with the
form/report controls that are used to display values. You
have explained the names of the controls, but not the names
of the fields they are bound to, so for now I will use
fldTherapyDate and fldPT to distinguish the fields from your
text box controls.

With that in mind, we have to find a way to write an
expression that does not refer to any text box controls.
Since you need to compare the value in a field to the value
in a control, we will need to create a public function in a
standard module to do the comparison. Here's an air code
example:

Public Function ChkRange(dt As Date, num As Variant) As
Variant

If dt >= Forms!theform.txtEnterDate - 6 _ And dt <= Forms!theform.txtEnterDate _

Then ChkRange = num
End Function

Once that is in place, the txtResults text box (in the
subform) can use the expression:

=Sum(ChkRange(fldTherapyDate, fldPT))

Next, we have to deal with the issue of getting the
expression to be re-evaluated whenever a user changes the
value in the txtEnterDate text box. This can be done by
adding a line of code to the txtEnterDate text box's
AfterUpdate event procedure:
Me.Recalc


Expression: =Sum(IIf([txtEnterDate] Between [TherapyDate]
And [TherapyDate]-6,[PT],0))
In foot of Main form I added two boxes. 1)txtEnterDate
2)txtResults. The above expression is in the Control
Source property of the txtResults text box.
The subform named tblTherapyMinutes has a Date txtbox named
"TherapyDate"; a text box named "PT" which holds numbers;
the table has 48 records with the Dates filled in in
succession. The PT field has numbers in some but not all
records. When user enters a specific date they want to sum
any figures from that date back 6 days in the PT field.
-----Original Message-----
I have 48 records On a sub form.
Fields
Date Physical Occupational Speech
10/1/04 25 10 30
10/2/04 30 25 15
10/3/04 10 10
10/4/04 15 25
10/5/04 25 25
10/6/04 15
10/7/04 25 25 25

There will probably be a command button which will ask the
user what date he wants. After typing in date, I need code
that will total numbers for that date plus the previous 6
dates. I don't know code and if button should be on main
form or sub form or even if button should be used.

.

 

[Marshall Barton]

It would be interesting to see the exact expressions that
you used and the error message/number.

Try changing the sub form's view so we can see the total
there too.