Dlookup help

[Garz]

Hello all, this one slightly beyond my skills.

I was working on an inherited database dump from a legacy platform that is no longer being subscribed to. I can easily rebuild it just linking the relationships up.

There was a table used as a helper table to keep many static lists but splits them up like this:

ID | Description | Column | Code | Value

The column being the name of the field in the main tables and storing the number instead of the named category (Value).

I am trying to do a Dlookup so in a Table it refers to a Code, which is contained in the helper table above. One of the issues is the helper table Code is Text whereas in the main tables the Code is a datatype Number. I have bypassed the problem by converting the Code to type number and Making a new table with codes and values into a dedicated table.

Here is an example snippet of the records:

ID    Description            Column            Code    Value
27    CalendarItems    PriorityCode    0    Normal
28    CalendarItems    PriorityCode    1    Low
29    CalendarItems    PriorityCode    2    Important
30    CalendarItems    PriorityCode    3    Critical
31    Transactions    TransactionTypeCode    0    Unknown
32    Transactions    TransactionTypeCode    1    Segmented Payment
33    Transactions    TransactionTypeCode    2    Segmented Gift
34    Transactions    TransactionTypeCode    3    Pledge/Payment
35    Transactions    TransactionTypeCode    4    Pledge
36    Transactions    TransactionTypeCode    5    Gift

I was hoping someone could put me right in case I wanted to build queries where I could present the names instead of the values using Dlookup?

Here was the builder code I used before giving up that route as it was returning the first record only through all the results and not what the name was:

DLookUp("Value","ColumnCodes","Description='Transactions'" And "[*Code] = [TransactionTypeCode]")

 

[Minty]

You would join the ColumnCodes table to your data table on the code field, and limit the results by putting the criteria in the description field.

Code is (I think) a reserved word, maybe change it to Code_ID?

You would want to use a left join to make sure that Data without a code is still displayed.

 

[Garz]

Thanks @Minty I got that working in the Query instead of using a DLookup, much neater.

If I was to use DLookup on that ColumnCodes table is that approach how to do it using multiple criteria?

 

[Minty]

If you can use a table join then please do, it's an order of magnitude more efficient.
A Dlookup is effectively creating an individual query for every record returned, not a problem on a single record form, but very poor performance on a continuous form.

The syntax would be something like

DLookUp("Value","ColumnCodes","Description='Transactions' And [ Code] = " & [TransactionTypeCode])

Note how you concatenate the field value from your data table.
No quotes for number values. Dates should use # # .
See the link in my signature

 

[Gasman]

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
The when correct, I can use them in the function.

Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

 

[Pat Hartman]

You should almost always be using left joins when joining to the table with the lookup values. The only time it is safe to use inner joins is if the lookup ID is defined at the table record as required and the default is null NOT 0 (which is the Access default for numbers)

 

[Garz]

Regards to the query method. If you have two columns in the same table that use the helper table, do you have to create two separate queries then to refer to the joins?

 

[Minty]

No simply add the lookup table a second time.
Access will automatically give it an Alias such as ColumnCodes1 but it is the same table.

 

[Garz]

Thanks guys, bit of a tougher way to arrange a query but got the criteria right now (Where SQL).

 

[Pat Hartman]

You need to add the lookup table a second time to tell the query engine what you want. YOU know that the two relationships are separate so the second copy of the table informs the engine so it knows too

 

[Garz]

Hi @Pat Hartman it was slotting in the criteria that was making me doubt it. Sometimes Access is not that smart when it comes to my assumptions

@Minty thanks for the Dlookup example I was almost there but your close quote ampersand was the nugget I needed!

Thanks again gents!