Solved

Update Data Query Problem.

Posted on 2009-07-13
21
188 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now