Removing a suffixes from a table field before checking for duplications

[DamnItJanet]

I have to warn you I'm a newbie so I'll probable write some dumb stuff. Hopefully this forum can make me less dumb.

I have a table with a field for a part number. The part numbers are 6 characters with some having an additional character at the end to indicate that the part is a tooling part. For example:
125467
125468
125469S
125470
125470S



The field is set to no duplicates but when a part number has the extra suffix it does detect that the "number" portion of the part numbers has been duplicated. Our goal is to make sure that the first 6 characters are not duplicated so we can prevent entry of 125470 and a 125470S as shown above.

Is there a way to use only the first 6 characters to test for duplicates in the table or do I need to somehow incorporate a query to interact with the table to prevent the entry of duplicates (with the suffix stripped off or ignored).

I think we are entering new part numbers directly into the table but I not 100% sure.

Any help is appreciated. Thanks.

[CJ_London]

but when a part number has the extra suffix it does detect that the "number" portion

presume you mean 'does not'

you would be better to split this into two fields, the numeric part and the suffix part. Generally much easier to manage.

alternatively if the text is always at the end you can use validation - something like

val(partnum)<>val("125470S")

or perhaps

dcount("*","myTable","val(partnum)=125470") which will return 0 if no match found

the val in this case is 'value' not 'validation' and returns the numeric value before any text.

However you then go on to say

Our goal is to make sure that the first 6 characters are not duplicated so we can prevent entry of 125470 and a 125470S as shown above.

Which implies the first 6 characters may not be numeric - in which case you would need to use the left function