Pablo;
Here's what I would do:
1. Create a query with your table as the record source.
2. Drag the A field down to the Query Design Grid and drop it on the first
column.
3. In the first ROW of the second column type Fixer: 0
4. In the Criteria field of the first column (where you dropped the A field),
type Is Null
5. View the query.
6. Click on the gray column header bar of the Fixer column and then copy that
field using your preferred copy method. (Myself, I like to do the <Ctrl> <C>
keyboard combo.)
7. Click on the gray column header bar of the A column and paste the copied
information into that column. What you've just done is to replace all of your
empty fields with zeroes.
8. Return to the query design view.
9. Replace the A field with the B field. You can do this by doing one of the
following:
a. Drag and drop the B field into the Query Design Grid;
b. Click the drop-down list arrow just to the right of the A and select B;
c. Highlight the A field name and type in the B field name.
10. Make sure your criteria is still set to Is Null (in the B field column).
11. View the query again and do the copy/paste routine again to replace all
empty fields with zeroes.
Your "sum" procedure should work correctly now. As a side note, you might
consider setting the default value of the A and B fields to 0. You can do this
in the table design window of the table that contains these fields. Setting
the default value will automatically enter a zero in these fields with each new
record entered. Then as you enter new records, you can change the zero or
leave it be.
John A. Carter
--- In ms_access@yahoogroups.com, P S <discworld2...@...> wrote:
>
> Hello all,
>
> I need some help generating a sum of 2 fields, where at least one field is in
> blank. I've attached a very small database that shows what I'm trying to do.
> I have 8 rows, and 4 columns. ID, A, B, SUM.
>
> SUM = A + B.
>
> However, when A is blank or B is blank, I'm getting a blank value for SUM. I
> would like to recode "blank As" and "blank Bs" to zero, so that I can get
> always get a value for SUM. I have 1,000 rows, so I don't want to enter this
> data again.
>
> Here is my code:
>
> Private Sub B_LostFocus()
> SUM = A + B
> End Sub
>
> Does anyone know how to recode A and B to make sure that I am getting a SUM
> for every row?
>
> Thanks,
> Pablo
>
>
>
>
> [Non-text portions of this message have been removed]
>
