Sum in select query

[gazsharpe101]

Hi everyone,

It's been a long while since I posted on here, I hope you are all well.

Basically, my problem (which I think should be relatively easy to solve) is this:

I have a query which selects values from multiple tables. This part works ok.

However, here are some of the columns that I have in my query:
Act: Nz([InsolvSchemeDetails.ActiveMembers],0)
Def: Nz([InsolvSchemeDetails.Deferreds],0)
Pen: Nz([InsolvSchemeDetails.Pensioners],0)
Trans: Nz([InsolvSchemeDetails.Transferred],0)
Dec: Nz([InsolvSchemeDetails.DeceasedMembers],0)

My question is how do I sum these within my query?

I have tried this:
TotalMems: [Act]+[Def]+[Pen]+[Trans]+[Dec] however this doesn't sum them it just ends up as 00101 for example.

I have also tried to se the sum function but that also hasn't worked.

Any ideas would be much appreciated.

Thanks.
Gareth.

 

[Lyconal]

Scrap that - I was talking crap. lol

Or was I?

Try using the &, cos Im pretty sure that + concantenates number formatted fields.

 

[Kafrin]

Is there a reason that your query thinks your numbers are text? It might be worth investigating to make sure it doesn't cause you other problems.

As it stands though, try using the Val function, which interprets strings as numbers if possible, ie:
TotalMems: Val([Act])+Val([Def])+Val([Pen])+Val([Trans])+Val([Dec])

..in fact it might be better to do this step on the individual fields before summing instead, so you'd have:
Act: Val(Nz([InsolvSchemeDetails.ActiveMembers],0))
Def: Val(Nz([InsolvSchemeDetails.Deferreds],0))
Pen: Val(Nz([InsolvSchemeDetails.Pensioners],0))
Trans: Val(Nz([InsolvSchemeDetails.Transferred],0))
Dec: Val(Nz([InsolvSchemeDetails.DeceasedMembers],0))
and
TotalMems: [Act]+[Def]+[Pen]+[Trans]+[Dec]

 

[gazsharpe101]

Thanks for your replies guys I will try it tomorrow.

I did try and use the val function but I tried:

TotalMems: Val([Act]+[Def]+[Pen]+Val[Trans]+[Dec]) which didn't work.

Thanks again.
Gareth.

 

[gazsharpe101]

..in fact it might be better to do this step on the individual fields before summing instead, so you'd have:
Act: Val(Nz([InsolvSchemeDetails.ActiveMembers],0))
Def: Val(Nz([InsolvSchemeDetails.Deferreds],0))
Pen: Val(Nz([InsolvSchemeDetails.Pensioners],0))
Trans: Val(Nz([InsolvSchemeDetails.Transferred],0))
Dec: Val(Nz([InsolvSchemeDetails.DeceasedMembers],0))
and
TotalMems: [Act]+[Def]+[Pen]+[Trans]+[Dec]

I tried this and it worked a treat! Thanks for all of your help.