Solved

Update Data Query Problem.

Posted on 2009-07-13
21
191 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 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