Concatenates two numbers instead of adding them in a text box, using plus sign (+) (1 Viewer)

[pacctono]

Hello!

I created a control text box that calculates the sum of two numeric columns that comes from a query. (the two numeric columns are in text box controls, too). Something like '=[monAhorroAporte]+[monAhorroPersonal]'. Instead I am getting the concatenation of the two numbers. 12,79+27,55=12,7927,55. Looks like access is using plus sign (+) to concatenate as ampersand (&) and trying the two numeric fields as string.

Can any one tell me if I can have a text box control to get aritmethic expression and how to do it?

NOTE: I am using spanish language

 

[plog]

I'd wrap the individual values in a Val() and then add them:

MS Access: Val Function

This MSAccess tutorial explains how to use the Access Val function with syntax and examples. The Microsoft Access Val function accepts a string as input and returns the numbers found in that string.

www.techonthenet.com

 

[pacctono]

I'd wrap the individual values in a Val() and then add them:

MS Access: Val Function

This MSAccess tutorial explains how to use the Access Val function with syntax and examples. The Microsoft Access Val function accepts a string as input and returns the numbers found in that string.

www.techonthenet.com

I tried that, but as the two numbers are taken as string (12,79 and 27,55), access takes only the integer part. the problem is the that it is expecting a decimal point and in spanish we use comma.

 

[plog]

Why are they as string? You need to fix the root issue. And don't blame a language, every nation and culture uses numbers--the same set in fact.

In your query you need to remove any formatting if you want these numbers to be numbers. If the issue is that you are storing them as text in a table, you need to fix that and change the field to the appropriate numeric type. Then in the form you can do the math you want and you can also format the control so that it diisplays those numbers in the format you want.

 

[pacctono]

Why are they as string? You need to fix the root issue. And don't blame a language, every nation and culture uses numbers--the same set in fact.

In your query you need to remove any formatting if you want these numbers to be numbers. If the issue is that you are storing them as text in a table, you need to fix that and change the field to the appropriate numeric type. Then in the form you can do the math you want and you can also format the control so that it diisplays those numbers in the format you want.

The values are numeric (they come from a SUM) and I formatted them as standard with two decimals. Spanish is my language and I like it but I do not understand why access translate words like 'True' in a boolean column/variable.

For example, I did an <txtSql = "UPDATE table SET bo_aps = " & booAps> where bo_aps is a table column of type boolean and booAps is a vba variable of type boolean. I got an error because 'txtSql' was "UPDATE table SET bo_aps = Verdadero" and did not understand the 'Verdadero' value. So I had to create a function to return the string "True" or "False".

 

[MajP]

The values are numeric (they come from a SUM) and I formatted them as standard with two decimals.

It is unclear how you formatted the number, but a format function returns STRING.
Wrap the sum in a CCUR() function instead to convert the value to a currency.
Bottom line is those inputs are STRINGS. Somehow you converted them into a string. Has nothing to do with the regional settings.

 

[pacctono]

It is unclear how you formatted the number, but a format function returns STRING.
Wrap the sum in a CCUR() function instead to convert the value to a currency.
Bottom line is those inputs are STRINGS. Somehow you converted them into a string. Has nothing to do with the regional settings.

Sorry! I did not explain myself. I am not using the format function. when I talk to format, I am talking to the properties of the control text box that I choosed 'standar' with 2 decimals as always do with numbers.

 

[arnelgp]

you can create a function in a Module to convert your textbox to Real number:

Public Function fnVal(ByVal var As Variant) As Variant
    Dim a As Integer
    fnVal = var
    If IsNull(var) Then
        Exit Function
    End If
    var = Trim$(var & "")
    a = Len(var) - InStrRev(var, ",")
    var = Replace$(var, ",", "")
    If a = 2 Then
        var = Left$(var, Len(var) - 2) & "." & Right$(var, 2)
    End If
    fnVal = Val(var)
End Function

=fnVal([Textbox1]) + fnVal([Textbox])