2nd Post - Pull up a report based on date 6 months ago....

[Danielle17]

I posted a while back and got a couple different suggestions on what to do but they only solved part of the problem....I have to be able to pull up a report based on 2 things...One: is that the "Status" field must say "Registered Lead", Second: is that the "Date Entered" field must be dated 6 months ago or further back...So for instance the date must be 2/15/01, 6 months from today...Here is the code that I'm using in the query...

SELECT [Customer and Lead Listing].CustomerID, [Customer and Lead Listing].CompanyName, [Customer and Lead Listing].ContactName, [Customer and Lead Listing].City, [Customer and Lead Listing].State, [Customer and Lead Listing].Representative, [Customer and Lead Listing].Status, [Customer and Lead Listing].[Date Entered], [Customer and Lead Listing].CalHouse
FROM [Customer and Lead Listing]
WHERE ((([Customer and Lead Listing].Status)=&quot;Registered Lead&quot AND ((([Customer and Lead Listing].[Date Entered])=[Date Entered]) AND [Date Entered]<DateAdd(&quot;m&quot;,-6,Date())));

The results I'm getting are for the year before, The dates range from 3/5/00 to 12/22/00. If I take the &quot;-&quot; (before the 6) out then I get dates ranging from 3/5/00 to 8/6/01(the most recent)...I'm not sure why it won't take all of the records from 2/15/01 and before(which in this table would be 12/22/00)

I'm not sure what is wrong with it...Any suggestions would be greatly appreciated

 

[JoeMiller]

It appears to me that you want data that is 6 months less than the date the user enters into the parameter for [Date Entered], if this is true then change the parameter under the [Date Entered] field to this:

<DateAdd(&quot;m&quot;,-6,[Enter A Date])

I change your parameter of [Date Entered] to [Enter A Date] because the Access might be confused by using a parameter the same as the field name.

HTH

Joe Miller
joe.miller@flotech.net

 

[Danielle17]

I would like it to read the Date() so that the user doesn't have to enter a date...they just have to click the command button and it will automatically read the current date...

I just added a textbox that is not visible onto the form that will automatically read the current date...So the new code looks like <DateAdd(&quot;m&quot;,-6,[HiddenDate])...It's still not displaying the current records though....I think that the &quot;-&quot; takes a year off...for example when the &quot;-&quot; sign is in the code the only dates displayed are from 2000...it looks like it displays all of the dates for 2000...I'm not sure what other code I could use....any other suggestions? :-I

 

[tempclerk]

Have you tried simply <Date()-180? or did you specifically need to go by calendar month?

 

[JoeMiller]

If you want the query to return all records GREATER than the date 6 months ago, meaning from today back 6 months, you need to change your sign on the criteria from < to >. Then your original equation would work.

>DateAdd(&quot;m&quot;,-6,Date())

HTH
Joe Miller
joe.miller@flotech.net

 

[collyann]

Why do you have ((([Customer and Lead Listing].[Date Entered])=[Date Entered]) this in your code and not just [Date Entered] = <Date()-180.

 

[Danielle17]

I DON'T need the dates from, let's say today back to 2/15/01...only the dates before that....

The ((([Customer and Lead Listing].[Date Entered])=[Date Entered]) had already been deleted...so it looks like this:

SELECT [Customer and Lead Listing].CustomerID, [Customer and Lead Listing].CompanyName, [Customer and Lead Listing].ContactName, [Customer and Lead Listing].City, [Customer and Lead Listing].State, [Customer and Lead Listing].Representative, [Customer and Lead Listing].Status, [Customer and Lead Listing].[Date Entered], [Customer and Lead Listing].CalHouse
FROM [Customer and Lead Listing]
WHERE ((([Customer and Lead Listing].Status)=&quot;Registered Lead&quot AND (([Customer and Lead Listing].[Date Entered])<DateAdd(&quot;m&quot;,-6,[HiddenDate])));

The main purpose of this report is so that a Sales person can look and see if a Registered Lead has expired...once a lead is registered it as 6 months to become active, if after 6 months then it expires....

 

[JoeMiller]

This using today's date (recommended):

SELECT [Customer and Lead Listing].CustomerID, [Customer and Lead Listing].CompanyName, [Customer and Lead Listing].ContactName, [Customer and Lead Listing].City, [Customer and Lead Listing].State, [Customer and Lead Listing].Representative, [Customer and Lead Listing].Status, [Customer and Lead Listing].[Date Entered], [Customer and Lead Listing].CalHouse
FROM [Customer and Lead Listing]
WHERE ((([Customer and Lead Listing].Status)=&quot;Registered Lead&quot AND (([Customer and Lead Listing].[Date Entered])<DateAdd(&quot;m&quot;,-6,Date())));

Or this using your form (not recommended):

SELECT [Customer and Lead Listing].CustomerID, [Customer and Lead Listing].CompanyName, [Customer and Lead Listing].ContactName, [Customer and Lead Listing].City, [Customer and Lead Listing].State, [Customer and Lead Listing].Representative, [Customer and Lead Listing].Status, [Customer and Lead Listing].[Date Entered], [Customer and Lead Listing].CalHouse
FROM [Customer and Lead Listing]
WHERE ((([Customer and Lead Listing].Status)=&quot;Registered Lead&quot AND (([Customer and Lead Listing].[Date Entered])<DateAdd(&quot;m&quot;,-6,[Forms]![MyFormName]![HiddenDate])));

HTH Joe Miller
joe.miller@flotech.net

 

[Danielle17]

If I use either one of them they don't work...I haven't been able to pull up the correct dates...It has to be something with the DateAdd(&quot;m&quot;.....thing....even if I use the Date() thing in the query it pulls up dates that are not from 6 months and before...the &quot;-&quot; pulls up all of the dates for the year before....I'm starting to think this is a lost cause........

 

[JoeMiller]

Can you send me a sample of the db so I can have a look?

Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

Are you using Standard (U.S.) date format? If not then many of the date functions need to have &quot;special&quot; handling.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Danielle17]

I FIGURED IT OUT! I was soooooo blind....I thought that the dates were wrong but actually there are no dates for feb or jan...so by getting the dec. dates and back it was correct...the next date after 12/22/00 is 3/5/01...which in 3 weeks will be 6 months back and then will be on the report.....DUHHHH!!! (-: LOL
Thanks for all the help and patience!