query fails from access, works in mySQL-front 2

[andytodd]

Our database is mySQL, but has an MS Access (2000 at present) interface.

We have a script which imports a comma delimited file and updates various tables in our db. As the data is supplied from Personnel (Human Resources) it is frequently misspelt, or prone to format change without warning.

Our biggest headache is data on which department staff are paid by. We have to provide training information to various parties who want to know which departments etc have taken up training and which haven't.

To enable this to be administered, the script compares the Personnel spelling / wording for the department to a look up table, spitting out where it can't match things so we can teach the look up table. So far so good.

A recent change to a department title has me stumped. Both the csv and the lookup table contain "Mechanical & Manufacturing Eng", but Access fails to match the two either in script, or in a saved query. MySQLFront does match them up though. I thought it was the ampersand, but it can cope with "COMMUNICATIONS & PUBLICITY"

Any ideas?

If it is any help, SQL is below

Dim myDb As Database
Dim myQRY As QueryDef

Set myDb = CurrentDb()
Set myQRY = myDb.CreateQueryDef("&quot

With myQRY

.SQL = " UPDATE DATAFILE LEFT JOIN tbl_paypoint ON DATAFILE.Section = tbl_paypoint.Paypoint SET DATAFILE.sectionID = tbl_paypoint.Sectionid "
.Execute

 

[bboffin]

Have you checked to see if there are any trailing spaces in the data?

Try:
... ON TRIM(DATAFILE.Section) = TRIM(tbl_paypoint.Paypoint)

 

[andytodd]

Hi bboffin,

I had looked, and even keyed through the data in various interfaces, but still no sign of any spaces.

Your suggestion to use TRIM in the SQL statement (I didn't know you could do that) has fixed the problem.

Thank you.

 

[bboffin]

You can use pretty much any function anywhere in a SQL statement as long as it makes sense. The SQL parser will barf if it doesn't like it anyway