How to update one field by summing two other fields

[sueb]

I have a table with some number fields in it, and I'm trying to write a query (an extremely straight-forward one, I thought!) that will add together two of the fields and place the sum in a third (all fields are Long Integer). Here's the query I currently have:

UPDATE [ACE Volumes] SET [ACE Volumes].Approved = [Ace Volumes].APP+[Ace Volumes].NAN;

I get no error, but the third field remains blank. What's going on here? I've written much more complicated update queries in the past, so I must be overlooking something because this one is so simple, or something.

[NeoPa]

Which one are you referring to as 'the third field'?

Let me say here that I may not be around to continue this till the end of next week, but your explanation will make it easier for someone to help you in my absence.

BTW. I see nothing wrong with your SQL. I would expect from this that [Approved] would be updated to reflect the sum of [APP] and [NAN].

[Rabbit]

Are either APP or NAN null?

[sueb]

Are either APP or NAN null?

They certainly could be.

[sueb]

NeoPa, by "third field", I just mean another of the Long Integer fields.

[NeoPa]

SueB:
by "third field", I just mean another of the Long Integer fields.

How come that explanation leaves it less clear even than before (:-D)?

If you're referring to a field that hasn't been included in the SQL then, of course, you wouldn't expect to see it updated, or anything happen to it at all.

If you mean one of the three fields included in the SQL ([Approved], [APP] or [NAN]), then a name would make it clear what you are expecting.

Frankly, if you're referring to anything other than [Approved], then you shouldn't be expecting any changes whatsoever. If that is the field, and you want a numeric value in there regardless of the existence or otherwise of valid numeric values in [APP] and [NAN], then you may want to change the SQL to handle Nulls :

UPDATE [ACE Volumes]
SET    [Approved] = Nz([APP], 0) + Nz([NAN], 0)

[sueb]

I guess I assumed that the SQL I posted was clear--my apologies.

But thanks for the fix, anyway!

[NeoPa]

SueB:
I guess I assumed that the SQL I posted was clear--my apologies.

No worries Sue, but it's very often the case that a questionner expects something to be clear when an expert can see so many more possibilities than they can hence, unless the problem is explained categorically (unambiguously) , they are not sure what is being asked. We also deal with questionners of such varying abilities that we have to be careful not to assume you understand what you probably do. It's very easy to insult some people, while simultaneously confusing others, by the simple expedient of explaining what we know in a way that doesn't suit their level of understanding. As that level is generally not clear to us we have to walk a very tight line most of the time.

To help you avoid putting yourself in that position in future, my advice was to phrase your question in a clear and unambiguous manner. Clearly that will make my life easier (as I hope and expect you'll be posting more questions for us), but equally it will have the same effect for you. Anyway, I'm glad we managed to find a solution one way or another for this situation :-)

[sueb]

Clarity is the key, isn't it? So thanks for sticking with me until I made sense!

[NeoPa]

That is an understanding I will risk being considered preachy to get across Sue. That said, your error was pretty minor. I just like to take any opportunities presented to make this most important of points. In truth, I always enjoy answering your questions as they are generally pretty clear.