Problem with using function as a recordsource for text boxes 1

[luvmy92]

I am using Access 2000 on a Windows 2000 Professional machine, and am having a slight problem when using a function for my recordsource on certain text boxes. I have done this before but can't remember what I did to make it work (it's been a few years since I used Access).

I have a number of text boxes on my two detail lines that are numeric fields, and I don't want to change my query to alter the values of the fields for other reasons. I am using a fuction called ConvertAmt to either move "" to the box or a formatted string depending on the input. Here is my recordsource:

=ConvertAmt(CDbl([DiscountPrice]), "Amt&quot

And here is the function which is in a module of the database (and not the report) called Standard:

Function ConvertAmt(CnvAmt As Double, CnvType As String) As String
If CnvAmt = 0 Then
ConvertAmt = ""
ElseIf CnvType = "Pct" Then
ConvertAmt = Format$(CnvAmt, "##0.000000&quot
ElseIf CnvType = "Amt" Then
ConvertAmt = Format$(CnvAmt, "$###,###,##0.00&quot
ElseIf CnvType = "Qty" Then
ConvertAmt = Format$(CnvAmt, "#,###,##0&quot
End If
End Function

When the value is 0, it is putting a "" in the text box, but it is also putting a blank in there when the field has $150.00 in it coming from the query. Is there a way for me to see what's going on? When I set a break to the function in the debugger and then run the report, it never goes inside the function. Is there something I am missing?

Thanks,

Mike

 

[coco86]

Sorry Mike
When I try to recreate the situation it works fine for me so it's hard to say what the problem could be.

Where is the function located?
What is the datatype of [DiscountPrice]?
Is there any records for which it works or are all of them returning blank?
What happens if you change the second parameter of the function to one of your other choices?

 

[luvmy92]

Thanks for the reply. I have tried it with the function in the report as well as in a class module within the database, and I get the same results.

The datatype of DiscountPrice is double, but the function also gets called if Qty is passed and thats defined as a integer. I have tried it both ways... with or without the CDbl function and get the same results.

As for your 3rd and 4th questions, it returns blanks on every record (like it's not even performing the function), and I have tried it for all 3 conversion types with the same results.

My thinking is it's not performing the function at all. I don't know why it's not seeing it. Do I have to do some kind of compile or something?

Thanks again,

Mike

 

[coco86]

I think you're right, it doesn't seem like the function is running at all.

Do you have the code for the function located in a module?

One thing you might want to try is to move the function call to your query. Add a field like this:

ConvertedPrice:ConvertAmt(CDbl([DiscountPrice]), "Amt&quot

and then set the control source of the textbox to ConvertedPrice

 

[luvmy92]

That's the ticket! I'm not sure why it doesn't work from the recordsource but this does. I guess I could have done the following as well:

DiscountPrice: IIf([hdwrDiscountPrice]=0,"",Format(DiscountPrice, "$#,##0.00&quot)

The Function is much cleaner and takes up less space. Thanks for your help. I think that deserves a star!

Mike