Hello,

With my dealers database i got the problem that when searching for a name example: Montreal in the database it could be defined as Montréal. But i don't know how to make sure e is equal to é ?

Any suggestions are welcome, tx.


Dave Joosten

You may want yo try the LIKE verb in your SQL statement. Worked in a small test on SQL7.0

SELECT *
FROM dealer_data
WHERE city LIKE 'Montreal'

The LIKE statement has nothing todo with this issue, even tough it might work for one, i got more then one word with this problem. In general anybody that handles a websites in French will encouter it so was wondering what they do about it. Thanks though for your input, it would work if i had only one city.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
You may want yo try the LIKE verb in your SQL statement. Worked in a small test on SQL7.0

SELECT *
FROM dealer_data
WHERE city LIKE 'Montreal'


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Dave Joosten


Edited by - djoosten on 06/12/2001 16:51:26

The LIKE verb works for any words in that data column, so it does not matter how many cities there are.

SELECT *
FROM dealer_data
WHERE city_data LIKE %whatever is chosen by the user%

So if I search for Vancoover the search will return all locations in Vancouver, if I search for Edminton the search will return all locations in Edmonton, etc. The LIKE statement will allow me to use just a few letters to do the search.

Now, if you want to change from one to the other you could write a trigger and a/or stored procedure. When the data is updated the trigger is fired (or the stored procedure is called)...

CREATE TRIGGER tr_FixCitySpelling
ON dbo.dealer_data
FOR INSERT
AS
INSERT INTO dbo.dealer_data
(city_data)
SELECT Montréal
FROM inserted

Of course this would not be the complete listing for this trigger, just an example.

You could use a drop down box on the choice of Cities, that way you can lock in the values.

Great m8te, that actually makes a lot of sence. I'm gonna talk to my database programmer and try it out ! Ill keep you updated if we got it fixed. Dropdown would not be a wise choise since i got around 6000 different places to start with, so that dropdown would be at least 3000 long lol Anyways, thanks for the good idea !<img src=icon_smile_wink.gif border=0 align=middle>

Dave Joosten

Cool, let me know how it turns out.

On the drop down, you could generate the list dynamically....but I sure would hate to scroll through 3000 selections!!! <img src=icon_smile_cool.gif border=0 align=middle>

Forgot to say, but the SQL works a bit different, not you like you thought it would. If you use the %whatever% this will only trigger that when searching for example montr it will search anything higher then the selected search, but it will not show montereal, it will show montreal, montreel etc etc. Just wanted to add this for the record if anybody would read it, this is what it really will do.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
The LIKE verb works for any words in that data column, so it does not matter how many cities there are.

SELECT *
FROM dealer_data
WHERE city_data LIKE %whatever is chosen by the user%

So if I search for Vancoover the search will return all locations in Vancouver, if I search for Edminton the search will return all locations in Edmonton, etc. The LIKE statement will allow me to use just a few letters to do the search.

Now, if you want to change from one to the other you could write a trigger and a/or stored procedure. When the data is updated the trigger is fired (or the stored procedure is called)...

CREATE TRIGGER tr_FixCitySpelling
ON dbo.dealer_data
FOR INSERT
AS
INSERT INTO dbo.dealer_data
(city_data)
SELECT Montréal
FROM inserted

Of course this would not be the complete listing for this trigger, just an example.

You could use a drop down box on the choice of Cities, that way you can lock in the values.



<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Dave Joosten

Which SQL are you using?

This select statement in SQL7.0;
<b>
SELECT *
FROM city_search
WHERE city_name LIKE '%montr%'
</b>
returned the following;
<b>
city_name
--------------------------------------------------
montreal
Montreal
Montréal
montreel

(4 row(s) affected)
</b>

Using Access 2000 (i prefer SQL server but heck no choice right now) for this, yes what you write is correct, but in that previous message you quoted that when searching for %vancoover% it would return vancouver as well, what isn't true. So think of this when someone searches for %montreal% he will still not get montréal as a result right ?
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
This select statement in SQL7.0;
<b>
SELECT *
FROM city_search
WHERE city_name LIKE '%montr%'
</b>
returned the following;
<b>
city_name
--------------------------------------------------
montreal
Montreal
Montréal
montreel

(4 row(s) affected)
</b>


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Dave Joosten

I just spoke with our Access guru (I am a MSSQL / Oracle kind of guy) and he said that what you want to do is impossible in Access for a number of reasons.

1. No Stored Procedures can be written.
2. No Triggers can be written.

I do see your point where the return might not work as expected, but there are a couple of ways around this if you can use MSSQL or Oracles db's, like the full text search in MSSQL.

Sorry I couldn't be of more help. <img src=icon_smile_sad.gif border=0 align=middle>

Thanks for your research and time, i appriciate it ! Don't worry to much about anymore cause i'm simply not gonna allow them to use any é anymore ! heh makes live easy for me again ..sighh yeah Access has so many restrictions that i noticed, anyways not to worry i'm intending to get my hands on SQL Server ..again, thanks m<img src=icon_smile_8ball.gif border=0 align=middle>te !

Dave Joosten


Edited by - djoosten on 06/12/2001 19:24:41