If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding field values together
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","qrySECCountOfPlacement s","[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 |
#2
|
|||
|
|||
Adding field values together
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 he 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JohnB" wrote in message ... 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","qrySECCountOfPlacement s","[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 |
#3
|
|||
|
|||
Adding field values together
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" wrote: 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 he 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JohnB" wrote in message ... 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","qrySECCountOfPlacement s","[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 |
#4
|
|||
|
|||
Adding field values together
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JohnB" wrote in message ... 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" wrote: 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 he 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" wrote in message ... 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","qrySECCountOfPlacement s","[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 |
#5
|
|||
|
|||
Adding field values together
Thank you Allen. I've understand now. Sorry again for the confusion
Cheers, JohnB "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JohnB" wrote in message ... 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" wrote: 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 he 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" wrote in message ... 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","qrySECCountOfPlacement s","[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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form, Subform, Tab key | 2nd_Stage_User | Using Forms | 17 | August 25th, 2006 12:30 AM |
Adding calculated values into a table field??? | Workshop | General Discussion | 4 | March 14th, 2005 06:06 PM |
adding field values to selected contacts | boe | Contacts | 1 | February 19th, 2005 05:24 PM |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
Adding auto-numbered field screws up Table order | Carl | Database Design | 5 | May 30th, 2004 03:25 AM |