find string as a part of larger string

[SDaccess24]

I am trying to find a string as part of a larger String. Is there any Access formula I can use?

Eg. Find..

ABCDEF in [ ASADFAG, ASDHJFSH, FHEWFH, ABCDEF, JKSDGFS]

There is no set count of characters that I can use a mid, left, right functions.

Any help is much Appreciated!

 

[MarkK]

Welcome to the forum. Check out the InStr() and Split() functions.

 

[SDaccess24]

Can you pleas help me with an example? I tried


InStr([Wells]![Invoice ID],[PeopleSoft]![Invoice])

[Wells]![Invoice ID],[--> Longer String
[PeopleSoft]![Invoice]) --> Shorter String that is part of the string mentioned above.

No Luck :-(

 

[MarkK]

Your best resource for functions like this are VBA help. Open a code window, click help, and use the search function in the help viewer. A nice alternative to this is find the function in the Object Browser, and right click it, which also offers a quick link to VBA help.

In VBA help you'll get a very thorough technical description of the method and its parameters.

Cheers,

 

[gemma-the-husky]

instr returns the location of the start of the match

instr is normally non case-sensitive so

pos = instr("The cat in the hat","CAT") 'sets pos to 5.
pos = instr("The cat in the hat","CATCH") 'sets pos to zero

there is also instrrev which is useful if you are searching from the end of the string for say, a "\" path delimiter.

try

msgbox "match at position: " & InStr([Wells]![Invoice ID],[PeopleSoft]![Invoice])

(although I am not sure what your [wells] and [peoplesoft] declarations are trying to achieve?)

 

[SDaccess24]

Table 1: Wells

Table 2: Peoplesoft

Please see Picture attached for tables.

I am trying to have a Access function to find is Recond # 4 with invoice ID ABCDEF is present in the Peoplesft table and pull the corresponding Amount field.

I tried instr function and it is not working :-(

Thanks!!

 

[Brianwarnock]

I'm always amused when people say a function does not work when they mean that they cannot use it .

You might like to take a look at this

http://www.utteraccess.com/forum/index.php?showtopic=1888759

Brian

 

[MarkK]

Also, your tables 1 and 2 should be merged and you should add a field. Data in that field should be "PeopleSoft" or "Wells." I don't know if those are customers or what, but see how much easier your job would be then? All data having the same structure should be in the same table, and distinguishing features in that data should be stored in fields.

Finally, you are actually trying to find a string inside a larger string using SQL, which you did not mention, and in that case you want to look into the SQL "LIKE" operator. Instr() is VBA, and most useful for finding substrings in VBA code. Use the SQL LIKE operator to match substring data in fields in a table.

Hope this helps,

 

[SDaccess24]

All I am trying to do is an access query between the two tables. I want to find all values in the Peoplesoft table where Invoice ID field is present in the Invoice field on the Wells table. Sorry if I wasn't very specific earlier.

Attached are my two tables for reference.

Thanks again!

 

[smig]

You have a bad design here.
This is why we have the Single To Many relations.

It can be solved with InStr() but you better redisgn your db

 

[Brianwarnock]

Puzzled as to why my link does not point at a solution to your problem, IE wild cards in joins.

Brian