Euro symbol in queries

[Col]

I have an Access front end to a SQL Server 2005 database using ODBC linked
tables. There appears to be a problem when using the euro symbol in queries.

SELECT *
FROM tbl_Datatable
WHERE (tbl_Datatable.strPRICE)='?40'; (the ? should be a euro symbol
Alt+0128. it appears that even just posting causes the same problem)

This query returns nothing although there are many entries with a strPRICE
of '?40'


SELECT *
FROM tbl_Datatable
WHERE (tbl_Datatable.strPRICE)='$40';

This query returns all with a strPRICE of '$40'


How do I use the symbol in queries?

Regards

Colin

 

[Aaron Bertrand [SQL Server MVP]]

Why don't you just store the number in SQL Server? Then you don't have to
worry about translating a euro character between database engines and ODBC
etc.

 

[David Portas]

I have an Access front end to a SQL Server 2005 database using ODBC linked
tables. There appears to be a problem when using the euro symbol in
queries.

SELECT *
FROM tbl_Datatable
WHERE (tbl_Datatable.strPRICE)='?40'; (the ? should be a euro symbol
Alt+0128. it appears that even just posting causes the same problem)

This query returns nothing although there are many entries with a strPRICE
of '?40'


SELECT *
FROM tbl_Datatable
WHERE (tbl_Datatable.strPRICE)='$40';

This query returns all with a strPRICE of '$40'


How do I use the symbol in queries?

Regards

Colin

You mean you store the price as a string?! Why? I'd say that entering a euro
symbol will be the least of your problems.

 

[Sylvain Lafontaine]

You have a code page translation problem because the code page on the server
is not the same as the code page (either OEM or Window) used by your client.

If you are using a passthrough query, try adding the prefix N before the
string constant:

SELECT *
FROM tbl_Datatable
WHERE (tbl_Datatable.strPRICE)=N'€40';

If this doesn't work, then replace the ANSI type of the field with an
Unicode type: instead of using the types char, varchar and text; use the
types nchar, nvarchar and ntext. Notice that even with these Unicode types,
you must still add the N prefix before all string constants for all your
passthrough queries (but not for you JET queries).

It's possible to have the euro symbol without using Unicode; however, it
will be a moving target.

 

[--CELKO--]

You are not writing SQL at all; you are faking COBOL mixed with 1960's
BASIC.


1) Do not put prefixes to tell the compiler what the physical storage
structure is. We only have tables in SQL anyway!

2) Do not put prefixes to tell the compiler what the physical storage
data type is. BASIC had to use $ for strings because of a one-pass
interpreter in the old days; this is SQL.

3) you need a better data element name, such as "foobar_price" that
follows ISO-11179 rules.

4) Prices are numeric data, and require numeric data types. Look up
the rules for Euros (triangulation, required decimal places, etc.);
what you have is going to give illegal results.

5) Why are you formatting data in the back end? The basic principle
of a tiered architecture is that display is done in the front end and
never in the back end. This is a more basic programming principle
than just SQL and RDBMS.

 

[Col]

Thanks Sylvain,

The strPRICE field may contain many prices separated by commas. This is
defined in SQL Server as nvarchar(125)
I'm trying to run the query in an Access front end so the =N'?40' does not
work.
I'll look into the code page issue

Regards

Colin

 

[Sylvain Lafontaine]

If the field is defined as nvarchar on the SQL-Server then I'm very, very
surprised that you have any problem with the euro symbol. By using Unicode,
you shouldn't have any problem. It's possible that the date are mixed up
on the table and that the euro symbol is not really represented by the euro
symbol in the table.

Create a passthrough query and verify that the unicode value for the euro
character is 8364 and not something else:

SELECT unicode (left (NameOfYourField, 1)) from NameOfYourTable.

What ODBC driver are you using and what kind of collation have been defined
for the database/table/column? Also, is the "Perform Translation for
character data" correctly checked in the DSN options?

 

[Col]

Hi Sylvain,

The query returns the correct character. MSAccess shows the correct
character in table view, but when used in a query, converts it to a '?'

SELECT unicode (left (strPRICE, 1)), left (strPRICE, 1) from tbl_Datatable

8364 ?
8364 ?
8364 ?
8364 ?

Regards

Colin

 

[Sylvain Lafontaine]

I'm still surprised that you have any problem and I cannot reproduce this
problem on my machine. The fact that it display correctly in the table view
indicate that there is no problem on the SQL-Server side.

Maybe the current font used in Access on your machine for displaying the
result of this query is not capable of displaying the euro symbol. How are
you using this query exactly and what version of Access and Windows are you
using?

 

[Col]

Hi Sylvain,

I'm trying to get a recordset from the query built with VB

If Left(strPRODCOST, 3) = "US$" Then
strCUR = "US$"
~....~
ElseIf Left(strPRODCOST, 1) = "?" Then
strCUR = "?"
Else
strCUR = "£"
End If

strSQL = "SELECT tbl_Datatable.strPRICE"
strSQL = strSQL & " FROM tbl_Datatable"
strSQL = strSQL & " WHERE LEFT(tbl_Datatable.strPRICE," & Len(strCUR) &
") = '" & strCUR & "'"

Set ds = DB.OpenRecordset(strSQL, dbOpenDynaset)
If Not (ds.BOF And ds.EOF) Then
ds.MoveFirst
etc...

The recordset works fine for other currency symbols but is always empty for
Euro.

I am using MSAccess XP on Windows XP and SQL Server 2005

Regards

Colin

 

[Sylvain Lafontaine]

I would suppose that strProdcost and "?" don't contain the Euro symbol.
Replace your strCur with the exact representation of the Euro symbol and
display the strSQL in a MsgBox to make sure that it is OK. Use the AscW()
function to make sure that the result is OK and repeat the test by replacing
the value of the Euro symbol with ChrW(8364):

Dim db As Database
Set db = CurrentDb

Dim strsql
strsql = "SELECT tbl_Datatable.strPRICE from tbl_Datatable"

Dim ds As DAO.Recordset
Set ds = db.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)

While Not ds.EOF
MsgBox ds("strPRICE ") & ": " & AscW(Left(ds("strPRICE "), 1))
ds.MoveNext
Wend


Repeat with:

strsql = "SELECT tbl_Datatable.strPRICE from tbl_Datatable where left
(s, 1) = '€'"

(Put the Euro symbol here between the two single quotes ' )

And:

strsql = "SELECT tbl_Datatable.strPRICE from tbl_Datatable where left
(s, 1) = '" & ChrW(8364) & "'"


Like with the passthrough query, AscW should give 8364. Finally, I don't
understand that you didn't need the option dbSeeChanges on a linked
SQL-Server table with a primary key. Is there a primary key on this table?

 

[Sylvain Lafontaine]

Of course, don't forget to apply AscW() to strProdCost in order to verify
it, too.