Update Data Query Problem.

Hi,

I have query below.

The problem is at :

SaldoAwalDbt = SaldoAwalDbt + 15382752,56,

It is because of regional setting.

How to solve this problem?

Thank you.
UPDATE THLEDGER2009 SET SaldoAwalDbt = SaldoAwalDbt + 15382752,56, SaldoAwalKrd = SaldoAwalKrd + 0, UpdId = 'ADMINIST', UpdDate = '2009/07/13' WHERE AccNo = '1101.01.01 ' AND CostCentre = '' AND PlantCode = '      '

Open in new window

LVL 1
emi_sastraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> It is because of regional setting.

Regional settings are applicable at the application / presentation layer. In SQL Server, Integer wont consider ","  in between integer values and hence you need to replace , with . in your query to make it work.

I believe SaldoAwalDbt is a column of datatype decimal or float like that.

Kindly try this one out:
UPDATE THLEDGER2009 
SET SaldoAwalDbt = SaldoAwalDbt + 15382752.56, 
SaldoAwalKrd = SaldoAwalKrd + 0, 
UpdId = 'ADMINIST', 
UpdDate = '2009/07/13' 
WHERE AccNo = '1101.01.01 ' 
AND CostCentre = '' AND PlantCode = '      '

Open in new window

0
emi_sastraAuthor Commented:
Hi rrjegan17,

I use vb code.

 strSqlCommand = "UPDATE " & strFileName
                strSqlCommand &= " SET " & strDebet & " = " & strDebet & " + " & dblSaldoAwalDbt

How to replace ---> dblSaldoAwalDbt ?

Thank you.
0
jefftwilleyCommented:
Can you show us the source for the oddly formatted value? it appears to be stored in a Double variable. The commas, in their position in the number do not make sense.
J
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

emi_sastraAuthor Commented:
Hi jefftwilley,

It makes sense since the regional setting is Indonesia.

Thank you.
0
jefftwilleyCommented:
ok, let's say the values are correct,

then modify this way

 strSqlCommand = "UPDATE " & strFileName
                strSqlCommand &= " SET " & strDebet & " = " & strDebet & " + " & VAl(dblSaldoAwalDbt)

Access is obviously not understanding the formatting. Perhaps pulling the value from the field will help.

another variant might be this

 strSqlCommand = "UPDATE " & strFileName
                strSqlCommand &= " SET " & strDebet & " = " & strDebet & " + " & val(replace(dblSaldoAwalDbt,",",""))

J



0
emi_sastraAuthor Commented:
More Info, when I trace it.

dblSaldoAwalDbt =15382752.56

But when I see the strSqlCommand  then it becomes 15382752,56

Thank you.
0
emi_sastraAuthor Commented:
VAl(dblSaldoAwalDbt) does not works.

val(replace(dblSaldoAwalDbt,",","")), decimal value become non decimal value, increase the value.

Thank you.
0
jefftwilleyCommented:
UPDATE THLEDGER2009
SET SaldoAwalDbt = SaldoAwalDbt + 15382752.56,
SaldoAwalKrd = SaldoAwalKrd + 0,
UpdId = 'ADMINIST',
UpdDate = '2009/07/13'
WHERE AccNo = '1101.01.01 '
AND CostCentre = '' AND PlantCode = '      '

Is the current value in the SaldoAwalDbt field NULL by chance?
0
emi_sastraAuthor Commented:
Yes, it should be the syntax. But I use vb code not SP.

-Is the current value in the SaldoAwalDbt field NULL by chance?
Allow null is checked.

Thank you.
0
jefftwilleyCommented:
I asked the question because often when you add 1 + Null you do not get a value.

Is there an error when you run the query?

And I understand that you're not using a Stored Procedure. You're in Access.
J
0
emi_sastraAuthor Commented:
-I asked the question because often when you add 1 + Null you do not get a value.
No, the value to add will not a null value.

I use VB 2005.

Use VAL, the code you provided, still not work.

Thank you.
0
jefftwilleyCommented:
Are you receiving an error when you run the query? If so, can you tell us what it is here?
0
emi_sastraAuthor Commented:
Refer to you post Id : 24839848

 strSqlCommand = "UPDATE " & strFileName
                strSqlCommand &= " SET " & strDebet & " = " & strDebet & " + " & VAl(dblSaldoAwalDbt)

The above code still get error.

 strSqlCommand = "UPDATE " & strFileName
                strSqlCommand &= " SET " & strDebet & " = " & strDebet & " + " & val(replace(dblSaldoAwalDbt,",",""))

For example dblSaldoAwalDbt =10.20, then it become 1020 after insert.

Thank you.
0
jefftwilleyCommented:
Is the error just that the value is wrong? or are you getting another type of error?

So in your regional settings, you have it so that a decimal's separator character is a comma?

ie.  10.20  is stored in your database as 10,20  ?
0
jefftwilleyCommented:
While we're at it...what field Type is the destination field? Do you have it set up as numeric double?
0
emi_sastraAuthor Commented:
The first code get the same error.
The second code as I mentioned in the recent post.

It is money type.

Btw, use MS SQL not Access.

Thank you.
0
jefftwilleyCommented:
<<<The first code get the same error.>>>

ok, what is the error? Is there an error code?

0
emi_sastraAuthor Commented:
UPDATE THLEDGER2009 SET SaldoAwalDbt = SaldoAwalDbt + 55856497,5, SaldoAwalKrd = SaldoAwalKrd + 0, UpdId = 'ADMINIST', UpdDate = '2009/07/15' WHERE AccNo = '1101.02.06 ' AND CostCentre = '' AND PlantCode = '      '


Incorrect syntax near '5'.

Thank you.
0
jefftwilleyCommented:
use this one

strSqlCommand = "UPDATE " & strFileName
                strSqlCommand &= " SET " & strDebet & " = " & strDebet & " + " & replace(dblSaldoAwalDbt,",",".")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor Commented:
Yes, it works.

Thank you very much for your help.
0
jefftwilleyCommented:
Sorry it took so long. Always happy to help.
J
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.