Mid Function

[ClowneUser]

Hi,

We are using access 2000. we use a product code of A1234 -A, A1234 -B, B1234 etc... In a query we use the mid function to bring back all part numbers no matter what prefix it has, so if we query for 1234 it will bring back all parts.. the query is

Part: Mid([tblPART].[strPartNo],1,5)

This worked really well, until we started using new part numbers for a new line we are producing, so now the new parts are AM1234, AM1234 -A etc....

Is there any way to change the query to be able to pick up both Old and New parts?

Any help would be appreciated.

Regards,
Mick.

 

[MichaelRed]

depends -A LOT- on the consistiency of you schema. If the "prefixes" are all alpha and the part numbers are all numeric, just parse the field for the numerics or the not alphas.



MichaelRed

 

[SkipVought]

It is not a best or accepted practice to use embedded data. If Products and Parts are different animals, then EACH ought to be stored separately.

These are not the days of dear storage & memory. That was the 60's & 70's. This is the twentyfirst century.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[missinglinq]

These are not the days of dear storage & memory. That was the 60's & 70's. This is the twentyfirst century."

I express the same sentiments as Skip at least once a day, only adding processor speed to storage & memory to the mix. So many comments I read about this procedure or that function being slow simply aren't true, anymore, and have to date back to the thinking of a previous place and time!

Having said that, here's a VBA hack for parsing out the digits in a Text Field. Perhaps you can use the code as the basis for a function that you can then call from your query:

Dim strNum As Long
Dim i As Long

On Error Resume Next

For i = 1 To Len(PartNum)
 If IsNumeric(Mid(PartNum, i, 1)) Then strNum = strNum & Mid(PartNum, i, 1)
Next i

ExtNum = CLng(strNum)

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[MajP]

If it was me I would combine the recommendations. Have a part number field and a product code field. Use the function in an update query to populate the "part number field", to update all current records. Then modify your input so that when you add a new record you store both.

Because if not, some day in the future they will change the rules again on product codes. And
A9M1234, A9M1234 -A
will be the same part "1234"
Or
AM1234, AM1234 -A1, AM1234 -A2
will be the same part.

 

[SkipVought]

BTW, whose idea was it to, "use a product code of A1234 -A, A1234 -B, B1234 etc... use the mid function to bring back all part numbers no matter what prefix it has?"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[missinglinq]

Come on, Skip, you know whose idea it was! The SUITS! Anytime a project requirement comes along, that makes as much sense as those 'Drive-Up Only' ATM machines with the user instructions in braille, you know the idea came from a SUIT!

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[SkipVought]

Just wanted to hear it from the horses mouth. If the 'horse' has horse sense, then perhaps the 'suit' might get a copy of this thread on his desk and have a prayer of avoiding future disaster.

But I dream too much.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Oops

Just wanted to hear it from the horse's mouth. If the 'horse' has horse sense, then perhaps the 'suit' might get a copy of this thread on his desk and have a prayer of avoiding future disaster.

But I dream too much.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}