I'm sure this is really simple!

[steve a]

I'm struggling....
I'm trying to do some simple maths with number values in a query, but can'
quite manage it.
eg.
costtotal: [xtrcost]+[coststaff]
returns the two fields "joined" rather than added together!

I also have some iif statments, which look like this,

xtrcost: IIf([reason for
cancellation]>"","0",([xtrstaff]*([time]/60))+[extra])

but they still return an incorrect value!

any help would be appreciated.
Thanks
Steve

 

[Gina Whipp]

Steve,

You forgot to tell them to Sum

costtotal: Sum([xtrcost]+[coststaff])

xtrcost: IIf([reason for
cancellation]>"","0",Sum(([xtrstaff]*([time]/60))+[extra]))



--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[steve a]

ok so made a little progress...
I how get an error.

i have a row with the code:

extra: IIf([activity]="court apperance","30","0")

this works fine, however when i try and add the next row, which references it:

xtrcost: IIf([reason for
cancellation]>"","0",Sum(([xtrstaff]*([time]/60))+[extra]))

i get an error message:

You tried to execute a query tthat does not include the specified expression
'activity' as part of an aggregate function.

What does that mean?
any help would be appreciated.

Thanks again
Steve

--
steve adlam

Steve,

You forgot to tell them to Sum

costtotal: Sum([xtrcost]+[coststaff])

xtrcost: IIf([reason for
cancellation]>"","0",Sum(([xtrstaff]*([time]/60))+[extra]))



--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

I'm struggling....
I'm trying to do some simple maths with number values in a query, but can'
quite manage it.
eg.
costtotal: [xtrcost]+[coststaff]
returns the two fields "joined" rather than added together!

I also have some iif statments, which look like this,

xtrcost: IIf([reason for
cancellation]>"","0",([xtrstaff]*([time]/60))+[extra])

but they still return an incorrect value!

any help would be appreciated.
Thanks
Steve

 

[Gina Whipp]

Try the below... you are not using a Totals query. It can't include a
field that technically hasn't added itself together yet.

