UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Problem Retrieve Data Decimal Type From Ado And Dao    
 
   
abduhsuryadi
post Aug 3 2018, 09:25 AM
Post#1



Posts: 13
Joined: 3-August 18



Haloo...
I have access 2007 as Fronte End and MySQL database as Back End. Database name: myshop, table name : Sales and one of field name: Price. The type of the field is Decimal (19,4). The question is why these three code below get different result?

CODE
      dim db as DAO.database
      dim rst as DAO.recordset
      set db=opendatabase("",false,false,"ODBC;Driver=MySQL ODBC 3.51 Driver;server=localhost;Database=myshop")
      set rst=db.openrecordset("Sales")
      debug.print rst!price
      'Result : 3500.0000


Second code with currentdb
CODE
      dim db as DAO.database
      dim rst as DAO.recordset
      set db=CurrentDb
      set rst=db.openrecordset("Sales")
      debug.print rst!price
      'Result : 3500


Third code with ADODB
CODE
      dim db as New ADODB.Connection
      dim rst as New ADODB.recordset
      db.open "Driver=MySQL ODBC 3.51 Driver;server=localhost;Database=myshop"
      rst.open "Sales",db
      debug.print rst!price
      'Result : 3500


The question is how to get result like the second and the third code with the first one? or why the first code get different data? i dont want the .0000 in the last result of the first code
Go to the top of the page
 
Doug Steele
post Aug 3 2018, 09:35 AM
Post#2


UtterAccess VIP
Posts: 21,984
Joined: 8-January 07
From: St. Catharines, ON (Canada)


welcome2UA.gif

How a value gets displayed by the Debug.Print statement really doesn't say much about the data type.

Do you get the correct values when there are decimal values for the field?
Go to the top of the page
 
cheekybuddha
post Aug 3 2018, 09:35 AM
Post#3


UtterAccess VIP
Posts: 10,538
Joined: 6-December 03
From: Telegraph Hill


Hi,

welcome2UA.gif

>> how to get result like the second and the third code with the first one? <<
CODE
' ...
      debug.print CCur(rst!price)


>> why the first code get different data? <<
I think that the ODBC connector will translate such a large DECIMAL number from MySQL as string in VBA.

You do realise you are using a very old version of the MyODBC connector. I would advise trying the latest version 5.X, or even give 8.X a try (but that is very new).

Also, you may want to add some OPTIONS to your connection string.

See if using:
CODE
' ...
      set db=opendatabase("",false,false,"ODBC;Driver=MySQL ODBC 3.51 Driver;server=localhost;Database=myshop;OPTIONS=16386")
' ...


See here for MyODBC options reference

hth,

d
Go to the top of the page
 
cheekybuddha
post Aug 3 2018, 09:40 AM
Post#4


UtterAccess VIP
Posts: 10,538
Joined: 6-December 03
From: Telegraph Hill


I wish I had a business where I required a DECIMAL(19,4) field to hold the price of one of my offerings!!!
Go to the top of the page
 
GroverParkGeorge
post Aug 3 2018, 09:41 AM
Post#5


UA Admin
Posts: 33,936
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Not to be too flippant, but isn't it true that all three results are logically equivalent? I.e. isn't 3500.0000 mathematically equal to 3500 ?

Given that the results in all cases are mathematically the same, does it really matter how they are formatted when initially retrieved?

You can ALWAYS apply formatting within your Access accdb if you want to DISPLAY the result as 3500, so it's largely a matter of DISPLAYING the result, not CALCULATING it or retrieving it.

That said, the first code is retrieving the actual value as stored and displayed in the MySQL database--i.e. it has the four decimal places with it. The second version depends on how that field is defined in the linked table in Access and that we can't see here. Is it somehow being formatted in Access as an integer, or with no digits of precision?

I would have to do some more research on how the ADO approach might differ.
Go to the top of the page
 
abduhsuryadi
post Aug 3 2018, 09:42 AM
Post#6



Posts: 13
Joined: 3-August 18



I have use this
ccur(rst!price) and the result 35000000. because i'm indonesian and point in my country is (,) not (.). in my country point (.) means thousand

And I have added option 16386 and the result is the same.

And I have tried with driver: MySQL ODBC 5.2 ANSI Driver and MySQL ODBC 5.2 UnicodeDriver. And the result is the same also. The problem when I use OpenDatabase instead of Currentdb or ADODB.connection

