Problem with join in mySQL 1

[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("")
      
      With myQRY
  
      .SQL = " UPDATE DATAFILE LEFT JOIN tbl_paypoint ON DATAFILE.Section = tbl_paypoint.Paypoint SET DATAFILE.sectionID = tbl_paypoint.Sectionid "
      .Execute

 

[BobStubbs]

Could this be caused by a leading or trailing space? These can be very difficult to see when you inspect data visually, but might cause Access to fail to match data items.

You could try checking the length of this data with the Len() function, to see if the items being compared are the same length.

I hope this helps.

Bob Stubbs

 

[andytodd]

Hi Bob,

Thanks for the tip. As you say, hard to see a trailing space, especially as Access doesn't always allow you to key over them.

Combining this with a suggestion from bboffin to use TRIM in the SQL statement (I didn't know you could do that) has fixed the problem.

Thank you.

 

[BobStubbs]

I didn't know TRIM worked in SQL either ... could you post an example of the syntax?

... something new to learn every day!

PS Many thanks for the star!

Bob Stubbs