Formatting currency Problem with Access 1

[humbletechie]

Hi,

The fields format is set to Currency in the Access DB but it's displaying on the page as a string. I have tried using the formatcurrency and formatnumber functions and both are giving me a Type mismatch error. Below is the code for all three ways I have tried.

<%=&quot;$&quot;&FormatNumber(Settlements(&quot;Atty Fees&quot,2)%> - Gives type mismatch error.

<%=FormatCurrency(Settlements(&quot;Atty Fees&quot,2)%> - Gives type mismatch error.

<%=Settlements(&quot;Atty Fees&quot%> - Displays it in a string like 800000.

Can someone please help?

Thanks!

 

[mit99mh]

You sound like you are sending the wrong type to the functions FormatNumber, FormatCurrency. These functions need numerical types. To check this use the VarType function, in my experience this is invaluable for a no-typed language like VBScript.

To check:

<%= VarType( Settlements(&quot;Atty Fees&quot )%>

I imagine it will output an 8 indicating it is a string.

What you need to do is Cast the string to an integer.

The following code may work

<%=&quot;$&quot;&FormatNumber( CInt(Settlements(&quot;Atty Fees&quot,2) )%> -

<%=FormatCurrency(CInt(Settlements(&quot;Atty Fees&quot),2)%> -

Hope this helps

 

[humbletechie]

Thanks for the VarType tip, that's good to know. I did as you said and I am getting a VarType of 6, which I believe is currency. I also tried changing the code to
<%=FormatCurrency(CInt(Settlements(&quot;Atty Fees&quot),2)%> and it's giving me a Overflow: 'CInt' error. Any other ideas??

Thanks.

 

[mit99mh]

if the VarType is 6 you can remove the CInt cast - from memory the FormatCurrency functions take lots of arguements and yours takes 2 try using:

<%=FormatCurrency(Settlements(&quot;Atty Fees&quot,2, -1, 0,-1)%>

Or to use your formatting

<%=FormatCurrency(Settlements(&quot;Atty Fees&quot,2,,,)%>

Hope this helps

 

[humbletechie]

Thanks for your response. The first one is giving a type mismatch error and the second one is giving me a syntax error. Any other ideas?

 

[mit99mh]

just tried a few things out with FormatCurrency and it works with int, string or curr data types - I got this error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'FormatCurrency'

when I passed no value

<%
currency = &quot;&quot;

%>
<%=FormatCurrency(c, 2)%>

to the FormatCurrency function (above code) - If this is what you see, I'd check that your function Settlements(&quot;Atty Fees&quot),is actually returning a value

<%= Settlements(&quot;Atty Fees&quot %>

 

[humbletechie]

Thanks again. It is returning a value some of the time but there are a lot of nulls in the data. Can this be causing the problem?

Thanks!

 

[mit99mh]

What do you mean by nulls?

It would help if you pasted the output so I can see it.

I'd try something like:

<%
Dim fees
fees = Settlements(&quot;Atty Fees&quot
Response.write &quot;<li>fees = &quot; & fees & &quot;<li>&quot; & VarType(fees)
%>

And post me what is displayed.

 

[humbletechie]

Here is the output:

fees =
1

OR

fees = 511000
6

Thanks.

 

[mit99mh]

excellent now we are rocking -
<%
Dim fees
fees = Settlements(&quot;Atty Fees&quot
%>

before calling

FormatCurrency(fees,2)

insert the code

<%
' check value is not vbNull
If (VarType <> 1 ) Then
FormatCurrency(Settlements(&quot;Atty Fees&quot,2)
End If
%>
This should work now

You could include an Else condition so:

<%
If (VarType <> 1 ) Then
FormatCurrency(Settlements(&quot;Atty Fees&quot,2, -1, 0,-1)
Else
Response.write &quot; No fees &quot;
End If
%>

Hope this works

 

[humbletechie]

Finally Success! Thank you sooooo much for your help. Below is how I finally got it to work.

<%
Dim fees
fees = Settlements(&quot;Atty Fees&quot
If VarType(fees) = 1 Then fees = &quot;-&quot; else
If VarType(fees) = 6 Then fees = FormatCurrency(Settlements(&quot;Atty Fees&quot,2)
%>

Then for the table output I wrote <%=Response.Write(&quot;&quot;&fees&&quot;&quot%>

I will remember the VarType from now on, I can think of a ton of other area's this will be useful in.

 

[mit99mh]

excellent news I'm glad you got it to work - one word of advice whenever you post something post all the code and error message too

Regards

Mick