Type Mismatch: formatnumber

Hi,

I have a little asp application that calculates users monthly bill.  I give the option to show the bill per month either by individual customer, or show all customers bills.

When I show the bill individually the format number works fine, but when I use it when showing all customers bills, I get a type mismatch:'formatnumber' error.

I don't understand as it is the same code that I have copy/pasted from the individual one.

Any ideas?
LVL 1
johnnyc313Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DiggaCommented:
Can you post your code Johnny?
0
johnnyc313Author Commented:
This is the code that works:

<% IF rsInvoice.eof then %>
      <td height="18" valign="top" class="normaltext">No Invoice Generated For This Month</td>
      <tr>
            <td height="18" valign="top" class="smallbold"><a href="customers.asp?CustomerId=<%=rsCustomerDetails("CustomerId")%>">Back</a></td>
      </tr>
      <% Else %>



<%'response.ContentType="application/msword"%>
<table width="667" border="0" cellpadding="0" cellspacing="0">
  <!--DWLayoutTable-->

  <tr>
    <td height="188" colspan="3" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
      <!--DWLayoutTable-->
      <tr>
        <td width="212" height="36">&nbsp;</td>
        <td width="123">&nbsp;</td>
        <td width="332" rowspan="4" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
        </tr>
      <tr>
        <td height="19" valign="top" class="smallbold">Invoice To: </td>
        <td>&nbsp;</td>
        </tr>
      <tr>
        <td height="19" valign="top" class="normaltext"><%=rsCustomerDetails("CustomerName")%></td>
        <td>&nbsp;</td>
        </tr>
      <tr>
        <td height="19" valign="top" class="normaltext"><%=rsCustomerDetails("Location")%></td>
        <td>&nbsp;</td>
        </tr>
      <tr>
        <td height="19" valign="top" class="normaltext">Glasgow</td>
        <td>&nbsp;</td>
        <td></td>
        </tr>
      <tr>
        <td height="19" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
        <td>&nbsp;</td>
        <td></td>
        </tr>
      <tr>
        <td height="19" valign="top" class="normaltext">Tel: <%=rsCustomerDetails("ContactNumber")%></td>
        <td>&nbsp;</td>
        <td></td>
        </tr>
      <tr>
        <td height="20">&nbsp;</td>
        <td>&nbsp;</td>
        <td></td>
        </tr>
      <tr>
        <td height="18" valign="top" class="smallbold">Invoice Date: <%= rsInvoice("InvoiceDate") %></td>
      <td colspan="2" valign="top" class="normaltext"></td>
      <% if not rsInvoice.eof then %>
        <% end if %>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td height="209" colspan="3" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
        <!--DWLayoutTable-->
        <tr>
          <td height="22" colspan="3" valign="top" class="smallbold">Service Description </td>
      <td colspan="2" valign="top" class="smallbold"><div align="left">Amount</div></td>
      <td width="5"></td>
        </tr>
            <% do while not rsInvoice.eof %>
        <tr>
          <td height="19" colspan="3" valign="top" class="normaltext"><%= rsInvoice("Description") %></td>
      <td colspan="2" valign="top" class="normaltext">&pound;<%= formatnumber(rsInvoice("Amount")) %></td>
      <td></td>
        </tr>
      <% rsInvoice.MoveNext
      Loop %>
        <tr>
          <td width="337" height="19"></td>
            <td width="125"></td>
            <td width="18"></td>
            <td width="55">&nbsp;</td>
          <td width="127">&nbsp;</td>
          <td></td>
        </tr>
              <% totalvat=(totalCost("totalCost")*0.175) %>
                <% invoiceamount = (totalCost("totalCost")+totalvat) %>
        <tr>
          <td height="18" colspan="2" valign="top" class="smallbold"><div align="right">Cost: </div></td>
        <td></td>
            <td colspan="2" valign="top" class="normaltext">£<%=formatnumber(totalCost("totalCost"))%></td>
      <td></td>
        </tr>
        <tr>
          <td height="1"></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
        </tr>
        <tr>
          <td height="19" colspan="2" valign="top" class="smallbold"><div align="right">VAT @ 17.5 %: </div></td>
          <td>&nbsp;</td>
          <td colspan="2" valign="top" class="normaltext">&pound;<%= formatnumber(totalvat, 2) %></td>
          <td></td>
        </tr>
        <tr>
          <td height="14"></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
        </tr>
        <tr>
          <td height="33" colspan="2" valign="top" class="smallbold"><div align="right">Invoice Amount: </div></td>
          <td>&nbsp;</td>
          <td colspan="2" valign="top" class="normaltext">&pound;<%= formatnumber(invoiceamount, 2) %> </td>
          <td></td>
        </tr>
        <tr>
          <td height="34">&nbsp;</td>
          <td>&nbsp;</td>
          <td></td>
          <td></td>
          <td></td>
          <td></td>
        </tr>
        <tr>
          <td height="30">&nbsp;</td>
          <td colspan="3" valign="top" class="smallbold"><div align="right"><a href="#" onClick="window.print()">Print Invoice </a></div></td>
        <td>&nbsp;</td>
          <td></td>
        </tr>
                </table></td>
  </tr>
  <tr>
    <td width="338" height="37">&nbsp;</td>
    <td width="197" valign="top" class="smallbold"><div align="right"><a href="customers.asp?CustomerId=<%=rsCustomerDetails("CustomerId") %>">Back to Summary </a></div></td>
  <td width="132">&nbsp;</td>
  </tr>


</table>
</body>
</html>
<% End If %>


This is the code that doesn't:

