Summing List Box from in Text Box WIth Data Type Error

[Fattire]

I use this code to sum up column 9 into a list box whenever its requeried but if there isn't at least a $0.00 in the column it says I have a data type problem. It's hung up on the NULL's. The data type in the table the value is held is set to currency. I've tried NZ, I've tried Format, IsNull() but nothing seems to work. Messes up on the line:

listsum4 = listsum4 + (Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x))

Any help would be aprpeciated:

Public Function AMTCOLLECTED()
    
    Dim listrs4 As Single
    Dim listsum4 As Single
    
    listrs4 = Forms!FRM_MAIN!LST_OPEN_BALANCES.ListCount
    If listrs4 = "0" Then
    Forms!FRM_MAIN!TXT_AMT_COLLECTED.Value = "0"
    Else
    x = 1
    Do
    listsum4 = listsum4 + (Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x))
    x = x + 1
    Loop Until x = listrs4
    Forms!FRM_MAIN!TXT_AMT_COLLECTED.Value = listsum4
    
    End If
    
End Function

 

[Fattire]

And yes "On Error Resume Next" works... but I just hate using that. But if that's what I need to do then... so be it.

 

[dhookom]

I expect this line should generate an error

If listrs4 = "0" Then

listrs4 is dim'd as single and "0" is text.

Duane
Hook'D on Access
MS Access MVP

 

[Hap007]

How about giving this a try:
listsum4 = listsum4 + CSng(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x))

Also, just a thought, does the last value for x exist?

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Fattire]

dhookom - being single it should, 0 erred out, "0" does not for some reason.

Hap007 - conversion function - excellent idea, never thought of that CSng but it gave me the error 13 type mismatch unfortunately, tried the CDbl and CCur as well, no luck

Ahhh... just tried Val - that worked... thanks for the direction - I'm going to try and get rid of that first if statement now.

Here's the line:

listsum4 = listsum4 + Val(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x))

 

[dhookom]

I expect the data type of a column in a list box is a string variant.

Duane
Hook'D on Access
MS Access MVP

 

[boblarson]

Normally you can use the NZ function to handle nulls.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[Fattire]

The list box is populated by a query. The table the query comes from for column(9) has a data type of Currency, the query for column 9 has no format properties. Not sure where the variant comes into play though.

btw... Val stopped working after I did a compact and repair, so I recently went back to 'On Error Resume Next'

I tried these:
NZ(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x)), 0)
NZ(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x)), "0")
CDbl(NZ(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x))
CCur(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x))
CSng(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x))
Val(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x))

I think there may be some conflict with the expression I'm using for calculating column 9: I lost the formatting with the space function so I forced it in there, but the space or right may be messing it up as well:

Amt Collected: Right(Space(13) & Format([APPLIED_AMOUNT],"$#,###.00"),13)

 

[PHV]

What about this ?
listsum4 = listsum4 + Val(Replace(Replace(Forms!FRM_MAIN!LST_OPEN_BALANCES.Column(9, x), "$", ""), ",", ""))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hap007]

When all else fails, I would do something like this
Dim tempValue as Single
Dim tempFld as string

then...
tempValue = 0
tempFld = Trim(Forms!FRM_MAIN!LST_OPEN_BALANCES.column(9, x) & " ")
If Len(tempFld)>0 Then
tempValue = Val(TempFld)
End If

listsum4 = listsum4 + tempValue


I know it is ugly code, but the Trim and Len should keep always work.

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Fattire]

PHV - That works, and makes sense now... took the resume next out and it still works -

Hap007 I stuck your code in one of the columns it also does not error, thanks.

 

[Hap007]

Just a suggestion,

It sounds like the formatting was getting in the way.

In the past, I have created extra columns in the list box and set their width to zero.

Then, I can store unformated data in those hidden columns for later use.

This works great when storing key valuse and the like.

If you had stored the unformated amounts in a hidden column, you might never had this problem in the first place.

Just my 2 cents, Enjoy,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Fattire]

By the way I had to change the data type in the module to Double; Single was causing a .03 variation every $150,000 in the totals.

 

[boblarson]

If this is money then you should be using the CURRENCY datatype actually. It is even more accurate than Double.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com