DMAX if only one record?

[angelandgreg]

I tried searching to see if i can find something first but did not find anything ...


I have a query that looks for the last date for an order number.


But if the order number only exists once it doesn't show.
It only shows the DMAX date IF the order number exists more then once.


How or what should i use to have my query show only the last or only record for an order number?

 

[PHV]

Why not posting your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[angelandgreg]

UPDATE (((SO2_SOEntryDetailLine ON OrdersToReprint.SalesOrderNumber = SO2_SOEntryDetailLine.SalesOrderNumber) INNER JOIN ControlLogArchives ON OrdersToReprint.SalesOrderNumber = ControlLogArchives.SalesOrderNumber 

SET ControlLogArchives.LogStatus = "BrghtBk"

WHERE (((ControlLogArchives.ControlLogDate)=DMax("ControlLogDate","ControlLogArchives")));

 

[dhookom]

Since this is an update query that seems to only involve the single table ControlLogArchives, why do you need the other tables?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[angelandgreg]

hi dhookom!

it needs to link where the order number exists in the SO table as well and update the status in the ControlLogArchives where it's the only one or the lastest one, hence the need to use the DMAX.

I think it's working though. I tried it on one that had only once instance in the ControlLogArchives and the DMAX didn't bring that one up but tested with another order number that was in the table more then once and it worked.

BUT i tried it again yesterday for an order number that was in only once and it seemed to work.

should it?