DLookup between text fields to use in calculated expression

[prs312]

I’m having difficulty using DLookup in a query to calculate the “Morphine Equivalent Dosage (MED)” for medications taken. I have two tables and a query:

• tblTreatment – lists a medication taken, the dosage, and number of pills (this table is uploaded from excel)
• qryTreatment – based off of tblTreatment
• tblMEDLookup – lists medications and there morphine equivalent dosage (MED)

I'm trying to add a column to qryTreatment (called MED_Med1) that looks up the MED for a a medication taken (from tblMEDLookup) and multiplies it by the Dosage taken and Number of pills (from tblTreatment). The following picture illustrates this.


The expression I've tried to implement in qryTreatment to get MED_Med1 is the following:
MED_Med1: DLookUp("MED","tblMEDLookup","Analgesic= '" & Nz("[RescueMed1]",0) & " ' ")*([tblTreatment]![RescueMedDose1])*([tblTreatment]![RescueMedPill1])
So far this only returns blank values as shown below:

Any help on this issue would be very much appreciated.

[June7]

What field type is RescueMed1? If it is a number type and its actual value is the ID from tblMedLookup, that's why you get no results. The DLookup is referencing wrong field in the criteria.

Are you setting Lookup fields in table? Review: http://access.mvps.org/access/lookupfields.htm

[prs312]

the Data Type of [RescueMed1] is Short Text. and there aren't any lookup fields in tblTreatment, that table is imported as is from excel.

[June7]

Then I am at a loss as to why the DLookup fails. But why are you using DLookup instead of just joining tables in query?

[prs312]

The reason I can't join the two tables is that the tblTreatment is uploaded and may show records in which a patient takes a new medication that isn't already listed in tblMEDLookup. Therefore each medication must be typed in the exactly the same in tblMEDLookup and the MED for that med specified. Attached is a stripped down version of the database for clarification.

Clinical Database-stripped.zip

[June7]

Regardless if the tables are joined or you use DLookup, the Med values must be in tblMedLookup. Don't use INNER JOIN in the query, use RIGHT JOIN.

Not finding DLookup on tblMedLookup in qryTreatment.

Multiple similar name fields (RescueMed1, RescueMed2, etc) indicates a non-normalized data structure. This can cause lots of issues with data search and analysis. Because of this structure, would have to include tblMedLookup in the query 10 times, one for each RescueMedx field or have 10 DLookup expressions. Domain aggregates can slow down query performance.

[prs312]

Thanks, the RIGHT JOIN allowed me to get the MED values in the qryTreatment. you are right about the non-normalized data structure, but because this table is uploaded as is I'm forced to leave it that way and, like you said, include tblMedLookup in the query 10 times.

[June7]

Are you linking to the data or importing?

The data could be rearranged into a normalized structure. One way is with UNION query.

[prs312]

I'm importing so don't think I can change the structure.

[June7]

There are ways to import data into whatever structure you want. You just have to decide if it's worth the effort.