[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

function to recognize string patterns and insert when needed

ACCESS 2003 VBA

What I have:
table:
1 field  =  myDescriptions - memo


What I need:

function to recognize string patterns and insert when needed.


BEFORE:
3.5oz hammer
garden rake 13 inch
1/4-20 x 1    HHCS
1/4-20 X 1-1/2 HHCS
1/4-20 X 2 INCH
5/16-18 X 3/4
1/2-16 X 1 INCH SHCS
1/2-16 X 1-1/2
1/2-16 X 2
SOCKET SCREW 1/2-20 X 1
SOCKET WRENCH 1/2 COMBO


AFTER:
3.5oz hammer
garden rake 13 inch
1/4-20 x 1    HHCS
1/4-20 X 1-1/2 HHCS
1/4-20 X 2 INCH HHCS   <---- ADDED
5/16-18 X 3/4 HHCS <----  ADDED
1/2-16 X 1 INCH SHCS
1/2-16 X 1-1/2    <------ ADDED
1/2-16 X 2      <------ ADDED
SOCKET SCREW 1/2-20 X 1
SOCKET WRENCH 1/2 COMBO


The function has to sort of figure out if the string is needed above or below as needed



Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 3
1 Solution
 
Patrick MatthewsCommented:
Hello fordraiders,

UPDATE YourTable
SET Column = Column & " HHCS"
WHERE InStr(1, Column, " x ") > 1 And Right(Column, 4) <> "SHCS" And Right(Column, 4) <> "HHCS"

Regards,

Patrick
0
 
FordraidersAuthor Commented:
sorry after should be:
AFTER:
3.5oz hammer
garden rake 13 inch
1/4-20 x 1    HHCS
1/4-20 X 1-1/2 HHCS
1/4-20 X 2 INCH HHCS   <---- ADDED
5/16-18 X 3/4 HHCS <----  ADDED
1/2-16 X 1 INCH SHCS
1/2-16 X 1-1/2    SHCS  <------ ADDED
1/2-16 X 2      SHCS  <------ ADDED
SOCKET SCREW 1/2-20 X 1
SOCKET WRENCH 1/2 COMBO

0
 
peter57rCommented:
So how do you determine whether it should be HHCS or SHCS or nothing?

0
 
FordraidersAuthor Commented:
I don't know that is what I need  a function to determine?

my guess is :
first examine each record to see if only numerics exist : exception  is the  "X"

1.   1/2-16 X 1  HHCS
2.   1/4-20 x 1         <---  Just numerics and an "X"  HERE  .  no alpha term here
3.   1/4-20 X 1-1/2      <--- no alpha term here either
4.   1/4-20 X 2 INCH
5.   5/16-18 X 3/4  
6.   1/2-16 X 1 INCH SHCS
7.   1/2-16 X 1-1/2    SHCS  <------ ADDED
8.   1/2-16 X 2      SHCS  <------  added


Once we see there is no Alpha term in the next record(2) . look in the record before(1) does an alpha term exist.
yes  it is  "HHCS"
LOOK AT THE NEXT (3) does an alpha term exist.   "NO"  so ther missiing term must be  "HHCS"

Place an "HHCS"  after... 1/4-20 X 1-1/2

so go to record 4
does an lapha term exist.  it contains "INCH" , but like  "X" thi is an exception term..

so record 4 gets
4.   1/4-20 X 2 INCH  HHCS
record 5 gets
5.   5/16-18 X 3/4  HHCS

when e get to record 6
we now have a alpha term  "SHCS"

so HHCS gets thrown out (of memory sort to speak)

so we start the examination all over again...

6.   1/2-16 X 1 INCH SHCS
7.   1/2-16 X 1-1/2    SHCS  <------ ADDED
8.   1/2-16 X 2      SHCS  <------  ADDED

Thanks for looking

fordraiders





0
 
FordraidersAuthor Commented:
ok , lets say we have a table of terms to search for...

tblTerms
fldTerms

Data in table is:
HHCS
SHCS
FHCS
SOCKET SET




0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now