Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update Data Query Problem.

Posted on 2009-07-13
21
Medium Priority
?
201 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

610 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