Like? ignore dashes

>00440-02-0002558 because it thinks that this number is not in my db but it really is but has a dash in it.
The only way to store a numer with dashes in it is to make it a char/varchar field, which means it does text compare, not number (everything without the dashes) compare.

i.e.  '00440-02-0002558' will never equal '00440-02-000-2558'

The 'trick' is to remove all the dashes, convert to a number, then comapre the two.

Or just do it as a text compare, trimming it to deal with any leading or trailing spaces...

WHERE ((([import-CSM2].[Reference Number]) Not In (SELECT [import-CSM2].[Reference Number]

would have to be written as

WHERE Replace(Trim([Refence Number]), "-", "")
NOT IN (  SELECT Replace(Trim([import-CSM2]), "-", "")  )

Jerry, will the function modify the data within the tables?

No, just the display. The function was written for another requirement, so the name is perhaps unfortunate.

If it does, is there a simple way to undue the edits?

I should say-It will affect the display or evaluation (as in the WHERE statement.)

replaceNoNum("123-45-67-8-9") returns the same as replaceNoNum("1234-567-89")

It does not modify anything.

I think this is the solution i will test.  I do have other extrraneous characters besides dashes and the function looks like it will handle more than 1 character.  Thank you for the expert help!

Jerry,

I pasted the function in the module and it highlights the "IF" line in red.  
What do you think is the issue?  

thanks...

Missing a closing paren:
if not(isnumeric(mid(a,i,1))) then

Jerry,

sorry but have to ask a follow up.

00440-02-0002558
00440-02-000-2558

using the function, would the above equal eachother?  Meaning that the dashes would be stripped?   If it matters, the format of the field is not number but text.  Most of the data does have some letter in it but this example is only dealing with numbers.  

thank you

pdvsa - Did you try the solution that I posted in the second comment?  Just curious.

Yes, they would. It is based on string being text. It is also based on you trying to not consider non-numeric values in the string.  Have you tried it?  
If something is not working, re-open the question for additional help, and change of scoring if need be.

OK thank you.  I have not tested yet though.

Jim:  
I did not try it only because there are other characters besides dashes.

Depending on the number of characters, and whether or not you are only looking for numeric values, the solution you accepted may not be the best one. Can you post a sanitized examples of what values you are trying to compare?

Very well.

Jerry, there can be a combination of letters and numbers.  The fields are formatted as text.   What do you think now?  Thank you

Can you post some examples which give the characters other than dashes which you want to exclude from comparison?

Jerry:

I can not call it from within and SQL statement?  Notice the bold where statement.  I put the "replaceNoNum" in that line.  If I run it, I get a "no current record" error.  I dont have sample data other than what I have stated so far.  It is premature in the process at the moment.  

SELECT [import-CSM2].[Activity Code], [import-CSM2].[Reference Number], [import-CSM2].[Activity Description], [import-CSM2].[Actual Status], [import-CSM2].[Guarantor Description], [import-CSM2].Beneficiary, [import-CSM2].[Issuing Date], [import-CSM2].Nature, [import-CSM2].Currency, [import-CSM2].[Expiry Date], [import-CSM2].LCID, [import-CSM2].ProjID
FROM [import-CSM2]
WHERE ((replaceNoNum([import-CSM2].[Reference Number]) Not In (SELECT replaceNoNum([import-CSM2].[Reference Number]) FROM [import-CSM2] INNER JOIN tblLetterOfCredit ON tblLetterOfCredit.LCNo =[import-CSM2].[Reference Number])) AND (([import-CSM2].[Actual Status])<>"GEC") AND ((replaceNoNum([import-CSM2].[Activity Code])) Not In (SELECT replaceNoNum([import-CSM2].[Activity Code])
 FROM [import-CSM2] INNER JOIN Projects ON Projects.ProjectNo like "*"&[import-CSM2].[Activity Code]&"*")))
ORDER BY [import-CSM2].[Reference Number];

but if I take out the replaceNoNum the query does execute.

OK, post an empty database, please.

OK  attached is a db.  
Look at the qry:  ReplaceNoNum

ie
00440-02-0002558
00440-02-000-2558
EE.accdb

zipped
EE.zip

I am an idiot.

Function should read:
Function replaceNoNum(a As String) As String

Dim i As Integer
Dim s As String
    For i = 1 To Len(a)
        If IsNumeric(Mid(a, i, 1)) Then '<
        s = s & Mid(a, i, 1)
        End If
    Next i
replaceNoNum = s
End Function
However, we still need to deal with the alpha characters. And null values.

Are these the ref numbers you expect to see?
Reference Number
ONSHORE 02
LSM0198348
CSM 177
31888
7101
00450-02-0001681
00440-02-0002558
00440-02-0002567
00450-02-0001707

Hi Jerry,

thank you for the follow up.

<Are these the ref numbers you expect to see?
yes, there would be spaces (I guess that is null)

will be out of town for a few days but might be checking emails..

thank you

I can get the matching numbers, but cannot figure why the NOT IN sub-select isn't working.

Hi Jerry, do you suggest I use one of the other solutions from the other guys?    thank you

I would try it out, until you find a situation in which jimhorn's replace solution does not work.  It might be a case of adding one or two more characters to the replace function. ('Replace' only in terms of how the two ref numbers are evaluated, not actually changing any values.)
And, re-open question to adjust scoring as required.

Hi jim or Jerry

I do think i should add a handler for spaces in ADdition to dashes.  

Could Jim'suggestion be easily modified to allow for this?  Thank you.

Yes.
Replace(replace(table.FieldName, "-",""), " ", "")
is the syntax.
At this late hour, I am loathe to try to build the actual SQL statement. Tomorrow.

Hi Jerry, sorry to pester you.  Not sure if anyone is still following this thread.  

I was wondering if there have been any developments with issue we had in this question.  In the above, there was an issue with the NOT IN sub not working.

5 posts above: "I can get the matching numbers, but cannot figure why the NOT IN sub-select isn't working."

Thank you.

I'll re-look. It fell below the radar since Feb.

Ok Jerry...much appreciated.  Have a good day...

pdvsa:
I went back to db you posted, and fixed a couple of things. Unfortunately, my computer with right version of access just crashed as I was saving possible solution, so you'll have to work with me.

In your function replacenonum, the logic would have created a string of non-numeric values. Another idiot thing on my part.

Should be:
if isnumeric(mid(a,i,1)) then

Don't know what I was thinking.

Then, we come to checking for nulls in [Reference Number] and LCNo, which we did not do before.
Rather than work with the NOT IN criteria, I created a query (Call it qryNoMatch) with SQL of
SELECT replaceNoNum([import-CSM2].[Reference Number]) FROM [import-CSM2] INNER JOIN tblLetterOfCredit ON replacenonum(nz(tblLetterOfCredit.LCNo, "")) =replacenonum(nz([import-CSM2].[Reference Number],""))

Then, you can use NOT IN(Select [Reference Number] from qryNoMatch) as your criteria in your original query.

To speed things up, I used a make table query to get matching [Reference Number] to LCNo.

You would probably want to run code to run the 2 queries (qryMakeMatch and replacenonum3 in attached db.)

I removed extraneous tables/queries.
EErelookNew.accdb

Hi Jerry,

ok so to fix the code:
only make that correction?  
  if isnumeric(mid(a,i,1)) then
thanks

Well, it fixes most of the matching problem.
Doing the separate query as suggested should give proper results. Doing the make-table query is faster, once it is run.

ok thank you