Can I make this query more accurate - 1

[ZOR]

I have a query in a listbox that shows how many weeks ago I saw a client. I looked at one entry where the visit date was 20/02/2006 with the date today being 24/03/2006 it showed 11 weeks duration?

The query is as follows:

SELECT Contacts.ID2, Companies.ID1, Contacts.Name, Max(Appts.Visited) AS [Last Visit], Min(IIf(IsNull([Appts].[Visited]),'-',(Date()-Nz([Appts].[Visited],0))\7 & ' Weeks')) AS Duration
FROM .........

Many thanks

 

[PHV]

You may try this:
IIf(IsNull(Max(Appts.Visited)),'-',(Date()-Nz(Max(Appts.Visited),0))\7) & ' Weeks' AS Duration

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ZOR]

You hit it right on the head. Many thanks yet once again. Have a star & good weekend