Removing the blanks

[Guest]

I've two tables I want to join when a text field matches once all blanks (and
case) are ignored e.g. "A b CC d" will join with "ABCCD". What I wanted to
use was
.... Where UCASE(SUBSTITUTE(tableA.joinfield," ","") =
UCASE(SUBSTITUTE(tableB.joinfield," ","")
but the worksheet function substitute isn't recognised by SQL. Is there
another way of doing this in SQL? Or is there a VBA way of unloading the data
then reloading it so the join will work?

 

[Neil Sunderland]

I've two tables I want to join when a text field matches once all blanks (and
case) are ignored e.g. "A b CC d" will join with "ABCCD". What I wanted to
use was
... Where UCASE(SUBSTITUTE(tableA.joinfield," ","") =
UCASE(SUBSTITUTE(tableB.joinfield," ","")
but the worksheet function substitute isn't recognised by SQL. Is there
another way of doing this in SQL? Or is there a VBA way of unloading the data
then reloading it so the join will work?

Use REPLACE instead of SUBSTITUTE (the parameters are the same).

 

[Guest]

Thanks for taking an interest - just tried replace and get the same undefined
function message. Do I need to install some sort of add-on? I'm still using
Access 2000 (9.0) do I need to upgrade?

 

[Van T. Dinh]

I think in the original release of Access 2000, the Replace() function (and
some other new functions introduced in Access 2000) are not recognised by
the Expression Service used for Query processing.

All you need to do is to create a "wrapper" UDF to wrap the VBA function
Replace() and use the UDF instead of Replace.

 

[Guest]

Thanks!!!
I knew you could define functions for Excel and of course for use by forms -
never thought they'd work in SQL too.

MVP's are worth their weight in gold sometimes.

 

[Van T. Dinh]

You may also like to check the version of your Access 2000. If it is still
the original version, you should update it to at least Service Release 1a
(SR1a) which fixed a lot of bugs.

I think the up-to-date version should be Service Pack 3 (SP3). The 3 majors
updates are SR1A, SP2 and SP3 (plus lots of security updates).

Only sometimes ???