Over 60 Days Query

[sandy budd]

I need a query that will show all customers that have not been visited in over 60 days. I have tried >Date()-60 and it returns customers that have been visited within 60 days. I also tried <Date()+60 to try and get the reverse effect but it is actually returning all customer that have been visited within 60 days and deducting 60 days from the last visit date.

The dates are entered under a field named "Visit Date". Any suggestions?

[jzwp11]

What it sounds like you need is to select those records where the difference between today's date and the visit date is greater than 60 days

WHERE datediff("d", [Visit Date], date()) >60

[sandy budd]

Thank you so much for your response. I tried this and it says "The expression you entered contains an invalid syntax". You may have entered an operand without an operator.

What it sounds like you need is to select those records where the difference between today's date and the visit date is greater than 60 days

WHERE datediff("d", [Visit Date], date()) >60

[jzwp11]

My apologies, the WHERE clause I provided is what you would need if you were using the SQL view for the query and not the design grid view of the query. To do this in design grid view, you will need to create an new field with the expression: datediff("d", [Visit Date], date()) and then set the criteria to >60

You will have to make sure the visit date field name is correctly spelled. If you actually have a space in the name, you must enclose it in square brackets.

By change can you post the SQL text of the query that did not run properly? (You should just be able to switch to SQL View to see the SQL text) You can also just copy the WHERE clause text directly in that view if you wish.

[sandy budd]

When you say "create a new field", does that mean the field name is Visit Date as well? Here's a copy of the SQL that returns all visits within 60 days:

SELECT DISTINCT CLIENT.CITY, CLIENT.AGT_INIT, PRODUCT.[ACTIVE?], [SERVICE CYCLE].[Visit Date], CLIENT.[Service Cycle], CLIENT.[ACCOUNT NAME]
FROM (CLIENT INNER JOIN PRODUCT ON CLIENT.[CLIENT CTR] = PRODUCT.[CLIENT CTR]) INNER JOIN [SERVICE CYCLE] ON CLIENT.[CLIENT CTR] = [SERVICE CYCLE].[CLIENT CTR]
WHERE (((CLIENT.AGT_INIT)=[Enter Agents Initals]) AND ((PRODUCT.[ACTIVE?])="yes") AND (([SERVICE CYCLE].[Visit Date])>Date()-60));

[sandy budd]

I am trying to create a query that returns all clients that have not have visits within 60 days. If I use <Date()+60)) I get 0

[jzwp11]

Try the following; I've adjusted the WHERE clause accordingly.

SELECT DISTINCT CLIENT.CITY, CLIENT.AGT_INIT, PRODUCT.[ACTIVE?], [SERVICE CYCLE].[Visit Date], CLIENT.[Service Cycle], CLIENT.[ACCOUNT NAME]
FROM (CLIENT INNER JOIN PRODUCT ON CLIENT.[CLIENT CTR] = PRODUCT.[CLIENT CTR]) INNER JOIN [SERVICE CYCLE] ON CLIENT.[CLIENT CTR] = [SERVICE CYCLE].[CLIENT CTR]
WHERE CLIENT.AGT_INIT=[Enter Agents Initals] AND PRODUCT.[ACTIVE?]="yes" AND datediff("d", [Visit Date], date()) >60

BTW, what is the datatype of the active? field? If it is a yes/no field and not a text field, you may want to change the "yes" to -1

[sandy budd]

I really appreciate your help. When adjusting the SQL, it shows the last visit date of all of the customers and subtracts 60 days from their last visit.

[RayMilhon]

Unless I'm mistaken this query will get all that had a visit date > 60 days ago but if they had a visit say 90 days ago and a second visit 10 days ago it will show the 90 day visit. If I understand your question correctly. You only want those where the most recent visit is > 60 days ago. So You're query would have to be slightly modified.

SELECT DISTINCT CLIENT.CITY, CLIENT.AGT_INIT, PRODUCT.[ACTIVE?], Max([SERVICE CYCLE].[Visit Date]), CLIENT.[Service Cycle], CLIENT.[ACCOUNT NAME]
FROM (CLIENT INNER JOIN PRODUCT ON CLIENT.[CLIENT CTR] = PRODUCT.[CLIENT CTR]) INNER JOIN [SERVICE CYCLE] ON CLIENT.[CLIENT CTR] = [SERVICE CYCLE].[CLIENT CTR]
WHERE CLIENT.AGT_INIT=[Enter Agents Initals] AND PRODUCT.[ACTIVE?]="yes"
Group by CLIENT.CITY, CLIENT.AGT_INIT, PRODUCT.[ACTIVE?], CLIENT.[Service Cycle], CLIENT.[ACCOUNT NAME]
having datediff("d", Max([Visit Date]), date()) >60

[jzwp11]

It should not be doing any subtracting since there is no manipulation of the date in the SELECT clause. Now if you have multiple records for each client each with different visit dates then you will have to use an approach similar to what Ray suggests where you find the most recent visit for each client first and then apply the criteria to see if the most recent visit is > 60 days ago.

I would probably do a max query first just on the service cycle table

SELECT [client ctr], Max([Visit Date]) as MostRecentVisit
FROM [Service cycle]
GROUP BY [Client ctr]

Save the above query, then create a new query that contains the above query and your service cycle table & join by both the client ctr and the two date fields (visit date & MostRecentVisit). You can probably bring in your other tables into this query and the WHERE clause from your earlier query to filter the most recent visits down to those >60

This probably sounds confusing, so if you want, I can create the actual queries for you if you could zip and post a copy of your database (with any sensitive data removed)

[sandy budd]

I should have posted here first - I have been trying to do this on my own for three days and yall have helped me so much in just a few short hours.

RayMilhon Yours is working - it is returning all clients that have not had a visit in 60 days but it is asking me to enter the visit date when I run the report. If I do not enter anything when it prompts me, the visit date is left blank on the report. How do I correct this?

jzwp11 Thanks so much for your suggestion. I do have multiple records for each client with different visit dates. If you or Ray have a suggestion on how to show the visit date on the report without being prompted, then Ray's query should work just fine.

[jzwp11]

So the query runs without it prompting for a visit date but the report prompts for it? If so, that suggests that a control or something is spelled wrong on the report. BTW, did you rename the query that you created from Ray's suggestion? If so, did you update the Record Source for the report to that new query? Again you will have to verify that everything is spelled correctly.

[sandy budd]

You are both geniuses!!! Thank you SO VERY MUCH!!!! I changed the report field to match (maxvisitdate) in the query and it works now

[jzwp11]

You're welcome. Good luck with your project.