Using INSTR function to find text stored in a table

[BoomerBroomer]

I currently download a HSBC bank statement that contains a field called Bank Reference. Within this field, there are certain unique expressions that I wish to find and assign a code to for my monthly bank reconciliation. For example, AMERICAN EXPRESS is within the field and I would like to code it as DEPOSITS, if it is found. Another expression that I want to find is AMEX FEES and code it as CREDIT CARD FEES.

I have set up a table for these expressions and codes but can't get them to work in a query using the INSTR function with the table name and field name as the search for string.

Any help would be appreciated.

 

[Darrylles]

Boomer,

More detail please - e.g. table structure, code/query SQL used.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[BoomerBroomer]

Sorry I am so slow but it is year end and it is rather time-consuming.

I've skinnied down my data base but it has all the essentials.

Hope this link below will work.

Thanks Boomer

 

[HarleyQuinn]

I'm not going to be able to download an example DB at work (as several other probably aren't I'd guess), could you post an example of your query that you tried along with examples of data in the field you want to change.

Regards

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Darrylles]

Boomer,

Thanks for example.

Try this:
[tt]
SELECT type AS tran_type, [Bank reference]
FROM [banking detail], [find phrase]
WHERE ((([Bank reference]) Like '*' & [phrase] & '*'));
[/tt]

ATB

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

Boomer,

Just realised:

1) This is in the wrong forum (SQL experts will look in the 'Access Queries' forum).
2) Your [tt]type[/tt] field is a reserved word - better that you change it.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.