Add two fields of a query together?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andibevan - 29 Mar 2006 18:08 GMT

I am trying to add two fields from another query together in a query. I
want to add the fields together - [qry_AB_Metrics_4].[1
Critical]+[qry_AB_Metrics_4].[2 High] but there is a problem. If the
Critical and High values are 8 and 6 respectively - the total is 86 not 14.

How do you add two fields of a query together?

SELECT qry_AB_Metrics_4.Range, qry_AB_Metrics_4.Total,
([qry_AB_Metrics_4].[1 Critical]+[qry_AB_Metrics_4].[2 High]) AS High,
qry_AB_Metrics_4.[3 Medium] AS Medium, qry_AB_Metrics_4.[4 Low] AS Low

FROM qry_AB_Metrics_4;

TIA

Andi

Reply to this Message

Duane Hookom - 29 Mar 2006 18:14 GMT

Apparently your query qry_AB_Metrics_4 is returning the values a text. You
can wrap each field in Val() to conver to numeric.
Val([1 Critical])+Val([2 High]) As High,

Signature

Duane Hookom
MS Access MVP
--

>I am trying to add two fields from another query together in a query. I
> want to add the fields together - [qry_AB_Metrics_4].[1
[quoted text clipped - 13 lines]
>
> Andi

Reply to this Message

Tom Lake - 29 Mar 2006 18:16 GMT

>I am trying to add two fields from another query together in a query. I
> want to add the fields together - [qry_AB_Metrics_4].[1
> Critical]+[qry_AB_Metrics_4].[2 High] but there is a problem. If the
> Critical and High values are 8 and 6 respectively - the total is 86 not 14.
>
> How do you add two fields of a query together?

They are obviously text fields rather than numeric. try this:

Val([1 Critical]) + Val([2 High])

Tom Lake

Reply to this Message

Rick B - 29 Mar 2006 18:16 GMT

Sounds like it is concatenating strings, not adding values.

The fields you are using must be text fields, not numeric.

I think you can use the "Val" function to convert them, or just fix the data
types for the fields if they should truly be numeric.

Signature

Rick B

>I am trying to add two fields from another query together in a query. I
> want to add the fields together - [qry_AB_Metrics_4].[1
[quoted text clipped - 13 lines]
>
> Andi

Reply to this Message

Andibevan - 29 Mar 2006 18:29 GMT

'Thanks all - that did the trick.

> Sounds like it is concatenating strings, not adding values.
>
[quoted text clipped - 20 lines]
> >
> > Andi

Reply to this Message