DDL for INSERT/UPDATE with Decimal Formatting

[Guest]

I have a need to do DDE to do a number of Insert and Update statements (via
SQL) with a program that's provided to multiple countries, some of whom use
different number formats than I do (english version, USA).

Specifically, they would use 1.000,05 for $1,000.05...

When i do DDL with an actual comma in it, this messes the SQL syntax up...

What i've been TRYING to do is use a format(number,format) statement, like
this:
Code:
--------------------------------------------------------------------------------

UpdateSQL = "UPDATE SalesInfo SET SalesRating = " & format(AvgMark, "0.0")
& ", NumSales= " & NumSales & _ " WHERE SalesID= "
& rstSalesResults!fk_Sales_ID
--------------------------------------------------------------------------------

Actually this doesn't even work.

Format with "0.0" still gives 0,0 as the string, it nationalizes that.

Any thoughts on how i can do this? I could probably use two statements in
this specific example, but i have many other examples where i have an insert
with a number of columns, and that wouldn't work there.

 

[Allen Browne]

You cannot set the Format property of an Access table using DDL.

Instead, use DAO, like this:
http://allenbrowne.com/func-DAO.html#SetPropertyDAO

 

[Guest]

Mybe there is a better way, but you can always use the Replace function to
change the chr

Replace(Replace(Replace(AvgMark,".","@"),",","."),"@",",")

I hope it make sense to you,
I replaced the . with @
Then I replaced the , with .
Then I replaced the @ to ,

 

[RoyVidar]

I have a need to do DDE to do a number of Insert and Update
statements (via SQL) with a program that's provided to multiple
countries, some of whom use different number formats than I do
(english version, USA).

Specifically, they would use 1.000,05 for $1,000.05...

When i do DDL with an actual comma in it, this messes the SQL syntax
up...

What i've been TRYING to do is use a format(number,format) statement,
like this:
Code:
--------------------------------------------------------------------------------

UpdateSQL = "UPDATE SalesInfo SET SalesRating = " & format(AvgMark,
"0.0") & ", NumSales= " & NumSales & _ "
WHERE SalesID= " & rstSalesResults!fk_Sales_ID
--------------------------------------------------------------------------------

Actually this doesn't even work.

Format with "0.0" still gives 0,0 as the string, it nationalizes
that.

Any thoughts on how i can do this? I could probably use two
statements in this specific example, but i have many other examples
where i have an insert with a number of columns, and that wouldn't
work there.

I'd either go with Ofer Chohens suggestion

....SalesRating = " & Replace(CStr(AvgMark), ",", ".") & ...

or use methods where one could utilize the parameters collection of the
library one is using, or something similar.

Here's a little sample in the "something similar" category. ADO is a
bit frowned upon in these groups, but if you saved your query like this

PARAMETERS prm1 Float, prm2 Int, prm3 Int;
UPDATE SalesInfo
SET SalesRating = prm1, NumSales= prm2
WHERE SalesID = prm3

with the name MyQuery, then using an ADO connection, one could do

dim cn as adodb.connection
set cn = currentproject.connection
cn.MyQuery AvgMark, NumSales, rstSalesResults!fk_Sales_ID

(I think Access will coerce float to IEEEDouble when saving the query)

The beauty of this (or utilizing the parameters collection), is that
what one is doing, is passing values between "objects" of the same
datatype. Which means that no conversion is needed, and there (should)
be no internationalization issues.