DLookup in a query

[Kevin Bruce]

This should be simple, but I'm finding it troublesome...

I have a report based on an SQL statement that is based on a table
(tblAIEFinancial).

Each record in the table has two fields which, when combined, identify that
record as unique: [DistrictAndAffiliationID] and [ApplicationYear].

The query needs to pull the data for one particular field [AssessmentR] from
both the current year as well as that for the previous year and put them on
the same line of the query result so that both can be printed on the report.

To this end, I have created a user-defined function as follows:

AssessmentRLastYear:
DLookUp("[AssessmentR]","tblAIEFinancial","[DistrictAndAffiliationID]=
DistrictAndAffiliationID " And "[ApplicationYear]=ApplicationYear-1")

This does not produce the desired result, but rather returns the same data
for all records.

Any help would be much appreciated.

Thanks.

--
================================
Kevin Bruce
Program Coordinator
ArtStarts in Schools
301 - 873 Beatty Street
Vancouver, BC V6B 2M6

ph:604-878-7144 ext.3
fx: 604-683-0501

web: www.artstarts.com

 

[Duane Hookom]

Try:
AssessmentRLastYear:
DLookUp("[AssessmentR]","tblAIEFinancial","[DistrictAndAffiliationID]=" &
DistrictAndAffiliationID & " And [ApplicationYear]= " & ApplicationYear-1)

 

[John Vinson]

This should be simple, but I'm finding it troublesome...

I have a report based on an SQL statement that is based on a table
(tblAIEFinancial).

Each record in the table has two fields which, when combined, identify that
record as unique: [DistrictAndAffiliationID] and [ApplicationYear].

The query needs to pull the data for one particular field [AssessmentR] from
both the current year as well as that for the previous year and put them on
the same line of the query result so that both can be printed on the report.

To this end, I have created a user-defined function as follows:

AssessmentRLastYear:
DLookUp("[AssessmentR]","tblAIEFinancial","[DistrictAndAffiliationID]=
DistrictAndAffiliationID " And "[ApplicationYear]=ApplicationYear-1")

This does not produce the desired result, but rather returns the same data
for all records.

Two suggestions: if you want to use DLookUp at all (and I think my
other suggestion will be preferable), leave the AND inside the quotes
and the criteria out:

DLookUp("[AssessmentR]", "tblAIEFinancial",
"[DistrictAndAffiliationID]= " & DistrictAndAffiliationID &
" And [ApplicationYear] = " & ApplicationYear-1)

OR: use a Self Join query instead. Create a Query by adding your table
*twice*, joining the two instances by DistrictAndAffliationID; put a
criterion on the first instance's ApplicationYear of

=Year(Date())

and on the other instance of

=Year(Date()) - 1