[X]My Assistant

[abduhsuryadi]

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

[Doug Steele]

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?

[cheekybuddha]

Hi,



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

[cheekybuddha]

I wish I had a business where I required a DECIMAL(19,4) field to hold the price of one of my offerings!!!

[GroverParkGeorge]

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.

[abduhsuryadi]

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

[GroverParkGeorge]

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?

[abduhsuryadi]

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

[GroverParkGeorge]

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.

[abduhsuryadi]

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


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


What is the problem?

[cheekybuddha]

Hi,

You can try:

CODE

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

hth,

d

[abduhsuryadi]

I have many field that use decimal in one table. i thought it's not efective to use format.

[cheekybuddha]

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?

[cheekybuddha]

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

[abduhsuryadi]

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

[GroverParkGeorge]

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.

[abduhsuryadi]

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

[abduhsuryadi]

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

[abduhsuryadi]

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