'insert into' and doubles

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all!

Could anyone tell me how to make this code work? The field from the SELECT statement is a double. Constructing the INSERT INTO statement like this, the double is inserted with a comma (like 3,27). I would like a point (like 3.27) instead. The problem is that the INSERT statement is constructed like this.

"INSERT INTO table1 (col1) VALUES (3,27)"

This does not work because I'm not trying to insert two integers, but one double.

-----------------------------------------
dim rs as ADODB.Recordset
dim cmd as ADODB.Command

rs.Open "SELECT col1 FROM table2 WHERE col2=value", _
CurrentProject.Connection

cmd.CommandText = "INSERT INTO table1 VALUES(" & _
rs!col1 & ")"

----------------------------------------------------------
 
It should be so INSERT INTO table1 (col1) VALUES (3.27)

--
Bye

Dariusz My¶liwiec

----------------------------------------------------------------------
E-mail: (e-mail address removed)
WWW: http://www.darekmysliwiec.biz/
-----------------------------------------------------------------------


U¿ytkownik "ScubaD said:
Hi all!

Could anyone tell me how to make this code work? The field from the SELECT
Click to expand...
statement is a double. Constructing the INSERT INTO statement like this, the
double is inserted with a comma (like 3,27). I would like a point (like
3.27) instead. The problem is that the INSERT statement is constructed like
this.
 
Try:

cmd.CommandText = "INSERT INTO table1 VALUES(" & _
Replace(rs!col1, ",", ".") & ")"



HTH,

Kevin



ScubaD said:
Hi all!

Could anyone tell me how to make this code work? The field from the SELECT
Click to expand...
statement is a double. Constructing the INSERT INTO statement like this, the
double is inserted with a comma (like 3,27). I would like a point (like
3.27) instead. The problem is that the INSERT statement is constructed like
this.
 
I think Kevin's solution should work. While Access itself will use the
Regional, SQL will not. This is most often a problem with date formats (ie
dd/mm/yyyy rather than mm/dd/yyyy), but it applies to the decimal too.
Therefore, you need to convert it to US decimal format for the SQL string.
It will remain displayed in the database with a comma.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

ScubaD said:
Yes, the decimal character is set to comma in the regional settings. I
Click to expand...
don't want to change this however, because the program should not be
dependant on the regional settings. There must be a way to convert the
double to a string using the decimal character I want.
Or is there some other way I should approach this? How can I do the same
Click to expand...
thing without converting the result to a string first?
 
Yes, the decimal character is set to comma in the regional settings. I
don't want to change this however, because the program should not be
dependant on the regional settings. There must be a way to convert the
double to a string using the decimal character I want.
Click to expand...

This is exactly analogous to the problem with passing dates. Jet _always_
wants dots for decimal points regardless of the regional settings. VBA on
the other hand will use regional settings for default number-to-text
conversions. You have to use an explicit format:

cmd.CommandText = _
"INSERT INTO table1 VALUES(" & _
Format(rs!col1, "0\.00") & _
")"

The backslash is used to force use of a point, as windows will interpret
0.00 as using the regional setting decimal separator. If you see what I
mean.

Hope it helps
No it doesn't. After a bit of experimenting with my Regional Settings, the
above format DOES NOT WORK. In fact, I cannot find any way of forcing a
period when the control panel is set to 1.234,56 for thousands and
decimals. This does the job but it's pretty ugly:

Replace(Format(rs!col1,"0.00"), ",", ".")

which looks a bit confusing with all the commas, but format() returns the
2,34 and then the Replace() changes it to 2.34 and I cannot do any better
than that.

I assume this is a bug within the Format() function: I am using Windows
98SE with OLEAUT32.DLL version 2.40.4518 if anyone else thinks it has been
fixed for later releases.

Hope it helps this time!


Tim F
 
Thankyou guys!

I decided to go with the solution suggested by Kevin. This seems to be the most straight-forward method.

Thanks !
/ ScubaD
 
Back
Top