Finding Pattern in Field

[yessir]

I have a temp table where I import data from an excel file to.

I do some work on the data in the table, but am having trouble with this part.

The field(ClientID) in a table(tblWater_Sample_Temp) contains information like:

HZ540-EN15
HZ540-MTG

I also have a table(tblPrefix_Outlets) which contains all the prefixes such as;

EN
MTG

What I need to do is find all instances of the prefixes(from tblPrefix_Outlets) in the ClientID and take that information and compare it to entries to another table(tblLocation, Outlet) and insert the associated ID into another Field(Outlet_ID, in tblWater_Sample_Temp)

So the Outcome looks Like:

+-------------+-----------+
| ClientID | Outlet_ID |
+-------------+-----------+
| HZ540-EN15 | 3 | (EN15)
| HZ540-MTG | 5 | (MTG)
+-------------+-----------+

I will include the sample to try it on...

Thanks for any help...
~

 

[Cosmos75]

Add another field.

Suffix: Right$([ClientID],Len([ClientID])-InStr(1,[ClientID],"-"))

This will return all text after the first "-".

Hope this is what you are trying to accomplish.

 

[yessir]

While that is a great approach, and I though of trying that (though I wasn't sure how)...

BUT the problem is that it will not always follow a '-' and there may be locations like 'HZ250-EN-05' which would then only extract the 05,

as well there are ones that neglect the '-' altogether!

I understand the problem this poses to data integrity as well as programatically but I wish to take as much of hte dummy work out of this for the user (at their request)

Any further help would be greatly appreciated!

Thanks

 

[Cosmos75]

So you want to go through each record

e.g. 'HZ250-EN-05'

and return everything left of and including the prefixes from tblPrefix_Outlets, e.g. EN and MTG.

Is that what you are trying to accomplish?

How is ClientID being assigned? Manually?

It seems that you are aware that there may be some normaliztion issues and it sounds to me like ClientID should be made up of several fields, one of which would be the prefiex field from tblPrefix_Outlets.

I could probably come up with a method for doing this but perhaps it might be worthwhile to step back and see the bigger picture. What exactly are you trying to accomplish and why?

You may need to explain to me what it is you are trying to accomplish and why rather than spitting our field and table names.

Take me through it step by step.
e.g.

• find all instances of the prefixes(from tblPrefix_Outlets) in the ClientID.
• take that information (e.g. EN-15, MTG-65)
• compare it to entries to another table(tblLocation, Outlet) - ?
• insert the associated ID into another Field(Outlet_ID, in tblWater_Sample_Temp) - I don't quite get the last two yet but then I haven't spent a lot of time looking at your database yet. Maybe when I have more free time.

 

[yessir]

Sorry, lost my internet for a while...

You hit the nail almost right on the head.

• find all instances of the prefixes(from tblPrefix_Outlets.Prefix) in the tblWater_Sample_Temp.ClientID.

• take that information (e.g. EN-15, MTG-65) and compare it to entries to another table(tblLocation.Outlet)

• insert the associated tblLocation.ID where tblLocation.Outlet = EN-15 into another Field tblWater_Sample_Temp.Outlet_ID

This seems sort of a round about but it is what is needed.

I am reposting ht edb as some minor changes have been made.

The db form import is the main form and I am using the button command30 for testing this part,

please keep in mind that this is torn down CONSIDERABLY, and I am looking for the code that could do the above