Real Simple: add together 2 numbers in query?

[KevinSlater]

Hello, how do i add 2 field numbers in a query together, i have the fields: "basic hours" & "overtime", and a "total". I would like the "total" field to add basic hours & overtime together.

I believe i need to use the =sum function in the query on the criteria bit, is that right?....but forgot what to do please help?

 

[KenHigg]

You should be able to do something like:

myTotal: [basic hours] + [overtime]

???

 

[KevinSlater]

yeah thats what i thought cant get it working
ive tried the following

sum= [BASIC HOURS WORKED] + [OVERTIME]
Total: "BASIC HOURS WORKED + OVERTIME"

 

[KenHigg]

The sum thing is wrong. Did you try the sample I provided?

myTotal: [basic hours] + [overtime]

 

[KevinSlater]

Thanks, ive got it working now (i tried your example before & it didnt seem to work) except ive got this below which calculate fine,

TOTAL HRS: [BASIC HOURS WORKED]+[O/TIME HOURS]-[HRS ABSENT]

but if i put in one more subtraction calculation as shown below it doesnt work, any suggestions?

TOTAL HRS: [BASIC HOURS WORKED]+[O/TIME HOURS]-[HRS ABSENT]-[HOLIDAY HRS]

 

[mresann]

Do not use "Sum" as a field name. It is a dedicated name or function in Access.

For the same reason, don't use "Date", "Now", or other names that are intrinsic to VBA and Access.

Ken's example is correct. Just change the name of the field.

 

[KenHigg]

If this is what you currently have:

TOTAL HRS: [BASIC HOURS WORKED]+[O/TIME HOURS]-[HRS ABSENT]-[HOLIDAY HRS]

It should work provided the fld names are correct.

FYI: Spaces and things like "/" are bad juju in fld names

 

[KevinSlater]

ok cheers all, i will try taking the / out of my table name as it still isnt working (ive checked the field names and they are correct)

 

[ejstefl]

What's not working? What error message or results are you seeing? Is the field that caused the problem (HOLIDAY HRS) a number field?

 

[Pat Hartman]

[TOTAL HRS]: Nz([BASIC HOURS WORKED],0)+Nz([O/TIME HOURS],0)-Nz([HRS ABSENT],0)-Nz([HOLIDAY HRS],0)

You need to use the Nz() funcion since one or more of the fields may contain nulls. Any expression that contains a null element will return null that's why the expression stopped working when you added the holiday hours field.

 

[KevinSlater]

Thanks Pat & everyone else who replied, oh right so thats why...yes seems like your right Pat as it does contain nulls on the holiday field, that did the trick nicely, cheers