Date Filter & Query

[Guest]

B4 I start I want to thank all of you that spend time answering all the
questions we New useres come up with. I have several dates in a table in a
field called "Date". I have an expression on another field that will tell me
what the date will be 90 days from that date. =DateAdd("y",+90,
[tblDate].[Date]). No problem so far. I want to be able to find out on a
daily basis which customers have not been back in the 90 day period. I set up
a Make Table Query that I can run every day or whenever, to filter records
just for the customers that I haven't seen in 90 days. I can't get the
expression just right or know what is the best way to get the results. I set
up a filter Query to run "on open" (using a marco), for the form I have
associated with the Make Table Query and tried a variety of expressions in
the criteria area under the "date+90 Field", with no luck. It would probally
be best to just write code for the "on open" for the form. Would that be
best? Would I use the "date" and add 90 days in the code then filter that
out, or use the "date+90" field and use that to set up the filter? I hope all
this isn't too confusing. Any help with the VBC would be greatly
appreciated....Thanks lyndac

 

[Douglas J. Steele]

First, rename your Date field: Date is a reserved word, and using it for
your own purposes will usually result in problems.

Let's assume you renamed it to LastVisitDate.

You should then be able to put < DateAdd("y", -90, Date()) or even <
Date() - 90 to get those records where the customer hasn't been visited for
90 days.

 

[Guest]

You do not need a make table query. Use this SQL for your query.

SELECT YourTable.Contact, Max(YourTable.YourDate) AS MaxOfYourDate
FROM YourTable
WHERE (((YourTable.YourDate)<Date()-90))
GROUP BY YourTable.Contactt;

 

[Guest]

Thank you Doug & Karl for answereing my post. I'm still having a problem. I
tried both your methods and got similar results. Both your responses are
saying basically the same thing so it's probally me. First, both of you used
"date()" is that an expession for the current day or is it use differently in
your examples? The reason I ask is on the many dates I use to add the 90 days
to, they are based on dates of invoices so every day they change, therefore
changing the results I'm looking for. Does that make a difference?
Doug, What I did with your expressions was to create a query base on a
couple of tables, and produced a form. In that query I have my exp for the
+90 days and that produces a field on my form which is Date+90. (I did by the
way changed by field names and took out the "Date" only. I forgot that that
could cause probs thanks) I entered your expression as the criteria for the
+90 field. with the ">DateAdd" I got everything, with <DateAdd" I got
nothing. I wonder where it is in the same column as my +90 expression if that
would cause a problem. If so where should I have put it?
Karl, I used your SQL in a Select Query I generated. In the SQL language
section I put in information you gave in. What I got in the coulmns of the
query was 2 expressions, 1 group by customer and 1 where clause. I believe
that to be what you expected (best Guess), but it showed all records. It
didn't filter out any. For giggles I changed < to > and got the same thing,
all records. I used the select query as a filter name on the filter section
of the form Properties. I hope that's where you wanted me to put
it.....Thanks both of you....Lyndac

B4 I start I want to thank all of you that spend time answering all the
questions we New useres come up with. I have several dates in a table in a
field called "Date". I have an expression on another field that will tell me
what the date will be 90 days from that date. =DateAdd("y",+90,
[tblDate].[Date]). No problem so far. I want to be able to find out on a
daily basis which customers have not been back in the 90 day period. I set up
a Make Table Query that I can run every day or whenever, to filter records
just for the customers that I haven't seen in 90 days. I can't get the
expression just right or know what is the best way to get the results. I set
up a filter Query to run "on open" (using a marco), for the form I have
associated with the Make Table Query and tried a variety of expressions in
the criteria area under the "date+90 Field", with no luck. It would probally
be best to just write code for the "on open" for the form. Would that be
best? Would I use the "date" and add 90 days in the code then filter that
out, or use the "date+90" field and use that to set up the filter? I hope all
this isn't too confusing. Any help with the VBC would be greatly
appreciated....Thanks lyndac