find string as a part of larger string (1 Viewer)

SDaccess24 · Oct 10, 2015

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 · Oct 10, 2015

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

SDaccess24 · Oct 10, 2015

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 · Oct 10, 2015

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 · Oct 10, 2015

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 · Oct 12, 2015

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 · Oct 12, 2015

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 · Oct 12, 2015

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 · Oct 12, 2015

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 · Oct 13, 2015

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 · Oct 13, 2015

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

Brian

find string as a part of larger string (1 Viewer)

SDaccess24

New member

MarkK

bit cruncher

SDaccess24

New member

MarkK

bit cruncher

gemma-the-husky

Super Moderator

SDaccess24

New member

Attachments

Brianwarnock

Retired

MarkK

bit cruncher

SDaccess24

New member

Attachments

smig

Registered User.

Brianwarnock

Retired

Similar threads

Users who are viewing this thread