Calculatin fields 1

[dixxy]

Hello,

I have report with 7 or 8 subreports. What I am trying to do makes no sense. I want to calculate a total from a field in 4 of the 7 subreports. Problem is if I 'add' it puts the valus one after the other, the same way the '&' symbol would work, but if I substract that works fine

this works:

=([qreLaborAssembly subreport].Report!total)-([qreLaborMoutingDoors subreport].Report!total)

but this doesn't

=([qreLaborAssembly subreport].Report!total)+([qreLaborMoutingDoors subreport].Report!total)

I have tried putting a 'Sum' in front, but then I get the #error in my text box...

does anyone know why??

this is very puzzuling to me..

thanks

Sylvain

 

[dhookom]

Try:
=Val([qreLaborAssembly subreport].Report!total)+Val([qreLaborMoutingDoors subreport].Report!total)



Duane
MS Access MVP

 

[dixxy]

Duance,
You are the best, I had to give you another star...!!!!!

Now if I can ask some more, how would I format this so that it would display 'hours minutes'...1h 45mins'?

Any idea?

Thanks again

Sylvain

 

[dhookom]

If this is a time value that gets returned you should only need to format the text box as some time format. If the value will exceed 24 hours then I would just multiply the value by 24 and show 36 Hrs 30 Mins as 36.5 hours.

Duane
MS Access MVP

 

[dixxy]

Duance,

It is not a time value, it's just a bouch of numbers that get added up. I would like to have it display as 36 Hrs 30 Mins .

Is this possible?

Thanks

Sylvain

 

[dhookom]

So how would you think you could do this? What kind of number would result in 36 hrs and 30 mins or 1 hour and 45 minutes?

You gotta help here since we have no idea what your numbers represent if not time.

Duane
MS Access MVP

 

[dixxy]

Duance,

Would it help if I tell you that those numbers could easaly be currency. So for example $2.00. I am linking tables, and making queries from them and one of the multiplicator is always currency. So we would only need to trim the $ and we would already have the decimal points which could be the minutes we are lokking for...I don't know what do you think

 

[dhookom]

You can format this text box exactly like you would format any other text box on a report. If you want currency, set the format to currency. If you have a date/time set the format property to Short Date or Long Time or Short Time,...

Duane
MS Access MVP

 

[dixxy]

I think we're missing the point here.

What I would really like is to have it say 1 hour 30 minutes.

If I set the format to short time it changes it to some other numbers that don't make sense.

Right now in every querie I have, I use a price field to multiply the minutes allowed per operation. That price feild has a '$' sign and of course 2 decimal places.

What I want to know is if there is a way to use this value and show it has hours minutes, and of course if there is no hours then not display the label hours only minutes.

I guess I would need something like if the value is greater than 60 then display hours and show 1 or 2 depending on how many 60 there is, and take the difference and display that as minutes.

 

[dhookom]

So the value is minutes? If this is true, I'm not sure how I would have guessed that. If the number is minutes then use something like:
=[YourNumber]\60 & " Hrs " & [YourNumber] mod 60 & " Minutes"

Duane
MS Access MVP

 

[dixxy]

Duance,

Yes the value is minutes,this looks like what I want, but where do I put it?...in the control source??

Right now the control source is like this:

=Val([qreLaborNonStockCutting subreport].Report!Total)+Val([qreLaborDrilling subreport].Report!Total)+Val([qreLaborDado subreport].Report!total)

I really appriciate your help.

Thanks

Sylvain

 

[dhookom]

I was trying to avoid creating your entire string. Take everything in your expression except the "=" and substitute it for both of the [YourNumber].

Otherwise, name it "YourNumber", make it invisible and add a text box with my expression.


Duane
MS Access MVP

 

[dixxy]

Duance,

Your are the best, I have no choice but to givew you some stars...not one but two...

Now just to put the icing on the cake, would it be possible or not, that if there is no hours the text box would display '36 minutes' only without doing the '0 hrs 36 minutes' ?

If it can't be done or to complicated it's ok. So far this is realy great

Thanks again

Sylvain

 

[dhookom]

You could use the IIf() function to test if the value is 0. Something like:
IIf([yourvalue]=0,"",[yourvalue] & " Minutes&quot
Please try to figure out how to apply this and if you have a question, post back with your expression.

Duane
MS Access MVP