Inconsistent DLookup Behaviour - ARRRRG!!!

[ahmun]

Can anybody answer this one for me?

1. I have 2 lookup tables (BusUnit and Title) with identical structures:
a. an ID field (autonumber incremented)
b. a text field for the name of the data

2. I have a main "employee" table that has 2 fields (BusUnit and Title) that reference the ID fields of these lookup tables

3. I have a report with the employee table as its source. I ahve two bound controls to BusUnit and Title fields in employee table. These fields are invisible

4. I have two unbound fields with its control source as the following:

=DLookUp("[txtBusUnit]","lkpBusUnit","[lkpBusUnit]![intBusUnitID] = " & [intBusUnitID])

=DLookUp("[txtTitle]","lkpTitle","[lkpTitle]![intTitleID] = " & [intTitleID])

5. Currently, for all records in the employee table the BusUnit and Title fields are blank. No data has been entered yet. (just employee names, but all other attributs haven't been filled out yet)

---------Here is the InCoNsIsTenCY---------
6. When I run the report, The unbound control for BusUnit shows nothing (what I expect), however, the unbound control for Title shows

#Error

Earnie Eng - Newbie Access Programmer/DBA
If you are born once, you will die twice.
If you are born twice, you will die once

 

[Maquis]

Off the top of my head, I don't know what is wrong with the dlookup statements, however this really isn't the right way to go about getting the information on to the report.

Change the report's recordsource from being simply the employee table to a query which includes the necessary text fields in your lookup tables. Then you can just set the recordsource of your text boxes to those fields. Maq B-)
<insert witty signature here>

 

[tunsarod]

I think your lookup statements should be written:

=DLookUp(&quot;[txtBusUnit]&quot;,&quot;lkpBusUnit&quot;,&quot;[lkpBusUnit]![intBusUnitID] = '&quot; & [intBusUnitID] & &quot;'&quot

=DLookUp(&quot;[txtTitle]&quot;,&quot;lkpTitle&quot;,&quot;[lkpTitle]![intTitleID] = ' &quot; & [intTitleID] & &quot;'&quot

Rod

 

[ahmun]

I've been toying around with the DLookup statement, and it turns out that the problem is when I have no values (NULL?) in those fields the DLookup is querying.

I've tried using an IIF statement to test if the value is null, but found out through help file that IIf evaluates both the true and false statement.. thus I run into the #Error message again as DLookup tries to evaluate a null value...

any thoughts on catching that?
again... the values are allowed to be null. Earnie Eng - Newbie Access Programmer/DBA
If you are born once, you will die twice.
If you are born twice, you will die once

 

[jhall156]

Maquis is correct. Unless you have a compelling reason to use DLookUp() you should write a query to get all the information for the report. JHall

 

[tunsarod]

OK, just for the hell of it...

=DLookUp(&quot;Nz([txtBusUnit],'')&quot;,&quot;lkpBusUnit&quot;,&quot;[lkpBusUnit]![intBusUnitID] = '&quot; & [intBusUnitID] & &quot;'&quot

=DLookUp(&quot;Nz([txtTitle],'')&quot;,&quot;lkpTitle&quot;,&quot;[lkpTitle]![intTitleID] = ' &quot; & [intTitleID] & &quot;'&quot

Rod