I use three mehtod
1. DAO with opendatabase ; Result 3500.0000 (if i use clng or ccur or cint it return to 35000000)
2. DAO with currentdb ; Result 3500 (it's a real result)
3. ADODB connection and recordset ; Result 3500 (It's also a true result)
This post has been edited by abduhsuryadi: Aug 3 2018, 10:01 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 3 2018, 10:04 AM
Post#7


UA Admin
Posts: 33,936
Joined: 20-June 02
From: Newcastle, WA


Oh, I should have thought to ask about the regional settings. I made the wrong assumption that the decimal was for whole numbers, not for thousands.

Whole different thing.

So, you're saying that the result is being represented as 3500 THOUSANDS, vs 3500 and you need it to be 3500, not 3500 thousand?


What that tells me is that the problem is back in the MySQL database. Is it using the same regional settings (i.e. the . means "thousands") as your Access db?
Go to the top of the page
 
abduhsuryadi
post Aug 3 2018, 10:09 AM
Post#8



Posts: 13
Joined: 3-August 18



when i put the result in textbox it's shows 3500.0000 even the format of text box is standard but if i use ADO or currentdb it's show in textbox 3.500,00
It's like the first (3500.0000) consider text. And if i convert it to currency or to long or to integer (CCur, Clng or CInt) it will be 3.500,00


This post has been edited by abduhsuryadi: Aug 3 2018, 10:13 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 3 2018, 10:10 AM
Post#9


UA Admin
Posts: 33,936
Joined: 20-June 02
From: Newcastle, WA


Oh, you added more detail to your answer, and now I can see that you and Access are, indeed, both "right".

Access is bringing back that value, 3500.0000, thinking it means 3500 with 4 digits of precision for the decimals. I.e. that's the same value as 3500 as far as Access is concerned at that point.

However, when you format that result with CCur() using YOUR INDONESIAN REGIONAL SETTINGS, those settings "change" the . from meaning "Decimal" to meaning "Thousands" and unintentionally multiplying the result.

At least that is how it looks from here based on what we can see.


In other words, you ARE getting the "right" answer in all three cases, but because of the way it's being handled inside Access with regional settings for your location, using the CCur() expression generates a "wrong" answer.

Knowing that, you can handle the results appropriately, I think.
Go to the top of the page
 
abduhsuryadi
post Aug 3 2018, 10:32 AM
Post#10



Posts: 13
Joined: 3-August 18



this is the result in my textbox when i use currentdb or ADODB
Attached File  rst1.png ( 12.11K )Number of downloads: 0


And this is the result when i use opendatabase("",false,false,connstring)
Attached File  rst2.png ( 11.79K )Number of downloads: 0


What is the problem?
Go to the top of the page
 
cheekybuddha
post Aug 3 2018, 10:44 AM
Post#11


UtterAccess VIP
Posts: 10,538
Joined: 6-December 03
From: Telegraph Hill


Hi,

You can try:
CODE
' ...
      set rst=db.openrecordset("SELECT FORMAT(price, 4, 'id_ID') FROM Sales;")
      debug.print CCur(rst!price)
' ...


hth,

d



Go to the top of the page
 
abduhsuryadi
post Aug 3 2018, 10:57 AM
Post#12



Posts: 13
Joined: 3-August 18



I have many field that use decimal in one table. i thought it's not efective to use format.
Go to the top of the page
 
cheekybuddha
post Aug 3 2018, 10:59 AM
Post#13


UtterAccess VIP
Posts: 10,538
Joined: 6-December 03
From: Telegraph Hill


Then perhaps you will have to use one of the other 2 methods where the number is returned correctly.

I agree - using Format() is not a very useful solution. It returns strings which have to be converted to number variables in Access.

Did you try a newer version of the connector to see if it makes any difference?
Go to the top of the page
 
cheekybuddha
post Aug 3 2018, 11:03 AM
Post#14


UtterAccess VIP
Posts: 10,538
Joined: 6-December 03
From: Telegraph Hill


One last try:
CODE
      dim db as DAO.database
      dim rst as DAO.recordset
      set db=opendatabase("",false,false,"ODBC;Driver=MySQL ODBC 3.51 Driver;server=localhost;Database=myshop")
      db.Execute "SET lc_time_names = 'id_ID';", dbFailOnError
      set rst=db.openrecordset("Sales")
      debug.print rst!price


hth,

d
Go to the top of the page
 
abduhsuryadi
post Aug 3 2018, 11:17 AM
Post#15



Posts: 13
Joined: 3-August 18



I've used 3.51, 5.2 ANSI, 5.2 Unicode, 8.0 ANSI and 8.0 Unicode
the result is the same..... 3500.0000 with DAO.openrecordset
With ADO it's 3500,
I don't use ADO because it's slower then DAO

The last code:

CODE
     db.execute ........


it's return error number 3065: Cannot execute a select query
This post has been edited by abduhsuryadi: Aug 3 2018, 11:21 AM
Go to the top of the page
 
GroverParkGeorge
post Aug 3 2018, 12:31 PM
Post#16


UA Admin
Posts: 33,936
Joined: 20-June 02
From: Newcastle, WA


After being away for a while and thinking about this some more, it dawned on me.

Your screen shot shows only DISPLAY FORMAT differences. It does NOT show different values. And that is the key.

Let's step back a bit. If the period is the Thousands indicator in your environment, then 3500.0000 would be an impossible value, would it not? Would it not be formatted as 35.000.000? In other words, to be consistent, you have to take into consideration both the delimiter and the number of digits, not just the delimiter.

I go back to my first statement. All three results are the same value: 3500

One of those results includes four additional digits of precision, which do NOT change that value.

To prove or disprove this, please alter your statements like this:

debug.print rst!price + 1

Now what are the three results? Please let us know what they are.

Thanks.
Go to the top of the page
 
abduhsuryadi
post Aug 3 2018, 05:27 PM
Post#17



Posts: 13
Joined: 3-August 18



it's the result when I use debug.print rst!price + 1

1. With opendatabase("",false,false,connstr) : 35000001
2. With CurrentDb : 3501
3. With ADODB : 3501
Go to the top of the page
 
abduhsuryadi
post Aug 3 2018, 06:37 PM
Post#18



Posts: 13
Joined: 3-August 18



The problem is in Region and Language Setting
When I use format English (American) the result becom correct 3500
but when i return to Indonesian the result become incorrect: 35000000

Is there any solution for this?
This post has been edited by abduhsuryadi: Aug 3 2018, 06:38 PM
Go to the top of the page
 
abduhsuryadi
post Aug 9 2018, 05:14 PM
Post#19



Posts: 13
Joined: 3-August 18



I found a soulition for this.
it's an error of decimal lenght. I change my decimal from 19,4 to 15,2
OpenDatabase cannot handle like this lenght 16,2; 16,4; 16,3; 17,3; 19,4; 19,2 etc. it will return into text in access form.
But if i set database as currentdb or using ADODB, it runs well.
I don't know why this problem persist
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2018 - 12:30 PM