Order by dlookup in query

[BillCo]

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("LastOf FieldA,"qryA","[Fieldx]= '" & tblx.x & "'") AS Result
FROM tblx
ORDER BY DLookUp("LastOf FieldA,"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!

[BillCo]

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?

[BillCo]

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

[Ed Marzan]

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