Re: can't find where to change data type

mlwerth wrote:
Thank you everyone!

Hi Larry:

I finally found that behind the form that I was designing was a screen
that allowed me to switch to table design, and then I changed the
field types from text to number no problem.

Now, about the query! I've got 9 number fields that I want to total,
and have it show up on my form. So, I'm in the form design view, and
my form is looking beautiful. I have a field on the form that I want
to be the total of the 9 fields, but I was told that one field cannot
be based on a calculation from other fields.

Someone must be pulling your leg then.

How do I get a box to appear on the screen where I can put a formula
into it to calculate the sum of these other 9 fields? What menu
commands do I use, or can I use the mouse to drag a "query box" of
some kind onto the screen where I can program it? I really know
nothing about how to make a query appear on my form desgin. Thank you
again and talk to you soon.

In your query you can create a calculated field. Let's say you want to add fld1...fld3. You could enter in the querybuilder
TotalOfFields : [fld1] + [fld2] + [fld3]

There's the chance you want to create the calculated field on the form instead of the query. In the control source of the field (TotalFlds) you could enter something like
=[fld1] + [fld2] + [fld3]

There's a caveat. What happens if you have Null values in the values to be summed. Let's pretend fld2 is blank.
fld1 = 1
fld2 = Null
fld3 = 2
? fld1 + fld2 + fld3
Null
That's not right. Right? You want to see a 3 as the result.

You need to do 1 of 2 things. NZ() the fields or change the table slightly.

Using NZ, you can force a value of zero if null. Ex:
=NZ([fld1],0) + NZ([fld2],0) + NZ([fld3],0)

If you do have null values (open the table and scan thru it looking for blanks in the numeric fields). If some fields are blank, you can fix the table it so all records will have zeros in the fields.

Open the table and ensure the DefaultValue is 0 for each fld to be summed (If the DefaultValue says Null or is blank, enter a zero).

Next, update all fields used in your summing and set the value to 0 if null. Ex:
UPDATE Table1 SET Table1.Fld1 = 0
WHERE (((Table1.Fld1) Is Null));

Run this query for each field to be summed...in your case 9 times. Once all null values have been updated to zero all new records will default to zero for those fields. You won't need to NZ() them.


Sincerely,

Michael

.

Loading