Need help with access

[Elias824]

Im an access noob and im trying to figure out how to add a damn exception for something, or maybe avoid it if I Can. I got this new job and they do a bunch of reports through access. All of the DB's were setup by the guys before me who are now gone, and I need to try to figure out how to make an odd change to this DB without knowing anything about access.

Basically I do these reports at my new job that takes a bunch of skews we pull from an online system, each skew is is multiplied by some duration, usually 15min, its a set number for each skew. all the math gets added up so we can find out how much time each person spent working, and on what thing.

Now here is my problem, each skew is usually multiplied by a static amount, but one skew gets manually put in by each person. so I need to add some kind of an exception to multiply one skew by a variable in the DB of what they entered.

Normally the math is done by, (number of each skew) * (multiplier). so I need to add some exception in there like if skew =7*variable of time

 

[KLin]

Is the multiplier for skew 7 static? And is the multiplier hardcoded into the query statement or report?

You can use what's called an extended if statement.

http://www.techonthenet.com/ac...tions/advanced/iif.php

You tell it to evalaute an expression. If it returns true, use this value. f it's false, return a different value value.

 

[Elias824]

The multiplies are defined for each skew in a table I belive, shows up next to my linked excel spreadsheets.
I can set the multiplier to each seperatly but its defined in the table as a number, when i need to have one multiplied as a variable from one of the linked excel spreadsheet.

all this stuff gets pulled from an online DB in a big excel spreadsheet. Each person at my work can add in a skew for exatly what they did, and they can also enter the time if they want. Normally pretty much no one enters time though, so it gets added out basically on set numbers * each skew.

im just at home right now sow i cant test anything untill later tommrow but ill see if I can have any luck with that if statement.
I have a little programing experience, but ive been pretty much learning access on my feet so far. Feel like kind of a nub here, I barely know enough to even explain what is going on.

 

[Evadman]

Function doMathsforElias824(Skew as double, SpecialSkew as double, SpecialSkewMultiplier as double, NormalMultiplier as double) as double
On Error Goto OMFGError
IF Skew = SpecialSkew then 'multiply by weird number
doMathsforElias824 = Skew * SpecialSkewMultiplier
ELSE
doMathsforElias824 =Skew * NormalMultiplier
END IF
Exit Function
OMFGError:
doMathsforElias824 = -1 'error, return -1 so the calling program knows. Do not raise an error so the program can continue without intervention
end function

 

[Elias824]

wooo got it working Thanks Klin, used an iff function as below


Calculated Duration: IIf([skew]=90801,[number of skews]*[Duration],[number of skews]*skew!Multiplier)

tired to simplify the variable from what they actually are, but it seems to work.
Another question though, in the report for all this nonsence it adds up all the total time each person works on everything.
Now how do I get that to round properly? to many damn decimal places

=Sum([calculated duration]/60)

 

[KLin]

You right click on the field, click properties and set the format type(under the format tab) and tell it to use however many decimal places you want to show.