Solved

Update Data Query Problem.

Posted on 2009-07-13
21
196 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:emi_sastra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
21 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24839099
>> 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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24839323
Hi rrjegan17,

I use vb code.

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

How to replace ---> dblSaldoAwalDbt ?

Thank you.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24839536
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 1

Author Comment

by:emi_sastra
ID: 24839563
Hi jefftwilley,

It makes sense since the regional setting is Indonesia.

Thank you.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24839848
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24839990
More Info, when I trace it.

dblSaldoAwalDbt =15382752.56

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

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24840252
VAl(dblSaldoAwalDbt) does not works.

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

Thank you.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24840449
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24840539
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24841046
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
 
LVL 1

Author Comment

by:emi_sastra
ID: 24845132
-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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24860084
Are you receiving an error when you run the query? If so, can you tell us what it is here?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24860255
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24860341
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24860347
While we're at it...what field Type is the destination field? Do you have it set up as numeric double?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24860393
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24860796
<<<The first code get the same error.>>>

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

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 24860877
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 24860987
use this one

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

Author Comment

by:emi_sastra
ID: 24861218
Yes, it works.

Thank you very much for your help.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 24861238
Sorry it took so long. Always happy to help.
J
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question