Write a Query to detect a 1 character difference

  • Thread starter Thread starter James A
  • Start date Start date
J

James A

Every month the company gets a old price list the overwrites the new
one.
(SiemS5100 table)

The company still has some old items in stock. (StockLevels table)

When a new purchase order is created the user enter in a part number,
eg.
3RK1 322-0HS02-0AA0


I want to be able to scan the stock table incase there is an old item
available in stock and if so prompt the user to use this one.

The difference between an old item & new is only ever one character,
for an old item of that same type as the item listed about has this
part number:
3RK1 322-0HS03-0AA0 (new)
3RK1 322-0HS02-0AA0 (old - as per above)

The only method I've come up with is to read each character one at a
time and have some sort of variable that keeps track of the
differences but this will take too long.

[kind of like when i was trying to manipulate a string but didn't know
about the replace function :(( ]

I'm thinking there's some easy way of doing this.

Thanks for the help.
 
You gave an example, but what wasn't clear is if ANY of the 16 chars could
be increased, or only the one you noted. If ANY of the 16 can change, then
you have your hands full. If it's only ONE char, then it's not so bad.
Either way, I would use VBA to solve the problem. Use the following a s a
basis to get you started. Add additional for..next statements in case ANY
of the 16 can change.

Open a recordset with the new part numbers.
Loop through each new value and query for a possible match:

CurrentChar = Mid$(!PartNo,14,1)
NextChar = Chr$(Asc(CurrentChar)+1)
'Note: What happens if the char was Z or 9? Need to handle it.

strSQL = "Select partno from table where partno = " & _
Left$(!PartNo, 13) & NextChar & Right$(!PartNo, 5)

'Open the recordset based on the sql
'Check for a match
'React accordingly.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top