Forums
This topic is locked
Any good number crunchers out there??
Posted 03 Nov 2002 11:27:20
1
has voted
03 Nov 2002 11:27:20 Russell Marshall posted:
Any good number cruncher’s out there?I have a problem when a zero input, or multiplier is used in the following query.
The query below is used to calculate the amount owed by clients (ClID is client ID) using information from invoice lines in the table inv_lines, multiplied by 1.175 to include the 17.5% vat.
SELECT SUM(PriceLine)*'1.175' AS totalowed
FROM acc_invlines
WHERE ClID = 'MMColParam' AND PaidYN = 'NO'
The query works fine when
A price line is present and the result is > zero
The query fails if:
The result is zero or
There are no lines entered for the client in acc_invlines
The error passed back to the browser is.
“Microsoft VBScript runtime error '800a000d'
Type mismatch: 'FormatNumber'
/logons/WipLogon/activity.asp, line 739”
In access, the field “PriceLine” is set to
Number
Long Integer
Decimal places 2
Default 0
In the Recordset.
Totalowed is set to number, 2 decimal places
Is the problem that if there is no record then it cannot be multiplied by 1.175
I am using an access 2000 database running on a Win2000 server. UD4 is set to asp /vbscript.
Russell
Replies
Replied 04 Nov 2002 14:16:52
04 Nov 2002 14:16:52 Vince Baker replied:
Change the recordset t:
Select SUM(PriceLine) AS TotalOwed
From ***
Where ***
Then, add the following above the <html> tag:
<% Dim strFinalTotal
If YourRecordsetName.fields.item("TotalOwed"
.value > 0 Then strFinalTotal = TotalOwed * 1.175 Else strFinalTotal = 0 End If %>
Add the following where you want to display the number:
<% Response.write(strFinalTotal) %>
There you go....
Regards
Vince
Response.write("The best line of code you can ever use"
VBScript | ASP | HTML | SQL | Oracle | Hosting
Select SUM(PriceLine) AS TotalOwed
From ***
Where ***
Then, add the following above the <html> tag:
<% Dim strFinalTotal
If YourRecordsetName.fields.item("TotalOwed"
.value > 0 Then strFinalTotal = TotalOwed * 1.175 Else strFinalTotal = 0 End If %>Add the following where you want to display the number:
<% Response.write(strFinalTotal) %>
There you go....
Regards
Vince
Response.write("The best line of code you can ever use"
VBScript | ASP | HTML | SQL | Oracle | Hosting
Replied 04 Nov 2002 14:17:46
04 Nov 2002 14:17:46 Vince Baker replied:
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Change the recordset t:
Select SUM(PriceLine) AS TotalOwed
From ***
Where ***
Then, add the following above the <html> tag:
<% Dim strFinalTotal
If YourRecordsetName.fields.item("TotalOwed".value > 0 Then strFinalTotal = YourRecordsetName.fields.item("TotalOwed".value * 1.175 Else strFinalTotal = 0 End If %>
Add the following where you want to display the number:
<% Response.write(strFinalTotal) %>
There you go....
Regards
Vince
Response.write("The best line of code you can ever use"
VBScript | ASP | HTML | SQL | Oracle | Hosting
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Regards
Vince
Response.write("The best line of code you can ever use"
VBScript | ASP | HTML | SQL | Oracle | Hosting
Change the recordset t:
Select SUM(PriceLine) AS TotalOwed
From ***
Where ***
Then, add the following above the <html> tag:
<% Dim strFinalTotal
If YourRecordsetName.fields.item("TotalOwed"
.value > 0 Then strFinalTotal = YourRecordsetName.fields.item("TotalOwed".value * 1.175 Else strFinalTotal = 0 End If %>Add the following where you want to display the number:
<% Response.write(strFinalTotal) %>
There you go....
Regards
Vince
Response.write("The best line of code you can ever use"
VBScript | ASP | HTML | SQL | Oracle | Hosting
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Regards
Vince
Response.write("The best line of code you can ever use"
VBScript | ASP | HTML | SQL | Oracle | Hosting
Replied 04 Nov 2002 15:18:04
04 Nov 2002 15:18:04 Russell Marshall replied:
Hi Vince,
Many thanks, I am very grateful.I have tried your suggestion and it works very well.
Is there a way I can format the results
<% Response.write(strFinalTotal) %>
so that it displays the results as a number with two decimal places.
eg 199.00 , 0.00
Best Regards
Davrus
Russell
Many thanks, I am very grateful.I have tried your suggestion and it works very well.
Is there a way I can format the results
<% Response.write(strFinalTotal) %>
so that it displays the results as a number with two decimal places.
eg 199.00 , 0.00
Best Regards
Davrus
Russell
Replied 04 Nov 2002 15:20:48
04 Nov 2002 15:20:48 Russell Marshall replied:
Hi Vince,
Many thanks, I am very grateful.I have tried your suggestion and it works very well.
Is there a way I can format the results
<% Response.write(strFinalTotal) %>
so that it displays the results as a number with two decimal places.
eg 199.00 , 0.00
Best Regards
Davrus
Russell
Many thanks, I am very grateful.I have tried your suggestion and it works very well.
Is there a way I can format the results
<% Response.write(strFinalTotal) %>
so that it displays the results as a number with two decimal places.
eg 199.00 , 0.00
Best Regards
Davrus
Russell
Replied 04 Nov 2002 15:28:14
04 Nov 2002 15:28:14 Vince Baker replied:
<% Response.Write(FormatNumber(strFinalTotal, 2))%> should do the trick
Regards
Vince
Response.write("The best line of code you can ever use"
VBScript | ASP | HTML | SQL | Oracle | Hosting
Regards
Vince
Response.write("The best line of code you can ever use"
VBScript | ASP | HTML | SQL | Oracle | Hosting
Replied 04 Nov 2002 16:38:54
04 Nov 2002 16:38:54 Russell Marshall replied:
Thanks Again Vince.
Mission accomplished.
Best Regards
Davrus
Russell
Mission accomplished.
Best Regards
Davrus
Russell
Replied 04 Nov 2002 16:39:03
04 Nov 2002 16:39:03 Russell Marshall replied:
Thanks Again Vince.
Mission accomplished.
Best Regards
Davrus
Russell
Mission accomplished.
Best Regards
Davrus
Russell
Replied 13 Dec 2003 11:30:01
13 Dec 2003 11:30:01 Russell Marshall replied:
Hi Vince,
I hope you are well.
It's a while ago now but you helped me with www.udzone.com/forum/link.asp?TOPIC_ID=20460 and your advise was spot on thanks.
I hope you don't mind me asking but you seemed the obvious person to ask.
I am in the process of upsizing the site to MS SQL and the code you supplied does not work in this environment.
The code I am using is:
<% Dim strMore90
If RS_More90.fields.item("More90".value > 0 Then strMore90 = RS_More90.fields.item("More90".value * 1.175 Else strMore90 = 0 End If %>
Do you have any idea why this does not work on the SQL.
I have checked the recordsets and they appear to be fine.
Thanks
Russell
I have started getting type mismatch errors.
Russell
I hope you are well.
It's a while ago now but you helped me with www.udzone.com/forum/link.asp?TOPIC_ID=20460 and your advise was spot on thanks.
I hope you don't mind me asking but you seemed the obvious person to ask.
I am in the process of upsizing the site to MS SQL and the code you supplied does not work in this environment.
The code I am using is:
<% Dim strMore90
If RS_More90.fields.item("More90"
.value > 0 Then strMore90 = RS_More90.fields.item("More90".value * 1.175 Else strMore90 = 0 End If %>Do you have any idea why this does not work on the SQL.
I have checked the recordsets and they appear to be fine.
Thanks
Russell
I have started getting type mismatch errors.
Russell