round function problem

[dharkangel]

Hi,

I have an SQL statement that is giving me an 'overflow' error. I think its because one of the fields I am selecting, I divide it to get a decimal that may be too big. I tried incorporating the Round() function in my sql statement but I get a 'Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect'. How can i solve this problem?

SELECT QPTRunningPercentageJan.WeekDate, QPTRunningPercentageJan.QPTClosedByCommitSum, QPTRunningPercentageJan.QPTWeeklyClosed, QPTRunningPercentageJan.QPTClosedByCommitSum/QPTRunningPercentageJan.QPTWeeklyClosed AS Round(QPTPercentage,2)
FROM QPTRunningPercentageJan;

 

[PHV]

Try either this:
SELECT WeekDate, QPTClosedByCommitSum, QPTWeeklyClosed, IIf(Nz(QPTWeeklyClosed,0)=0, 0, QPTClosedByCommitSum/QPTWeeklyClosed) AS QPTPercentage
FROM QPTRunningPercentageJan

Or this:
SELECT WeekDate, QPTClosedByCommitSum, QPTWeeklyClosed, Round(QPTClosedByCommitSum/QPTWeeklyClosed,2) AS QPTPercentage
FROM QPTRunningPercentageJan
WHERE Nz(QPTWeeklyClosed,0)<>0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dharkangel]

thank you very much it worked. I used your first solutions. Can you please tell me exactly what this means: Nz(QPTWeeklyClosed,0)

I've never seen this before.

Thank you

 

[PHV]

In the debug window (Ctrl+G) type nz and press the <F1> key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886