Using first record of a table in a query ? 1

[breukelen]

Good Morning.

I want to use the first value of a record of a table
in a query expression.
Need help with expression.It must be something like
[yearchoice]![year] etc.

Thank in advance for your help.

Gunter

 

[JimHorton]

Gunter,
Is the table from which you want the first value a different table than the one in the original query in question? I'm assuming so (otherwise the question wouldn't make sense or I didn't understand it).

If you have a select query, and in one of the columns you want the value of the first record in some other table, you first need to know what constitutes 'first record'. Once that is known then you can use a sub-select or dlookup in the expression to return the result.

Easiest is Dlookup. Now, since the result of the dlookup may vary as data changes, and probably won't always have an easy criteria for 'first' record, ie "RecordID = 1", you should create a query, ie "Select Year from Yearchoice Order by Year". Name it, ie. query1. Then Dlookup("year","query1&quot will, with *no* criteria, return the 'first' record, assuming the Order by field is the one that denotes 'first' record, according to you.

Conversly, if you wanted the last record, just change the query1 to ORder BY Year Desc, and the dlookup returns the first record, which is the 'last' record.

--Jim

 

[breukelen]

Hello Jim ,

You got me thinking and I solved my problem.

Thanks for your response !

best regards ,

Gunter