469,942 Members | 2,516 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,942 developers. It's quick & easy.

Order by dlookup in query

I'm usually strongly opposed to using dlookup in queries, but I need
this query to be updatable, so adding a subquery isnt an option. The
dlookup works fine except, I need to sort the results based on the
returned values. This doesnt work. Here's what I have (names changed to
protect the innocent!):

SELECT
tblx.x,
tblx.y,
tblx.z,
DLookUp("LastOfFieldA,"qryA","[Fieldx]= '" & tblx.x & "'") AS Result
FROM tblx
ORDER BY DLookUp("LastOfFieldA,"qryA","[Fieldx]= '" & tblx.x & "'")

If anyone has any ideas how to force the results to be returned in
order of the 'Result' field, please let me know... I have a feeling
there's a simple solution eluding me and it's starting to do my head
in!!!

Thanks!

Nov 13 '05 #1
3 5962
Update:
Ok, I've just realised that does actually kind of work - but the
problem is that the returned value is a date field and the sorting is
done alphabetical... I cant force the field to be recognised as a date
and sort the results properly. Changing the format in the frield
properties to "dd/mm/yyy" doesnt work. Any ideas?

Nov 13 '05 #2
Update 2:

Ok, solution found but it ain't pretty:

ORDER BY
format(DLookUp("LastOfFieldA,"qryA","[Fieldx]= '" & tblx.x & "'"),
"yyyy"),
format(DLookUp("LastOfFieldA,"qryA","[Fieldx]= '" & tblx.x & "'"),
"mm"),
format(DLookUp("LastOfFieldA,"qryA","[Fieldx]= '" & tblx.x & "'"),
"dd")

Any more elegant solutions would be greatefully recieved

Nov 13 '05 #3
How about using the CDate Function? This function attempts to convert
it's arguments into a date which you can sort on.

ORDER BY CDate(Fieldx)

Good luck
BillCo wrote:
Update 2:

Ok, solution found but it ain't pretty:

ORDER BY
format(DLookUp("LastOfFieldA,"qryA","[Fieldx]= '" & tblx.x & "'"),
"yyyy"),
format(DLookUp("LastOfFieldA,"qryA","[Fieldx]= '" & tblx.x & "'"),
"mm"),
format(DLookUp("LastOfFieldA,"qryA","[Fieldx]= '" & tblx.x & "'"),
"dd")

Any more elegant solutions would be greatefully recieved


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Tony Williams | last post: by
8 posts views Thread by Christine Henderson | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.