Syntax for a DLOOKUP as the criteria of another DLOOKUP?

[BoydW]

Can a DLOOKUP function which returns a string be the criteria for a DLOOKUP? Here's my example I'm working with now:

-Text box
Name: OPO_SSN
Control Source: =DLookUp("[SSN]","NTT Staff","[Dept]='OPO'&quot

-Another Text box
Control Source: =DLookUp(&quot; [Type]&quot;,&quot;Transactions&quot;,&quot; Date1 >= [StartDate] and Date1<=[EndDate] and OPO_SSN=[SSN]&quot

I'd like to replace the criteria in the second text box (OPO_SSN=[SSN) with the DLOOKUP formula in the first text box to consolidate the calculation. I'd surely appreciate any guidance in this. Thanks!

-Boyd

 

[jimmythegeek]

Try something like this on the OnOpen event of the form (let's say the name of your second text box is txtType):

==========
Dim strSSN As String, strDLU As String

strSSN = DLookUp(&quot;SSN&quot;,&quot;NTT Staff&quot;,&quot;Dept='OPO'&quot
strDLU = DLookUp(&quot;Type&quot;,&quot;Transactions&quot;,&quot;Date1 >= StartDate and Date1 <= EndDate and OPO_SSN = '&quot; & strSSN & &quot;'&quot

Me.txtType = strDLU
========== Jim Lunde
compugeeks@hotmail.com
Custom Application Development