Preventing duplicate entery including spaces and case sensitivity - ms access

[Ihk]

Want to prevent duplicate entery including extra/less spaces.
with follwoing code, duplicate entery is prevented including case sensitity.
I am wondering if it is possible to prevent with extra or less space. For example: Test1 or Test 1
1st one has no space, 2nd one has space. is it also possible to prevent this, if user manupulate a word while data entery.
My current code is

If Not IsNull(DLookup("[Catalogue]", "ArticleDetails", "[Catalogue] = '" & Me.txtCatalogue.Value & "'")) Then
Cancel = True
Msgbox "msg for duplicate"
Else
End if

Thanks

 

[Gasman]

Remove all spaces with Replace() and then compare.

 

[arnelgp]

use DCount instead of DLookup, it will return 0 if not found, or a Number if Found:

If DCount("1", "ArticleDetails", "Replace([Catalogue], '  ','') = '" & Replace$(Me.txtCatalogue.Value," ","") & "'") <> 0 Then
Cancel = True
Msgbox "msg for duplicate"
Else
End if

 

[Ihk]

The question is data is already in database.
Catalogue number from different companies is in different format, some with spaces some without, some capital, some not, some with special characters etc etc.
I dont have to change that, but when users are also allowed to add new catalogue if wont exist.
In that case with "Not IsNull dlook up", it does not allow users to add duplicate, also case sensitive, still we noticed some of the users Add some space or reduce some space and add the record.
Can this be prevented?

 

[moke123]

What does the data look like?

 

[The_Doc_Man]

Your problem is that you have already allowed the possibility of oddball spacing based on the already-entered numbers from different companies. Did you limit the number of spaces in those entries? If you didn't, then I would have to say "Not easily if at all."

If you did a sweep of your DB to adjust the oddball cases first, you could then reasonably do it a simple way. I would remove all doubled spaces and all leading and trailing spaces from the extant entries. Then you could apply the TRIM() and REPLACE() functions to clean up the new input before checking for duplicates. As for the case sensitivity, this can be done with the OPTION COMPARE setting that can be set to BINARY, which means case matters. There might be other things you could do as well, but they would depend on how Draconian you wanted to be. The "Test1" vs "Test 1" case would require a strict rule about "no interior spaces at all" and I gather that you don't think you can make that rule.

 

[moke123]

What about doing a comparison to existing data and present the user with a list of similiar entries to either select or ignore?
Perhaps using levenschtein distance?

 

[MajP]

Something like this might work

Removing duplicate text from random records

I've got a table where some of the cells in one column have nearly identical information entered twice. Here are some examples: I need a query to go through the whole table, catalog what data is in the cell, and delete duplicate text. So in my examples, the extra 11/16 49 97, 11/16 61 97...

www.access-programmers.co.uk

or

Solved - Search all words in field against all words in another field

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type) Like?? Like won't find similarities in strings with...

www.access-programmers.co.uk

or

Matching logic

Probably not difficult but I would advise against it. In case where matches aren't exact, I would want someone to check the results rather than trust any algorithm I have a system for checking a batch of online payments by parents against the reference number for their child so it can be...

www.access-programmers.co.uk

 

[GPGeorge]

Sometimes the final step in a Data Cleanup Process requires judicious use of HE (Human Eyeballs).

 

[jdraw]

I agree with George. We used to call it IEH-IBall for those ex-IBMers out there.

 

[Ihk]

Sorry I was travelling and could not get in.
I am very thankful for all of you for your helpful suggestions.

 

[Ihk]

What does the data look like?

Data normally looks like following from different companies.
98876
4001/002
SC-464
M12850
20350-1-AP
orb6272
16350.00250

 

[Ihk]

use DCount instead of DLookup, it will return 0 if not found, or a Number if Found:

If DCount("1", "ArticleDetails", "Replace([Catalogue], '  ','') = '" & Replace$(Me.txtCatalogue.Value," ","") & "'") <> 0 Then
Cancel = True
Msgbox "msg for duplicate"
Else
End if

Thank you very much. I tried this, this worked when put spaces in between and tried to enter data, it was not possible.
But this was working only for pure numbers like 1234 or 123 4 , either of these is considered as duplicate. But this does not detect any type of text or text+numerical as duplicate.

 

[Ihk]

I must have to say here, my current data is correct. There is no problem, all correction have been taken place side by side. Now the question was about next, not allow user to enter duplicate values.
Still I was able to manage this by "Not IsNull(DLookup...." above mentioned. This is case sensetive and prompts users for duplicate entery, this also stops leading and trailing spaces.
But it does not prompt when there is any space in between.
I tried above mentioned at post 3 by @arnelgp ,

use DCount instead of DLookup,

This stops duplicate entery (including between spaces) but only for numerical entery.

 

[The_Doc_Man]

The samples you showed us do not appear to contain embedded spaces. Can't tell if they have leading/trailing spaces. However, if you TRIM$ the input before testing for duplicates, that should match correctly most of the time. The only time you get skunked is if the stored data has leading or trailing spaces.