Addition across multiple fields

[GLS]

Hi

I work in a hospital and I am trying to calculate how long patients have
waited for appts, taking into account periods of unavailability, by
subtracting these from the total time waited. To do this I have 3 fields
which calculate from the unavailable start and end dates, how long they were
unavailable on each occasion. I then want to add these fields together to
get the total time unavailable.

I thought this would simply be a case of
[Unavailable1]+[Unavailable2]+[Unavailable3] in an expression, however, it is
concatenating the figures rather than adding them - ie 30 +29 is giving me
3029 rather than 59!!

I'm doing this in a 'Make Table' query.

Can anyone help please?

Many thanks

GLS

 

[ceesdatabase]

Hello,

Add the conversionfunction Cint to the first field

Try this CInt([Unavailable1])+[Unavailable2]+[Unavailable3]

grtz

 

[John Spencer]

Try the following expression.

Val(Nz(Unavailable1,0)) + Val(Nz(Unavailable2,0)) + Val(Nz(Unavailable3,0))

Val forces the field to a number type
NZ forces a value if the field is Null

Your problem was that the fields were being treated as strings. Since you
didn't post the calculations I can't say why the calculations were being
treated as strings.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[ceesdatabase]

Hello John,

I encounterd this problem after using the IIf function in query fields for
creating
APACHE and SOFA scores datasets. Something is happening then.

grtz

Try the following expression.

Val(Nz(Unavailable1,0)) + Val(Nz(Unavailable2,0)) + Val(Nz(Unavailable3,0))

Val forces the field to a number type
NZ forces a value if the field is Null

Your problem was that the fields were being treated as strings. Since you
didn't post the calculations I can't say why the calculations were being
treated as strings.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi

I work in a hospital and I am trying to calculate how long patients have
waited for appts, taking into account periods of unavailability, by
subtracting these from the total time waited. To do this I have 3 fields
which calculate from the unavailable start and end dates, how long they were
unavailable on each occasion. I then want to add these fields together to
get the total time unavailable.

I thought this would simply be a case of
[Unavailable1]+[Unavailable2]+[Unavailable3] in an expression, however, it is
concatenating the figures rather than adding them - ie 30 +29 is giving me
3029 rather than 59!!

I'm doing this in a 'Make Table' query.

Can anyone help please?

Many thanks

GLS

 

[John Spencer]

OKay???
So did this work or fail? Did you get an error message? What was it?

I am probably not going to be able to help you as I have no idea what APACHE
and SOFA are in this context.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello John,

I encounterd this problem after using the IIf function in query fields for
creating
APACHE and SOFA scores datasets. Something is happening then.

grtz

Try the following expression.

Val(Nz(Unavailable1,0)) + Val(Nz(Unavailable2,0)) + Val(Nz(Unavailable3,0))

Val forces the field to a number type
NZ forces a value if the field is Null

Your problem was that the fields were being treated as strings. Since you
didn't post the calculations I can't say why the calculations were being
treated as strings.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi

I work in a hospital and I am trying to calculate how long patients have
waited for appts, taking into account periods of unavailability, by
subtracting these from the total time waited. To do this I have 3 fields
which calculate from the unavailable start and end dates, how long they were
unavailable on each occasion. I then want to add these fields together to
get the total time unavailable.

I thought this would simply be a case of
[Unavailable1]+[Unavailable2]+[Unavailable3] in an expression, however, it is
concatenating the figures rather than adding them - ie 30 +29 is giving me
3029 rather than 59!!

I'm doing this in a 'Make Table' query.

Can anyone help please?

Many thanks

GLS

 

[ceesdatabase]

Hello John,

I thought you where working in a organisation dealing with healthcare.

APACHE and SOFA are scores for surgical patients with Multi Organ Failure.

I did not try your solution because the Cint function is also working, but
from
another posting from you a use the Nz function. I thought this was only
possible on SQL server.

grtz

OKay???
So did this work or fail? Did you get an error message? What was it?

I am probably not going to be able to help you as I have no idea what APACHE
and SOFA are in this context.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello John,

I encounterd this problem after using the IIf function in query fields for
creating
APACHE and SOFA scores datasets. Something is happening then.

grtz

Try the following expression.

Val(Nz(Unavailable1,0)) + Val(Nz(Unavailable2,0)) + Val(Nz(Unavailable3,0))

Val forces the field to a number type
NZ forces a value if the field is Null

Your problem was that the fields were being treated as strings. Since you
didn't post the calculations I can't say why the calculations were being
treated as strings.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GLS wrote:
Hi

I work in a hospital and I am trying to calculate how long patients have
waited for appts, taking into account periods of unavailability, by
subtracting these from the total time waited. To do this I have 3 fields
which calculate from the unavailable start and end dates, how long they were
unavailable on each occasion. I then want to add these fields together to
get the total time unavailable.

I thought this would simply be a case of
[Unavailable1]+[Unavailable2]+[Unavailable3] in an expression, however, it is
concatenating the figures rather than adding them - ie 30 +29 is giving me
3029 rather than 59!!

I'm doing this in a 'Make Table' query.

Can anyone help please?

Many thanks

GLS

 

[Bob Barrows [MVP]]

Hello John,

I thought you where working in a organisation dealing with healthcare.

APACHE and SOFA are scores for surgical patients with Multi Organ
Failure.

I did not try your solution because the Cint function is also
working, but
from
another posting from you a use the Nz function. I thought this was
only
possible on SQL server.

No, you have that backwards ... Nz is an Access-only function.
The corresponding function in T-SQL is ISNULL, although most people use
COAlesce because of its ability to use more than two arguments.

 

[ceesdatabase]

Thanks Bob !

The ISNULL I know, but the COAlesce function is new to me.

grtz

 

[GLS]

Hi Guys

Thanks for all the advice - I used the CInt function and it worked fine.

Regards

GLS