<% do while not rsCustomerDetails.eof
      Dim rsInvoice            
      Dim stringSQL
      Set rsInvoice = Server.CreateObject("ADODB.Recordset")      
      'strSQL = "SELECT Customer.*, CustomerServices.* FROM Customer, CustomerServices WHERE Customer.CustomerId = CustomerServices.CustomerId AND Customer.CustomerId=" & request.QueryString("CustomerId")
      stringSQL = "SELECT * FROM Invoice WHERE CustomerId="& rsCustomerDetails("CustomerId") & " and Format(InvoiceDate,'m')="& invoicemonth
      rsInvoice.open stringSQL, conCurrent
      
      Dim totalCost
      set totalCost = Server.CreateObject("ADODB.Recordset")
      strSQL = "Select Sum(Amount) as totalCost from Invoice Where CustomerId=" & rsCustomerDetails("CustomerId") & " and Format(InvoiceDate,'m')="& invoicemonth
      totalCost.open strSQL, conCurrent
%>
<%'response.ContentType="application/msword"%>
<table width="667" border="0" cellpadding="0" cellspacing="0">
  <!--DWLayoutTable-->

  <tr>
    <td width="667" height="93" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
      <!--DWLayoutTable-->
      <tr>
        <td width="212" height="36">&nbsp;</td>
        <td width="79">&nbsp;</td>
        <td width="44">&nbsp;</td>
        <td colspan="3" rowspan="4" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
        </tr>
      <tr>
        <td height="19" valign="top" class="smallbold">Invoice To: </td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td height="19" valign="top" class="normaltext"><%=rsCustomerDetails("CustomerName")%></td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td height="19" valign="top" class="normaltext"><%=rsCustomerDetails("Location")%></td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td height="19" valign="top" class="normaltext">Glasgow</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td width="93"></td>
        <td width="195"></td>
        <td width="44"></td>
      </tr>
      <tr>
        <td height="19" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td></td>
        <td></td>
        <td></td>
      </tr>
      <tr>
        <td height="19" valign="top" class="normaltext">Tel: <%=rsCustomerDetails("ContactNumber")%></td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td></td>
        <td></td>
        <td></td>
      </tr>
      <tr>
        <td height="18"></td>
        <td></td>
        <td colspan="2" valign="top" class="smallbold">Invoice Date: </td>
      <% IF not rsInvoice.eof then %>
      <td valign="top" class="normaltext"><%=rsInvoice("InvoiceDate") %></td>
      <% End If %>
      <td></td>
      </tr>
      <tr>
        <td height="1"></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
      </tr>
      <tr>
        <td height="19" colspan="2" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td height="189" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
        <!--DWLayoutTable-->
        <tr>
          <td height="22" colspan="2" valign="top" class="smallbold">Service Description </td>
      <td width="182" valign="top" class="smallbold">Amount</td>
      <td width="5"></td>
        </tr>
            <% do while not rsInvoice.eof %>
        <tr>
          <td height="19" colspan="2" valign="top" class="normaltext"><%=rsInvoice("Description") %></td>
      <td valign="top" class="normaltext">&pound;<%= formatnumber(rsInvoice("Amount"), 2) %></td>
      <td></td>
        </tr>
      <% rsInvoice.MoveNext
      Loop %>
        <tr>
          <td width="454" height="19"></td>
            <td width="26"></td>
            <td>&nbsp;</td>
      <td></td>
        </tr>
              <% totalvat=(totalCost("totalCost")*0.175) %>
                <% invoiceamount = totalCost("totalCost")+totalvat %>
        <tr>
          <td height="18" valign="top" class="smallbold"><div align="right">Cost: </div></td>
        <td></td>
            <td valign="top" class="normaltext">£<%=totalCost("totalCost")%></td>
      <td></td>
        </tr>
        <tr>
          <td height="1"></td>
          <td></td>
          <td></td>
          <td></td>
        </tr>
        <tr>
          <td height="19" valign="top" class="smallbold"><div align="right">VAT @ 17.5 %: </div></td>
          <td>&nbsp;</td>
          <td valign="top" class="normaltext">&pound;<%= formatnumber(totalvat, 2) %></td>
          <td></td>
        </tr>
        <tr>
          <td height="14"></td>
          <td></td>
          <td></td>
          <td></td>
        </tr>
        <tr>
          <td height="33" valign="top" class="smallbold"><div align="right">Invoice Amount: </div></td>
          <td>&nbsp;</td>
          <td valign="top" class="normaltext">&pound;<%= formatnumber(invoiceamount,2) %> </td>
          <td></td>
        </tr>
        <tr>
          <td height="45"></td>
          <td></td>
          <td></td>
          <td></td>
        </tr>
            </table></td>
  </tr>
  <tr>
    <td height="56">&nbsp;</td>
  </tr>


</table>
<BLOCKQUOTE><H3></H3></BLOCKQUOTE>
<% rsCustomerDetails.Movenext
      loop %>
0
DiggaCommented:
What line number are you getting back on the error, and can you post that too, the specific code on that line? Is it the totalcost that's failing?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

johnnyc313Author Commented:
         <td valign="top" class="normaltext">&pound;<%= formatnumber(totalvat, 2) %></td>

This is the line that gives me the error.  If I take out the formatnumber then the error comes on this line:

          <td valign="top" class="normaltext">&pound;<%= formatnumber(invoiceamount,2) %> </td>
0
yirehCommented:
Try adding validation

<%
If IsNumeric(invoiceamount) Then
%>
   <td valign="top" class="normaltext">&pound;<%= formatnumber(invoiceamount,2) %> </td>
<%
Else
%>
   <td valign="top" class="normaltext">&pound; 0 </td>
<%
End If
%>


yireh
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyc313Author Commented:
Great Stuff, that worked.

Thanks for all your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.