Hjælp til at udvide en SELECT

not quite sure what it is you want! Do you want ALL dates > newdate? and where is the newdate?

OK here is an explanation:
If tabel1 is like this:
mads 1
Henrik 2
Søren 3

and Tabel2 like this:
1 2-3-2001 a
1 6-6-2001 b
2 7-4-2001 b
3 8-1-2001 b
3 9-3-2001 a
3 1-10-2001 b

If newdate is 1-5-2001 The result from the select should be:
Mads 2-3-2001 a
Henrik 7-4-2001 b
Søren 9-3-2001 a

I want to get the newst date and datetype from tabel2 where the date is older than "newdate" and then make a match with tabel1

Vanggaard

so if the newdate was 6-4-2001 then Henrik would not be in the result, is that correct?

yes. But with my data is the always a match.

Ok! Here is a solution, not the most elegant but I think it works!

Make a query named qtyNewDate

SELECT tabel2.nummer, tabel2.sdato, tabel2.type, tabel2.newdate
FROM tabel2
WHERE (((tabel2.sdato)<[newdate]));

Then alter your original query to>

SELECT tabel1.navn, qryNewDate.sdato, qryNewDate.type
FROM qryNewDate INNER JOIN tabel1 ON qryNewDate.nummer = tabel1.nummer
WHERE (((qryNewDate.sdato)=DMax("(sdato)","qryNewDate","nummer=" & [Tabel1].[nummer])));

Hva' me' a' prøv:

SELECT Tabel1.navn, Tabel2.sdato, Tabel2.type FROM Tabel1 INNER JOIN Tabel2 ON Tabel1.nummer=Tabel2.nummer WHERE Tabel2.sdato=DMax('(sdato)','Tabel2','nummer=' & Tabel1.nummer & ' AND sdato<#' &[newdate] & '#');

hi proaccess> I tried your example, but just couldn get it to work, hence my not so elegant solution. Maybe a syntax error or something!

>Terry: The logic is - I want the maximal value of sdato that's less than newdate... and that has to be found by every tabel1.nummer...
So the newdate-part has to come in the DMax.

It could be that [newdate] has to be converted to a format([newdate],"mm-dd-yyyy")

yes I thought the logic was as you mentioned it too, but when I couldnt get it to work decided on the solution I could get to work. I think you are right about formatting the date though!

vanggaard>Hows it going ?

Kom du videre med dette, og kan vi få spørgsmålet lukket?