Error on form control

  • Thread starter Thread starter LMB
  • Start date Start date
L

LMB

Hi Guys,

Using Access 2000. On my form I have a control which has the first
expression and it's working fine. This is multiplying the number of
modalities done by a multiplication factor that is entered into my Modality
table. This is working fine but I want a control on the bottom of the form
to sum these totals. I tried the bottom expression but I get # error in the
control.........Thanks, Linda

=[NumDone]*[ModalityID].column(2).........working fine

=Sum([NumDone]*[ModalityID].column(2))..........#Error
 
I don't believe you can use the column property for the Sum function.

Create an invisible textbox; name it txtColumn2. Set its control source to
=[ModalityID].column(2)

Then use this expression in your Sum expression:
=Sum([NumDone]*[txtColumn2]
 
I did this but I get the same error message. I don't think this worked
because the numbers I want to add are multiple sums of modalities. Each
ModalityID has a name and multiplication factor. If a therapist does 4
vent checks, the multiplication factor is 2 so the
=[NumDone]*[ModalityID].column(2) returns the number 8, the next modality is
ABG Draw and that has a multiplication factor of .25 so the number returned
is 1. I want my new text box to add those 2 up and return the number 5.
Also, my control is on the form footer. Does it matter where I place the
control?

(I actually know what and how to make an invisible textbox, now that
sumthin!)

Linda


Ken Snell said:
I don't believe you can use the column property for the Sum function.

Create an invisible textbox; name it txtColumn2. Set its control source to
=[ModalityID].column(2)

Then use this expression in your Sum expression:
=Sum([NumDone]*[txtColumn2]


--

Ken Snell
<MS ACCESS MVP>

LMB said:
Hi Guys,

Using Access 2000. On my form I have a control which has the first
expression and it's working fine. This is multiplying the number of
modalities done by a multiplication factor that is entered into my
Modality table. This is working fine but I want a control on the bottom
of the form to sum these totals. I tried the bottom expression but I get
# error in the control.........Thanks, Linda

=[NumDone]*[ModalityID].column(2).........working fine

=Sum([NumDone]*[ModalityID].column(2))..........#Error
Click to expand...
Click to expand...
 
What I posted should work just fine based on your description for what you
seek to do, so there must be another cause for the error. Is it possible
that there is no value for either the NumDone or the txtColumn2 controls in
a record?
--

Ken Snell
<MS ACCESS MVP>




LMB said:
I did this but I get the same error message. I don't think this worked
because the numbers I want to add are multiple sums of modalities. Each
ModalityID has a name and multiplication factor. If a therapist does 4
vent checks, the multiplication factor is 2 so the
=[NumDone]*[ModalityID].column(2) returns the number 8, the next modality
is ABG Draw and that has a multiplication factor of .25 so the number
returned is 1. I want my new text box to add those 2 up and return the
number 5. Also, my control is on the form footer. Does it matter where I
place the control?

(I actually know what and how to make an invisible textbox, now that
sumthin!)

Linda


Ken Snell said:
I don't believe you can use the column property for the Sum function.

Create an invisible textbox; name it txtColumn2. Set its control source
to
=[ModalityID].column(2)

Then use this expression in your Sum expression:
=Sum([NumDone]*[txtColumn2]


--

Ken Snell
<MS ACCESS MVP>

LMB said:
Hi Guys,

Using Access 2000. On my form I have a control which has the first
expression and it's working fine. This is multiplying the number of
modalities done by a multiplication factor that is entered into my
Modality table. This is working fine but I want a control on the bottom
of the form to sum these totals. I tried the bottom expression but I
get # error in the control.........Thanks, Linda

=[NumDone]*[ModalityID].column(2).........working fine

=Sum([NumDone]*[ModalityID].column(2))..........#Error
Click to expand...
Click to expand...
Click to expand...
 
Ken,

There is a value for NumDone, that is stored in a table, the other value
from my unbound textbox, txtTotalModalityPts.
=[NumDone]*[ModalityID].column(2) is a calculated field on a form I guess
would not be stored? Maybe I need to make a query so the value is stored
somewhere?

Thanks,
LindA
 
I have created a query that is returning the figure I want. Now I have to
figure out how to get a control on my form that can display this. I have to
read a little more and think about it and will start a new thread.

Thanks Ken, I really appreciate your continued help and the fact that you
still answer my questions even though many of them probably don't make
enough sense for most of you MVPs to comprehend but I am really making
progress from my perspective!

Linda

LMB said:
Ken,

There is a value for NumDone, that is stored in a table, the other value
from my unbound textbox, txtTotalModalityPts.
=[NumDone]*[ModalityID].column(2) is a calculated field on a form I guess
would not be stored? Maybe I need to make a query so the value is stored
somewhere?

Thanks,
LindA



Ken Snell said:
What I posted should work just fine based on your description for what
you seek to do, so there must be another cause for the error. Is it
possible that there is no value for either the NumDone or the txtColumn2
controls in a record?
Click to expand...
Click to expand...
 
You could use a textbox with a control source that uses the DLookup function
to get the value from the query. Good luck.

--

Ken Snell
<MS ACCESS MVP>

LMB said:
I have created a query that is returning the figure I want. Now I have to
figure out how to get a control on my form that can display this. I have
to read a little more and think about it and will start a new thread.

Thanks Ken, I really appreciate your continued help and the fact that you
still answer my questions even though many of them probably don't make
enough sense for most of you MVPs to comprehend but I am really making
progress from my perspective!

Linda

LMB said:
Ken,

There is a value for NumDone, that is stored in a table, the other value
from my unbound textbox, txtTotalModalityPts.
=[NumDone]*[ModalityID].column(2) is a calculated field on a form I guess
would not be stored? Maybe I need to make a query so the value is stored
somewhere?

Thanks,
LindA



Ken Snell said:
What I posted should work just fine based on your description for what
you seek to do, so there must be another cause for the error. Is it
possible that there is no value for either the NumDone or the txtColumn2
controls in a record?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top