DLOOKUP no bringing back a value

[RBE]

This code seems to be comming back with a null value. What I am trying to do is look up a code (primary key)on a table using values on a form and comparing them to the table. The table name is "Tariff" and the form name is "Tenders Form". The code is in the control source of the field on the form.
The terminal code,beginning date, ending date, and tariff rates: #2055 are all on the "tariff" table that I am doing the DLookUp on. If you can tell me if there is any thing wrong with the syntax of this code I sure would appreicate it. Or if this cannot be done this way I sure would appreicate some advice.

Thanks rbe
=DLookUp(&quot;[terminal code]&quot;,&quot;tariff&quot;,&quot;iif([TARIFF RATES: #2055] = [FORMS]![TENDERS FORM]![TARIFF LOOKUP],iif([FORMS]![TENDERS FORM]![DATE RECEIVED]> [BEGINING DATE] ,iif([FORMS]![TENDERS FORM]![DATE RECEIVED]< [ENDING DATE] , [terminal code],0)))&quot

 

[PaulF]

if you want to check to see if a record exists where
[Tariff].[TARIFF RATES: #2055] = [FORMS]![TENDERS FORM]![TARIFF LOOKUP]
And
[FORMS]![TENDERS FORM]![DATE RECEIVED]> [Tariff].[BEGINING DATE]
And
[FORMS]![TENDERS FORM]![DATE RECEIVED]< [Tariff].[ENDING DATE]

if it does then return the [Tariff].[Terminal Code]
else
return a Zero (0) then you need something along this line

SomeVariable = IIf(DLookUp(&quot;[Terminal Code]&quot;,&quot;[Tariff]&quot;, &quot;[Tariff].[TARIFF RATES: #2055] = &quot; & [FORMS]![TENDERS FORM]![TARIFF LOOKUP] & &quot; And #&quot; & [FORMS]![TENDERS FORM]![DATE RECEIVED] & &quot;# > [Tariff].[BEGINING DATE] And #&quot; & [FORMS]![TENDERS FORM]![DATE RECEIVED] & &quot;# < [Tariff].[ENDING DATE]&quot > 0,[Tariff].[Terminal Code],0)

PaulF

 

[RBE]

Sorry, That seemed to be comming back with a #error error.
Maybe I need to clarify a bit. What I am trying to do is come back with a field on a table by comparing the beginning date, the ending date, and the tariff rate on the table to the date received and the tariff lookup on the form.
The date received should fall somewhere in between the beginning and ending date on the table and the tariff lookup should be the same as the tariff rate. If these conditions are met then the formula should return the terminal code (an autonumber,primary key)from the table. I am entering this formula into the control source of a text box on the form not in VBA. I did not use the &quot;somevariable&quot; because there is no variable assigned in a control source it is the variable. This thinking may also be wrong. Any suggestions....

 

[PaulF]

with your form Tenders Form open and a record selected,
open the debug window, type in ? followed by
DLookUp(&quot;[Terminal Code]&quot;,&quot;[Tariff]&quot;, &quot;[Tariff].[TARIFF RATES: #2055] = &quot; & [FORMS]![TENDERS FORM]![TARIFF LOOKUP] & &quot; And #&quot; & [FORMS]![TENDERS FORM]![DATE RECEIVED] & &quot;# > [Tariff].[BEGINING DATE] And #&quot; & [FORMS]![TENDERS FORM]![DATE RECEIVED] & &quot;# < [Tariff].[ENDING DATE]&quot

then press Enter......
if it returns a value then the DLookUp part of the code works, and the problem is with the rest of the IIf statement ...if not then there is a typo somewhere in the DLookUp code.

PaulF