Ms access 2013 dlookupp (1 Viewer)

Hi there, I am new new new to this site.
Been searching the web for days now on how to solve my dlookup issue. I know, just know there must be a way but I can't find it.

Here's my issue.

I have a table called BANK ACCOUNT
this table contains three fields
DATE AMOUNT DESCRIPTION

Because the description looks something like the following :

GOOGLE*ADWS82287718 485442*8690 05 MAY
PNP FRAN EDENGLEN 485442*8690 09 MAY
SMART-AP PREPAID AIRTIME 0712344171

I have appended the descriptions to another table called TRANSACTION ALIASES, that has two fields:

TRANSDESCRIPTION TRANSACTION ALIAS

where I have assigned alias names, for example :

TRANSDESCRIPTION TRANSACTION ALIAS
GOOGLE*ADWS82287718 485442*8690 05 MAY - GOOGLE
PNP FRAN EDENGLEN 485442*8690 09 MAY - PICK N PAY
SMART-AP PREPAID AIRTIME 0712344171 - VODACOM PREPAID

I then have a query, where I want to match the alias to the description:
So my query has two fields :

DESCRIPTION (which comes from [BANK ACCOUNT].[DESCRIPTION]); and
ALIAS which has the following criteria :

ALIAS: DLookUp("[TRANSACTION ALIAS]","TRANSACTION ALIASES","[TRANSDESCRIPTION]='" & [DESCRIPTION] & "'")

Now that works fine, unless I have two GOOGLE*ADWS82287718 485442*8690 descriptions and not on the same date so perhaps
GOOGLE*ADWS82287718 485442*8690 06 MAY

How would I get my criteria to match one or all or some of the words in the TRANSACTION ALIAS field IN THE TRANSACTION ALIASES TABLE to the word or words within the DESCRIPTION FIELD of [BANK ACCOUNT].[DESCRIPTION]

I have read up on Dlookp with Like criteria but nothing works for me.

Please can you assist, I'd be so very grateful.

looks like you need to exclude the date element of TRANSDESCRIPTION. How you do this it is not possible to say without being clearer of rules to apply. For example, you could exclude the last 6 characters (assuming June is Jun, July is Jul etc), but that wouldn't work for the SMART transaction.

If you manually removed the dates in TRANSACTION ALIASES (which by the way should be in square brackets because of the space) so you have a common denominator across all google transaction for example,

your dlookup would be
ALIAS: DLookUp("[TRANSACTION ALIAS]","[TRANSACTION ALIASES]","[TRANSDESCRIPTION] Like '" & [DESCRIPTION] & "*'")

DLookup only finds the first match if I recall correctly.

You could try a DCount("[TRANSACTION ALIAS]","[TRANSACTION ALIASES]","[TRANSDESCRIPTION] Like '" & [DESCRIPTION] & "*'")
to get some idea of the volume.
Another option is to use VBA with a recordset and a loop structure to "do something" to each occurrence of your target.

More info and some additional examples might be helpful.

Good luck.

DLookup only finds the first match if I recall correctly.

Click to expand...

you recall correctly - but it is the first randomly, no guarantee it will return the same value when more records have been entered