SparkyP
asked on
Dlookup wildcard in reverse.
I want to lookup a table from a subform where the main form has extended detail over the lookup table.
In essence I want to reverse the wildcard in the following:
Me.Cost = DLookup("Attendance Fee", "ContractedSites", "Site Like '*" & Forms![Job Reports]![Site] & "*'")
For example:
the Site on the Form is "Wolverhampton University Main Campus" and the Site in the lookup Table is "Wolverhampton University".
The Table will always have the extended detail over the Form.
Hope this makes sense?
In essence I want to reverse the wildcard in the following:
Me.Cost = DLookup("Attendance Fee", "ContractedSites", "Site Like '*" & Forms![Job Reports]![Site] & "*'")
For example:
the Site on the Form is "Wolverhampton University Main Campus" and the Site in the lookup Table is "Wolverhampton University".
The Table will always have the extended detail over the Form.
Hope this makes sense?
Alternatively, you might iterate through different permutations of "Wolverhampton University Main Campus" until you found it, removing the interior words and separating them with an asterisk character.
You might want to consider a schema change. If you have group locations and detail locations, you should have two related tables. You may find that some "group" locations do not have "detail" locations so both names would be the same.
ASKER
As usual, you experts help me refocus and tackle the issue in a different manner. Sorted using a query. Thank you both.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Open in new window
which wouldn't match "Wolverhampton University" in your table.If it were the other way around, you could make this approach work.
I think you need to have a multi-column list/combobox with both the long name (visible) and the short name (not visible). But if you're going to go this route, you'd get better performance if you had the row ID value as a hidden column.