xtrcost: IIf([reason for >
cancellation]>"","0",Sum(([xtrstaff]*([time]/60)))+(IIf([activity]="court
apperance","30","0")
))


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

ok so made a little progress...
I how get an error.

i have a row with the code:

extra: IIf([activity]="court apperance","30","0")

this works fine, however when i try and add the next row, which references
it:

xtrcost: IIf([reason for
cancellation]>"","0",Sum(([xtrstaff]*([time]/60))+[extra]))

i get an error message:

You tried to execute a query tthat does not include the specified
expression
'activity' as part of an aggregate function.

What does that mean?
any help would be appreciated.

Thanks again
Steve

--
steve adlam

Steve,

You forgot to tell them to Sum

costtotal: Sum([xtrcost]+[coststaff])

xtrcost: IIf([reason for
cancellation]>"","0",Sum(([xtrstaff]*([time]/60))+[extra]))



--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index.htm

I'm struggling....
I'm trying to do some simple maths with number values in a query, but
can'
quite manage it.
eg.
costtotal: [xtrcost]+[coststaff]
returns the two fields "joined" rather than added together!

I also have some iif statments, which look like this,

xtrcost: IIf([reason for
cancellation]>"","0",([xtrstaff]*([time]/60))+[extra])

but they still return an incorrect value!

any help would be appreciated.
Thanks
Steve

 

[John W. Vinson]

ok so made a little progress...
I how get an error.

i have a row with the code:

extra: IIf([activity]="court apperance","30","0")

This is not returning the *numbers* 30 and 0 - it's returning *text strings*.
Lose the quotes around 30 and 0.

this works fine, however when i try and add the next row, which references it:

xtrcost: IIf([reason for
cancellation]>"","0",Sum(([xtrstaff]*([time]/60))+[extra]))

You usually can't use a calculated field in a further calculation. Just
recapitulate the expression:

i get an error message:

You tried to execute a query tthat does not include the specified expression
'activity' as part of an aggregate function.

Please post the complete SQL of the query.


I'd REALLY rethink this. You're embedding a lot of business logic in these
complicated IIF statements, and you'll have a maintenance nightmare down the
road if the rules change.

 

[steve a]

ok the complete sql is: (Ive put line spaces in for easy viewing, as the
select is prettymuch all one line.

still get the error stated previously..

SELECT [activity table].activity, [activity table].time, [activity
table].Dept, [activity table].Datein,

[activity table].Ward,

[activity table].cost,

[activity table].xtrstaff,

IIf([activity]="court apperance",30,0) AS extra,

[activity table].[reason for cancellation],

IIf([reason for cancellation]="refused by patient","0",IIf([reason for
cancellation]>"",[time],"0")) AS staffrefused,

IIf([reason for cancellation]="refused by patient",[time],"0") AS ptrefused,

[activity table].patient,

IIf([reason for
cancellation]>"",0,Sum(([xtrstaff]*([time]/60))+(IIf([activity]="court
appearance",30,0)))) AS xtrcost,

IIf([reason for cancellation]>"","0",Sum(([time]/60)*[cost])) AS coststaff,

IIf([reason for
cancellation]>"",0,Sum(([xtrstaff]*([time]/60))+(IIf([activity]="court
appearance",30,0))))+Sum(([time]/60)*[cost]) AS costtotal

FROM [activity table]

WHERE ((([activity table].Datein) Between [Forms]![frmptcost]![txtstart] And
[Forms]![frmptcost]![txtend]) AND (([activity
table].patient)=[Forms]![frmptcost]![cboname]));


IF there is another way around this, then i''m willing to look into it,
however i'm running out of time for this project and would just like
something that works for now, so working on this query would be the best at
this point in time...

many thanks
Steve

--
steve adlam

ok so made a little progress...
I how get an error.

i have a row with the code:

extra: IIf([activity]="court apperance","30","0")

This is not returning the *numbers* 30 and 0 - it's returning *text strings*.
Lose the quotes around 30 and 0.

this works fine, however when i try and add the next row, which references it:

xtrcost: IIf([reason for
cancellation]>"","0",Sum(([xtrstaff]*([time]/60))+[extra]))

You usually can't use a calculated field in a further calculation. Just
recapitulate the expression:

i get an error message:

You tried to execute a query tthat does not include the specified expression
'activity' as part of an aggregate function.

Please post the complete SQL of the query.


I'd REALLY rethink this. You're embedding a lot of business logic in these
complicated IIF statements, and you'll have a maintenance nightmare down the
road if the rules change.

 

[steve a]

Hi all...
finally got it sorted..
Used some mathmatical functions in the report i was generating from the
query. so all sorted now...
Many thanks for your input.

--
steve adlam

ok the complete sql is: (Ive put line spaces in for easy viewing, as the
select is prettymuch all one line.

still get the error stated previously..

SELECT [activity table].activity, [activity table].time, [activity
table].Dept, [activity table].Datein,

[activity table].Ward,

[activity table].cost,

[activity table].xtrstaff,

IIf([activity]="court apperance",30,0) AS extra,

[activity table].[reason for cancellation],

IIf([reason for cancellation]="refused by patient","0",IIf([reason for
cancellation]>"",[time],"0")) AS staffrefused,

IIf([reason for cancellation]="refused by patient",[time],"0") AS ptrefused,

[activity table].patient,

IIf([reason for
cancellation]>"",0,Sum(([xtrstaff]*([time]/60))+(IIf([activity]="court
appearance",30,0)))) AS xtrcost,

IIf([reason for cancellation]>"","0",Sum(([time]/60)*[cost])) AS coststaff,

IIf([reason for
cancellation]>"",0,Sum(([xtrstaff]*([time]/60))+(IIf([activity]="court
appearance",30,0))))+Sum(([time]/60)*[cost]) AS costtotal

FROM [activity table]

WHERE ((([activity table].Datein) Between [Forms]![frmptcost]![txtstart] And
[Forms]![frmptcost]![txtend]) AND (([activity
table].patient)=[Forms]![frmptcost]![cboname]));


IF there is another way around this, then i''m willing to look into it,
however i'm running out of time for this project and would just like
something that works for now, so working on this query would be the best at
this point in time...

many thanks
Steve

--
steve adlam

ok so made a little progress...
I how get an error.

i have a row with the code:

extra: IIf([activity]="court apperance","30","0")

This is not returning the *numbers* 30 and 0 - it's returning *text strings*.
Lose the quotes around 30 and 0.

this works fine, however when i try and add the next row, which references it:

xtrcost: IIf([reason for
cancellation]>"","0",Sum(([xtrstaff]*([time]/60))+[extra]))

You usually can't use a calculated field in a further calculation. Just
recapitulate the expression:

i get an error message:

You tried to execute a query tthat does not include the specified expression
'activity' as part of an aggregate function.

Please post the complete SQL of the query.


I'd REALLY rethink this. You're embedding a lot of business logic in these
complicated IIF statements, and you'll have a maintenance nightmare down the
road if the rules change.