What is wrong with this formula

[RBE]

There is a typo in this formula somewhere. I am comming back with a #error. This is the control source of a text box on a form. I got this off of the board so the syntax may be wrong. As far as I can see there is nothing wrong with it. Helllppp. Thanks RBE

=DLookUp(&quot;[Terminal Code]&quot;,&quot;[tblTariff]&quot;,&quot;[TARIFF RATES: #2055] = &quot; & [Forms]![frmTENDERS]![CODE LOOKUP] & &quot; And #&quot; & [Forms]![frmTENDERS]![DATE RECEIVED] &&quot; # >[tblTariff].[BEGINING DATE] And#&quot; & [Forms]![frmTENDERS]![DATE RECEIVED] &&quot;# < [tblTariff].[ENDING DATE]&quot

 

[PaulF]

Add a space between And#
DATE] And#&quot; & [Forms]![frmTENDERS]![DATE RECEIVED] &&quot;# <
DATE] And #&quot; & [Forms]![frmTENDERS]![DATE RECEIVED] &&quot;# <

PaulF

 

[RBE]

Still not working. What are the & and # signs for? I am not sure what exactly the code is doing. Maybe if I understood I could fix it my self. Thanks for all your help Paulf.

=DLookUp(&quot;[Terminal Code]&quot;,&quot;[Tariff]&quot;,&quot;[TARIFF RATES: #2055] = &quot;& [Forms]![TENDERS FORM]![CODE 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

 

[JoeMiller]

You're code is very wrong in the first place. I'm going to GUESSSS that you have a field Date Received in your tblTarriff and have an BeginDate and Endate on frmTenders where the user enters two dates and gets a return?? If so the code would look like this:

DLookUp(&quot;[Terminal Code]&quot;,&quot;[tblTariff]&quot;,&quot;([TARIFF RATES: #2055] = [Forms]![frmTENDERS]![CODE LOOKUP])
AND ([DATE RECEIVED] Between [Forms]![frmTENDERS]![BEGINNING DATE] And [Forms]![frmTENDERS]![ENDING DATE])&quot

Your statements are backwards in the original code. If my assumptions are wrong, let me know how they differ.

HTH
Joe Miller
joe.miller@flotech.net

 

[PaulF]

These are the assumptions I made when I looked at your code

[Terminal Code] is a field in the table Tariff
[Tariff] is the table name
[TARIFF RATES: #2055] is a numeric field in table Tariff
[CODE LOOKUP] is a Unbound textbox on Form [TENDERS FORM]
[DATE RECEIVED] is a date/time field in table Tariff
[BEGINING DATE] is an Unbound textbox on Form [TENDERS FORM]
[ENDING DATE] is an Unbound textbox on Form [TENDERS FORM]


so you must enter data into the three textboxes on the form
and since these are not part of the recordset, you need to pass them as if they were variables, hence the double quotation marks and the ampersands (&), also since you need to pass date/time data from textboxes [Begining Date] and [Ending Date] to compare to [Date Received] you need to use # delimiters to indicate that it is date/time data.

PaulF

 

[RBE]

Sorry this is so confusing. This is what I have. And so far all my mangling of the original code still will not work. I keep comming back with the same error. This is where each field is and in which object. And below that is the code as it sits right now.

[Terminal Code] is a field in the table Tariff
[Tariff] is the table name
[TARIFF RATES: #2055] is a TEXT field in table Tariff
[CODE LOOKUP] is a Unbound textbox on Form [TENDERS FORM]
[DATE RECEIVED] is a unbound textbox on form [TENDERS FORM]
[BEGINING DATE] is a date field on the TABLE [TARIFF]
[ENDING DATE] is a date field on the TABLE [TARIFF]
(The reason the table has two date fields is because the tariff rates start on one date say, 7-1-01 and end on another date say, 7-1-02. I need to pull the tariff code for the date set that the date received field on the form falls between. If I need to redesign the table to make this simpler I will there is only 12 entries in it right now.)

CODE AS IT STANDS NOW
=DLookUp(&quot;[Terminal Code]&quot;,&quot;[Tariff]&quot;,&quot;[TARIFF RATES: #2055] = &quot; & [Forms]![TENDERS FORM]![CODE 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

When I use the debug window it comes back with an error that says something to the effect of &quot;expected an end of statement&quot;
I sure appreicate all the help guys.
RBE

 

[JohnLowell]

Try this:
=DLookUp(&quot;[Terminal Code]&quot;,&quot;[Tariff]&quot;,&quot;[TARIFF RATES: #2055] = '&quot; & [Forms]![TENDERS FORM]![CODE 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


You need to include a single quote when going against the column Tariff rates: #2055. Also, you can not have a space before or after the inserted # when building an Access date.

Let me know how this goes.

 

[RBE]

OH you are beatuiful, great and most of all a lifesaver. Thanks. If you can't tell it worked great. Thank you, Thank you , Thank you.
RBE