List dates 3 months ago

[Korncraig]

Hey

I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating

 

[boblarson]

In a query create a new field by putting this in:

IIf(DateAdd("m", 3, YourDateFieldHere) < Date,"Needs Meeting","No Meeting Necessary")

 

[Korncraig]

Thanks but

Thanks bob

But i need the clients who have not had a meeting arranged for over three months, if that makes any sence here is the question that was asked " Report lsiting all clients who have not had a meeting arranged for more than three months"

Thanks

Craig

 

[rainman89]

But i need the clients who have not had a meeting arranged for over three months,

that what he showed you.

do you have a field that had when their last meeting was? im assuming u do.
just enter that in the yourdatefield!

 

[Korncraig]

Yeah, i have put in a query their surname and meeting date, would i need to put that criteria in that date criteria box or just in the next empty space

sorry for acting dumb

 

[rainman89]

new field

IIf(DateAdd("m", 3, [Meeting Date]) < Date,"Needs Meeting","No Meeting Necessary")

 

[Korncraig]

Its now asking for a meeting date, all i need it to do is list the clients who have not had a meeting in the last 3 months

really really sorry if i'm starting to get on your nerv

thanks again

Craig

 

[boblarson]

If it is asking you for a parameter then you didn't type the right name of the field in where rainman put [Meeting Date]

replace [Meeting Date] with your actual field name.

 

[Korncraig]

Right my fieldname is called MeetingDate in the meetings table so i enterd the field name in wher it says to, but then this message came up

Data Type mismatch in criteria expression

sorry for being a complete noob

 

[boblarson]

Is your MeetingDate field, in the meetings table, really a date field or is it text?

 

[Korncraig]

IIf(DateAdd("m",3,[MeetingDate])<"Date","Needs Meeting","No Meeting Necessary")

this is what i am inserting

i should curl up into a ball and cry

 

[boblarson]

Take the quotes off of <Date as Date is a function not a string value.

 

[Korncraig]

Date/Time, Long time

 

[boblarson]

You don't want long time for this. Change it to ShortDate.

 

[Korncraig]

Done that but then it says the same message and puts quotes back on "Date"

 

[Korncraig]

Changed it to short date and input mask of 00/00/0000;0;_
but the ssame error message comes up

sorry for this stupid question

 

[boblarson]

The only thing I can say now is I think it's time you tried posting your database.

Go to Tools > Database Tools > Compact and Repair and then after that zip your database and then try posting here. It has to be 393Kb or smaller after compacting and then zipping for it to post here. If it winds up too big then I can give you my email address via PM to send it to me.

 

[Korncraig]

it's 552KB

could i send it via Messanger if at all possible mate

 

[rainman89]

delete the forms and tables that dont refer to your problem. then compact and repair then zip. should shrink it down!

 

[boblarson]

Okay, it's fixed. Check out the report and the query underlying the report for what I did. Sorry it took so long, but I had a meeting at work and then had to come home and fix dinner for my kids.

And just as a side note, you could have posted the database here as it was only 75Kb when you zipped and sent to me and it is only 59Kb after I compacted and zipped it.