DLookup Formula problem 1

[shelby55]

Hi

I am using Access 2000.

I have a table called tblData which has a field called Exit. The descriptions of Exit codes are in a table called tblDispCodes.

My database is for entering patient data and I want to have the user be able to print off a summary of entered data per abstract once a record is completed. The unique patient identifier is acctno. The report is based on qryData and in the acct number field in the query I have it equal to Forms!frmDataCollect!acctno. Frmdatacollect is the form where the command button is to print the report. On the report, however, I want the exit description to show up, not the exit code.

In the report I have the field as:

=DLookUp("[DispName]","tblDispCodes","[DispID]= '" & DLookUp("[Exit]","tblData") & "'")

This works fine but only selects the exit code from the first record and not per record. How do I include further identifier for this? My attempt that didn't work:

=DLookUp("[DispName]","tblDispCodes","[DispID] = '" & DLookUp("[Exit]","qryData") & "And [AcctNo] = '" & DLookUp("[AcctNo]","qryData") & "'")

Note that AcctNo is a text field.

Any and all assistance greatly appreciated.

Shelby

 

[randysmid]

HI,
I am not sure why you have two DLookups, but I don't think that this is necessary. Presuming that you have DispID in the detail section (either visible or not), this code should work:
=DLookUp("[DispName]","tblDispCodes","[DispID] = '" & [DispID] & "'"
' note the double quote, then single quote, then double

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[shelby55]

Hi

It worked! Not sure why I was trying 2 DLookups either!! Thanks so much!

Shelby