query calculation problem

[jenniferhelen]

I am having a problem with a calculation in Microsoft Access. I am trying to add two numeric columns, however the result I am receiving is the data is being concatenated. I am using the following formula: newField:([field1]+[field2]). If field1 contains 567 and field2 contains -98, my result is 567-98 instead of 469. I have performed simular calcualtions many times and I am going nuts trying to figure out why this won't work. I appreciate any help you can provide.

[missinglinq]

I reproduced your scenario, and this will only happen if you have Field1 and Field2 defined as Text. If they're defined as Numbers you'll get your desired results.

Alternatively, you could use

NewField: Val([Field1]) + Val([Field2])

Welcome to Bytes!

Linq ;0)>

[jenniferhelen]

Thank you, thank you, thank you! I don't even want to tell you how much time I spent on that!

Just out of curiosity, the fields are listed as "numbers" in the original table. However I have them in initial query before appearing in the final query where I am performing the calculations. Does the format not carry over from the table? Also, in the query I tried changing the format to "general number" or "standard number" and had the same concatenating result.

I really appreciate your help.

[missinglinq]

Don't really know where they got changed to Text, although the output of most formatting does is string/text rather than the original datatype.

Linq ;0)>

[NeoPa]

@jenniferhelen
This is where the problem is introduced.

The Format() function returns a string value, not a numeric value simply displayed as formatted. It is not generally considered a good idea to convert the value to a formatted string until it is necessary (If reporting, format it in the report rather than any of the intervening steps).

Does that make sense?

Welcome to Bytes!

[jenniferhelen]

Yes that makes sense. I have made those changes. Thanks again.

[NeoPa]

Very pleased to hear it JH :)

Good luck with your project.