INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

update a table with a decimal value from a textbox in a form with vba

update a table with a decimal value from a textbox in a form with vba

(OP)
Hi,
I would like to update a table with a decimal that is in a textbox on a form. The problem is when I use DoCmd.RunSQL "Update.... I only can update an Integer. I tried the conversion whtit VAL, no error but the deciamls are gone! The type in the table is decimal. Tryed other types like currency but still error. Is there a solution?
Thanks

RE: update a table with a decimal value from a textbox in a form with vba

Your "field size" property of the table is probably set to long integer. Needs to be single, double, or decimal.

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
Sorry, no it is set to double.
Thanks

RE: update a table with a decimal value from a textbox in a form with vba

Can we see the code?

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
Yep:
Getalwaarde = [frmKassa Subformulier].[Form]![txtAlgTot]
DoCmd.RunSQL "Update tblVerkoopKassa Set [TotaalBruto] =" & Getalwaarde & " where VerkoopKassaId=" & gintTicketnr & ""

RE: update a table with a decimal value from a textbox in a form with vba

Where is Getalwaarde declared and what is it declared as?
Getalwaarde = [frmKassa Subformulier].[Form]![txtAlgTot]
debug.print "Get " & Getalwaarde

show the debug

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
Debug result
Get 2,36

Dim Getalwaarde As Double
is declared as double

RE: update a table with a decimal value from a textbox in a form with vba

I believe there may be an issue with the regional settings if you are using a comma instead of a point as the decimal seperator in the regional settings. Not sure what the work around is. Let me see if I can test.

RE: update a table with a decimal value from a textbox in a form with vba


I have read the work around is to convert to text, replace the comma. Try this

CODE -->

dim strGet as string
strGet = cstr([frmKassa Subformulier].[Form]![txtAlgTot])
strGet = replace(strGet,",",".")
debug.print strGet
GetalWaarde = cdbl(strGet)
debug.print Getalwaarde
DoCmd.RunSQL "Update tblVerkoopKassa Set [TotaalBruto] =" & Getalwaarde & " where VerkoopKassaId=" & gintTicketnr & 
"

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
Result from the debug
2.36
236
I don't think the regional setting is the problem. I have read about this before because I thougt also that would be the problem.
Sorry

RE: update a table with a decimal value from a textbox in a form with vba

Quote:

I don't think the regional setting is the problem
No that is clearly what it is as the debug proves.

In US settings
?cdbl("2,36")
236
?cdbl("2.36")
2.36

So it without the replace

CODE -->

dim strGet as string
strGet = cstr([frmKassa Subformulier].[Form]![txtAlgTot])

debug.print strGet
GetalWaarde = cdbl(strGet)
debug.print Getalwaarde
DoCmd.RunSQL "Update tblVerkoopKassa Set [TotaalBruto] =" & Getalwaarde & " where VerkoopKassaId=" & gintTicketnr & 

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
Sorry, I had to break the contact yesterday. I try the code!
Ok!
If I use youre last code I get the same error that the update contains a syntax error.

RE: update a table with a decimal value from a textbox in a form with vba

See this post on the MS site. As far as I can tell SQL cannot handle a comma as a decimal seperator. According to the post you can use val but have to do it when the query executes and not before so the sql string has to contain "Val" in it.
Also you need to be able to debug your code with print statements so you not what is going on. Then you make a sql string ALWAYS do it as follows so you can debug and see any syntax errors

Dim strSql as string
strSql = "Update tblVerkoopKassa Set [TotaalBruto] = val("" & Getalwaarde & "") where VerkoopKassaId=" & gintTicketnr
'check your sql and post if does not work
debug.print strsql
currentDb.execute strsql

http://answers.microsoft.com/en-us/msoffice/forum/...

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
This is the debug result
Update tblVerkoopKassa Set [TotaalBruto] = val(" & Getalwaarde & ") where VerkoopKassaId=2090
Getalwaarde hat a value = 12,12 but in the tabel it is 0
I check the post on MS

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
In the post on MS they used this syntax
strSql = "Update tblVerkoopKassa Set [TotaalBruto] = val("""& Getalwaarde & """) where VerkoopKassaId=" & gintTicketnr &""

debug result
Update tblVerkoopKassa Set [TotaalBruto] = val(" & Getalwaarde & ") where VerkoopKassaId=2090

but still no decimal

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
I think I have found a solution!!!
If I use this instruction without the VAL than it is working!!!

Thank you for your help without it I was still trying

RE: update a table with a decimal value from a textbox in a form with vba

Both syntax are wrong as the debug shows. You should have something that resolves like
Update tblVerkoopKassa Set [TotaalBruto] = val('2,56') where VerkoopKassaId = 2090

Try this instead
strSql = "Update tblVerkoopKassa Set [TotaalBruto] = val("&"'" & [frmKassa Subformulier].[Form]![txtAlgTot] & "'" &") where VerkoopKassaId = " & gintTicketnr

RE: update a table with a decimal value from a textbox in a form with vba

(OP)
Debug result
Update tblVerkoopKassa Set [TotaalBruto] = val('12,45') where VerkoopKassaId = 2096

I changed it in:
strSQL = "Update tblVerkoopKassa Set [TotaalBruto] = (""" & Getalwaarde & """) where VerkoopKassaId=" & gintTicketnr & ""

debug result
Update tblVerkoopKassa Set [TotaalBruto] = ("1151,53") where VerkoopKassaId=2097
and then it is working, table is updated correct

RE: update a table with a decimal value from a textbox in a form with vba

In summary what works is to do the SQL with a string representation of the value in the regional format and SQL will handle the parsing. So you should not convert it to a point representation but keep the comma representation. Here is a nice looking function that seems to handle lots of cases.
http://stackoverflow.com/questions/38728268/ms-acc...

CODE -->

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close