Adding field values together

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I'm having a silly problem when I try to add the values in a series of
text fields together. Each of the text fields (set to format General Number)
is fed from a DLookup like this:

=DLookUp("TotalPlacements","qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) & " And [Subject]='Art & Design' And [PlacementStage] =
'First Placement'")

This produces a value like 1 or 2 or3 and is left blank if no value is found
by the DLookup. I then have another Totals text field that just adds the
values of these text fields together, using the calculation =[Text1]+[Text2]
etc. This works OK until I include a text field, in the calculation, that is
blank. This then produces a blank entry in the Totals field. Can anyone see a
way around this? I'd prefer not to have 0 appearing in my text fields where
the DLookup finds no value but if I have to, fair enough.

Thanks for any help. JohnB
 
John, I take it that you did mean these are Text type fields in table design
view (not merely text boxes.)

If so, you have several issues here:
1. Using Text fields where there should be numbers.
2. Handling nulls.
3. A non-normalized design.

To solve #1, change your Text fields into Number or Currency fields.
To solve #2, use Nz().
To solve #3, use a related table with lots of records instead of a table
with lots of fields. It then becomes very simple to sum the records.

If you don't want to do that, you will need to use Nz() to handle the nulls,
and then Val() to convert the text to values, and you can then add the
fields. It will look like this:
= Val(Nz([Text1],"")) + Val(Nz([Text2], "")
 
Thanks Allen.

I used your = Val(Nz([Text1],"")) + Val(Nz([Text2], "") code and it works
fine.

Sorry to confuse but I should have said text boxes rather than text fields.
They are just text boxes with DLookups as the control source. 'Totals' is
another text box with the above expression as its control source.

One extra question - if Text1 and Text2 have no values, the Totals text box
shows 0. Is there a way to make it just show blank?

Thanks again for the quick response. Regards, JohnB

Allen Browne said:
John, I take it that you did mean these are Text type fields in table design
view (not merely text boxes.)

If so, you have several issues here:
1. Using Text fields where there should be numbers.
2. Handling nulls.
3. A non-normalized design.

To solve #1, change your Text fields into Number or Currency fields.
To solve #2, use Nz().
To solve #3, use a related table with lots of records instead of a table
with lots of fields. It then becomes very simple to sum the records.

If you don't want to do that, you will need to use Nz() to handle the nulls,
and then Val() to convert the text to values, and you can then add the
fields. It will look like this:
= Val(Nz([Text1],"")) + Val(Nz([Text2], "")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JohnB said:
Hi. I'm having a silly problem when I try to add the values in a series of
text fields together. Each of the text fields (set to format General
Number)
is fed from a DLookup like this:

=DLookUp("TotalPlacements","qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) & " And [Subject]='Art & Design' And
[PlacementStage] =
'First Placement'")

This produces a value like 1 or 2 or3 and is left blank if no value is
found
by the DLookup. I then have another Totals text field that just adds the
values of these text fields together, using the calculation
=[Text1]+[Text2]
etc. This works OK until I include a text field, in the calculation, that
is
blank. This then produces a blank entry in the Totals field. Can anyone
see a
way around this? I'd prefer not to have 0 appearing in my text fields
where
the DLookup finds no value but if I have to, fair enough.

Thanks for any help. JohnB
Click to expand...
Click to expand...
 
Okay, so you have 3 text boxes: Text1 and Text2 are bound to a DLookup()
expression, and Text3 should show the sum of the two.

Set the Format property of all 3 text boxes to General Number, so Access
knows how you want them interpreted. If you want Text3 to be blank when
either Text1 or Text2 is blank, you can go back to your original:
=[Text1] + [Text2]

If you want Text3 to show a zero, even if the others are blank, use:
=Nz([Text1],0) + Nz([Text2],0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JohnB said:
Thanks Allen.

I used your = Val(Nz([Text1],"")) + Val(Nz([Text2], "") code and it works
fine.

Sorry to confuse but I should have said text boxes rather than text
fields.
They are just text boxes with DLookups as the control source. 'Totals' is
another text box with the above expression as its control source.

One extra question - if Text1 and Text2 have no values, the Totals text
box
shows 0. Is there a way to make it just show blank?

Thanks again for the quick response. Regards, JohnB

Allen Browne said:
John, I take it that you did mean these are Text type fields in table
design
view (not merely text boxes.)

If so, you have several issues here:
1. Using Text fields where there should be numbers.
2. Handling nulls.
3. A non-normalized design.

To solve #1, change your Text fields into Number or Currency fields.
To solve #2, use Nz().
To solve #3, use a related table with lots of records instead of a table
with lots of fields. It then becomes very simple to sum the records.

If you don't want to do that, you will need to use Nz() to handle the
nulls,
and then Val() to convert the text to values, and you can then add the
fields. It will look like this:
= Val(Nz([Text1],"")) + Val(Nz([Text2], "")

JohnB said:
Hi. I'm having a silly problem when I try to add the values in a series
of
text fields together. Each of the text fields (set to format General
Number)
is fed from a DLookup like this:

=DLookUp("TotalPlacements","qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) & " And [Subject]='Art & Design' And
[PlacementStage] =
'First Placement'")

This produces a value like 1 or 2 or3 and is left blank if no value is
found
by the DLookup. I then have another Totals text field that just adds
the
values of these text fields together, using the calculation
=[Text1]+[Text2]
etc. This works OK until I include a text field, in the calculation,
that
is
blank. This then produces a blank entry in the Totals field. Can anyone
see a
way around this? I'd prefer not to have 0 appearing in my text fields
where
the DLookup finds no value but if I have to, fair enough.

Thanks for any help. JohnB
Click to expand...
Click to expand...
Click to expand...
 
Thank you Allen. I've understand now. Sorry again for the confusion

Cheers, JohnB

Allen Browne said:
Okay, so you have 3 text boxes: Text1 and Text2 are bound to a DLookup()
expression, and Text3 should show the sum of the two.

Set the Format property of all 3 text boxes to General Number, so Access
knows how you want them interpreted. If you want Text3 to be blank when
either Text1 or Text2 is blank, you can go back to your original:
=[Text1] + [Text2]

If you want Text3 to show a zero, even if the others are blank, use:
=Nz([Text1],0) + Nz([Text2],0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JohnB said:
Thanks Allen.

I used your = Val(Nz([Text1],"")) + Val(Nz([Text2], "") code and it works
fine.

Sorry to confuse but I should have said text boxes rather than text
fields.
They are just text boxes with DLookups as the control source. 'Totals' is
another text box with the above expression as its control source.

One extra question - if Text1 and Text2 have no values, the Totals text
box
shows 0. Is there a way to make it just show blank?

Thanks again for the quick response. Regards, JohnB

Allen Browne said:
John, I take it that you did mean these are Text type fields in table
design
view (not merely text boxes.)

If so, you have several issues here:
1. Using Text fields where there should be numbers.
2. Handling nulls.
3. A non-normalized design.

To solve #1, change your Text fields into Number or Currency fields.
To solve #2, use Nz().
To solve #3, use a related table with lots of records instead of a table
with lots of fields. It then becomes very simple to sum the records.

If you don't want to do that, you will need to use Nz() to handle the
nulls,
and then Val() to convert the text to values, and you can then add the
fields. It will look like this:
= Val(Nz([Text1],"")) + Val(Nz([Text2], "")

Hi. I'm having a silly problem when I try to add the values in a series
of
text fields together. Each of the text fields (set to format General
Number)
is fed from a DLookup like this:

=DLookUp("TotalPlacements","qrySECCountOfPlacements","[SchoolID] = " &
Nz([SchoolID],-99999) & " And [Subject]='Art & Design' And
[PlacementStage] =
'First Placement'")

This produces a value like 1 or 2 or3 and is left blank if no value is
found
by the DLookup. I then have another Totals text field that just adds
the
values of these text fields together, using the calculation
=[Text1]+[Text2]
etc. This works OK until I include a text field, in the calculation,
that
is
blank. This then produces a blank entry in the Totals field. Can anyone
see a
way around this? I'd prefer not to have 0 appearing in my text fields
where
the DLookup finds no value but if I have to, fair enough.

Thanks for any help. JohnB
Click to expand...
Click to expand...
Click to expand...
 
Back
Top