DLOOKUP in a query

[sherylnance]

Hi All,

I would like to use dlookup in a query to return the SchName based on a field (short_num) in my query.....

I know this sounds really easy but for some reason I CANT make it work. for the rows that have data for location number the dlookup function is returning nothing and for the rows that have nothing for the location number its returning a location name, which of course is not correct.

here is the syntax of my query:
SELECT Assignment.[AA Student Number], Assignment.[Legal Name], Assignment.[First Name], Assignment.[Street Number], Assignment.[Street Name], Assignment.Apartment, Assignment.Municipality, Assignment.State, Assignment.[Zip Code], Assignment.[Birth Date], Assignment.Ethnicity, Assignment.Gender, Assignment.[Home School], Assignment.Hrm1, Assignment.NYSch, [grd]+1 AS NYGrade, Right([Catchment],3) AS short_num, DLookUp("name","schools","schools.short_num"=[short_num]) AS SchName
FROM Assignment
WHERE ((([grd]+1)>8 And ([grd]+1)<13));

The Schools table is as follows:

NO
Name
Short_num
Zone

I have tried every variation of quotes and square brackets known to man....

Some one please help me. I am at my wits end and I know the answer is staring me in the face....

Thanks for any help!!

 

[MarkK]

My guess is that your Right([Catchment],3) AS short_num is not evaluated early enough to be used by a function within the same query.
I'd try DLookUp("name","schools","short_num = " & Right([Catchment],3)) AS SchName