add a date expression on my query

[Guest]

PAYDATE: (SELECT ????([DATE]) FROM [TABLE2] WHERE [TABLE2].[IDNO] =
[TABLE1].[IDNO])

i want to set the the earliest date record from other table to my query but
i do not know what is the correct expression for this can any1 teach me how!

 

[Allen Browne]

Try MIN in place of ????
PAYDATE: (SELECT Min([DATE]) AS MinDate FROM ...

BTW, if you really have a field named Date, it will cause you problems.
Access will yield wrong answers (mistaking it for the system date) or errors
in some circumstances.

For a checklist of words to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html