Update SQL acts funny

[Guest]

Hi all.

I'm using an UPDATE SQL statement to update values in a Table.
The updated Fields all have the Currency (EUR) format, with two decimals.

The problem:
When the new value is a round number (eg 35) the SQL works fine.
If the new value has a decimal part (eg 35,2) the SQL fails.

Any thoughts please?

Thanks,
Alex

 

[Guest]

Hi all.

I'm using an UPDATE SQL statement to update values in a Table.
The updated Fields all have the Currency (EUR) format, with two decimals.

The problem:
When the new value is a round number (eg 35) the SQL works fine.
If the new value has a decimal part (eg 35,2) the SQL fails.

Any thoughts please?

Thanks,
Alex

Alex,

format only affects the way the data is displayed. It sounds like the data
type of the field in the table is Integer or Long Integer (the default).

Open the table in design vies and verify that the Data Type of the field is
Single or Double (or Decimal in A2K or later)

Select the field in the upper pane; in the "General Tab" at the bottom of
the window, look at the line "Field Size". If it is "Integer" or "Long", you
can only store whole numbers.

HTH

 

[Guest]

Steve thanks for the reply.

I corrected the NumberFormats in the underlying Table (had set the DataType
to Currency and the Format to Euro), but the problem persisted, even though i
played around with different combinations.

The problem seems to be that i'm working on a pc with European settings, and
the SQL i'm trying to run (when decimals are involved) looks like:
"UPDATE ... SET ... = 7,45" ,
whereas if you create a Query and check the SQL the syntax is:
"UPDATE ... SET ... = 7.45"

Any advice on how to make a variable that has a value of 7,45 (as it's
running on a European machine) look like 7.45 for the SQL expression?

Thanks again,
Alex

 

[Guest]

Steve thanks for the reply.

I corrected the NumberFormats in the underlying Table (had set the DataType
to Currency and the Format to Euro), but the problem persisted, even though i
played around with different combinations.

The problem seems to be that i'm working on a pc with European settings, and
the SQL i'm trying to run (when decimals are involved) looks like:
"UPDATE ... SET ... = 7,45" ,
whereas if you create a Query and check the SQL the syntax is:
"UPDATE ... SET ... = 7.45"

Any advice on how to make a variable that has a value of 7,45 (as it's
running on a European machine) look like 7.45 for the SQL expression?

Thanks again,
Alex

Alex,

From posts I've read by the MPVs, I would try using the FORMAT() function.
It works when converting European dates to US format for SQL.

Try something like this (AIR CODE):

UPDATE tblPayCals SET tblPayCals.payRate =
Format([payrate]*1.1,"###,##0.00") WHERE (((tblPayCals.dteFrom) Is Not Null));


HTH

 

[Guest]

Steve thanks for the tip, i'll give this also a shot.

I worked out a solution by converting the numeric argument to text (CStr)
and then using the Replace function to turn those dreaded commas into US- and
SQL-compliant dots

Cheers,
Alex

Steve thanks for the reply.

I corrected the NumberFormats in the underlying Table (had set the DataType
to Currency and the Format to Euro), but the problem persisted, even though i
played around with different combinations.

The problem seems to be that i'm working on a pc with European settings, and
the SQL i'm trying to run (when decimals are involved) looks like:
"UPDATE ... SET ... = 7,45" ,
whereas if you create a Query and check the SQL the syntax is:
"UPDATE ... SET ... = 7.45"

Any advice on how to make a variable that has a value of 7,45 (as it's
running on a European machine) look like 7.45 for the SQL expression?

Thanks again,
Alex

Alex,

From posts I've read by the MPVs, I would try using the FORMAT() function.
It works when converting European dates to US format for SQL.

Try something like this (AIR CODE):

UPDATE tblPayCals SET tblPayCals.payRate =
Format([payrate]*1.1,"###,##0.00") WHERE (((tblPayCals.dteFrom) Is Not Null));


HTH