DlookUp Function - Access97

[scroce]

Hello - I have been reading up on this function, but I have run across some syntax that I don't quite understand, here it is:

Let's say this is in a textbox called "txtMyText"

=IIf(DLookUp("[OrigPro-RatedPremium]","PaySchedule","[AccountID]=" & [AccountID] & " AND [Year] = '" & [Year] & "'&quot Is Not Null,DLookUp("[OrigPro-RatedPremium]","PaySchedule","[AccountID]=" & [AccountID] & " AND [Year]='" & [Year] & "'&quot,DLookUp("[EstPremium]","PolicyPremiums","[AccountID]=" & [AccountID] & " AND [Year] = '" & [Year] & "' AND [PayPeriodID]=1&quot)

Specifically I am having trouble understanding what the "&" is doing as well as the "AND" statement.

I know that "&" generally signifies a concatenation, and that "AND" usually limits criteria in a query search.

That being given. Could somebody help translate, especially this part:

DLookUp("[OrigPro-RatedPremium]","PaySchedule","[AccountID]=" & [AccountID] & " AND [Year]='" & [Year] & "'&quot,

which i think says something like "OK, look in a field called [OrigPro-RatedPremium] in a table called "PaySchedule" where Account ID = (.......and then I get confused....)

If someone could help me understand the (....and then I get confused....) part I'd be grateful.

SteveC.

 

[THoey]

I think you are right on track. They are just building their WHERE clause for the lookup. They want to find a specific OrigPro-RatedPremium from the table PaySchedule where the accountID = a certain accountID and the year = the year. Same as the following:

SELECT [OrigPro-RatedPremium] FROM PaySchedule 
WHERE [AccountID]=[AccountID] AND [Year]=[Year],

Don't doubt yourself, you were right... Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[Mongooses]

In this case the "&" is used to concatenate strings and the "AND" is part of the SQL statement. In other words
"[Field1] = " & "[Field2] AND " & "[Field3] = " & "[Field4]"
Creates a single statement that is formatted in a syntactically correct SQL statement.

HTH,
JC

 

[Rohdem]

It helps me to envision what the string that is being passed to the function looks like, for example if accountid = 1000 and year = 2001, than the criteria string being passed is:

[AccountID] = 1000 AND [Year] = '2001'

AccountID must be a number field because 1000 is not in single quotes, whereas year must be a string because 2001 is in quotes. If year were a date instead, you would enclose it with the pound (#) sign.
Mike Rohde
rohdem@marshallengines.com

 

[scroce]

I just wanted to say a big thank you for eveyone who took the time to reply to my request.

Sorry for the tardiness.