dates

[Guest]

I have a query with a date field. I do want to see all the dates when one of them is not older than 6 months from today and at the same time in the date field I do not want to see the dates when they are older than 6 months. Please help.

 

[Douglas J. Steele]

Add a WHERE clause WHERE MyDate > DateAdd("m", -6, Date())

(replace MyDate with the name of your date field)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have a query with a date field. I do want to see all the dates when one

of them is not older than 6 months from today and at the same time in the
date field I do not want to see the dates when they are older than 6 months.
Please help.

 

[John Spencer (MVP)]

Obviously we are missing some information from you. I think what you might be
saying is that for some entity you want to see the entity and all its dates if
any one date for the entity exists in the period between today and 6 months prior.

If that is not the case, possibly you can post a short example of data and what
you want to see returned.

Example:

SELECT CompanyName, EventDate
FROM Table
WHERE CompanyName IN
(SELECT T.CompanyName
FROM Table as T
WHERE T.EventDate >= DateAdd("m",-6,Date())

 

[Guest]

First of all I need to apologize for the confusion. This is what I have; date1: 01/01/03, date2: 02/03/03 and date3: 12/12/03. I want all these dates to show up because date3 is less than 6 months old. When date1, date2 and date3 are older than 6 months I do not want them to show up.

 

[John Spencer (MVP)]

So, you have 3 date fields in one record. Correct? If that understanding is
incorrect then ignore the remainder of this post.

And if any one of the three date fields is within 6 months of today's date (or
some specified date) you want the record to show up in your query results.

Or do you mean that you want all the records to be in the query results but show
or not show the date fields? If this is your desire, it can't be done in a
query. You could do it in a report by using some VBA code to show/hide the
associated controls and move them as appropriate. You could also do this on a
Single form (but not a continous form).

SELECT Date1, Date2, Date3
FROM YourTable
WHERE Date1 > DateAdd("m",-6,Date())
OR Date2 > DateAdd("m",-6,Date())
OR Date3 > DateAdd("m",-6,Date())

If you were doing this in the query grid, put the criteria under each date
field, but on separate lines of the criteria area.

So
Date1 would have > DateAdd("m",-6,Date()) as criteria on line 1
Date2 would have > DateAdd("m",-6,Date()) as criteria on line 2
Date3 would have > DateAdd("m",-6,Date()) as criteria on line 3

 

[Guest]

I'm sorry, no I do not have 3 date field in one record. I have as an example, 3 records in one date field. One record is 02/02/03, the other one is 05/05/03 and the third one is 10/05/03. I want to see all three records because the last record is less than 6 months old. I have no problem in getting those records. The problem is that when all 3 records are more than 6 months old, I do not want to see them. I thought that I needed to use a criteria like "Between DateAdd("m",-6,date()) And Date() Is Null", however that doesn't seem to work.

 

[John Spencer (MVP)]

Ok, I surrender.

I have not been able to figure out what you want or how your data is structured.
I gave you an example earlier that I thought met your needs. That was

SELECT CompanyName, EventDate
FROM Table
WHERE CompanyName IN
(SELECT T.CompanyName
FROM Table as T
WHERE T.EventDate >= DateAdd("m",-6,Date())

If all you have is the dates then change that to

SELECT EventDate
FROM Table
WHERE Exists
(SELECT T.EventDate
FROM Table as T
WHERE T.EventDate >= DateAdd("m",-6,Date())

That will return EVERY record in the table if at least one record in the table
meets the criteria.

 

[John Spencer (MVP)]

Also, since you seem to be running this same problem with John Vinson. I quit.
You've been two-timing me.

In other words, please don't run two simultaneous threads on the same problem.
We're all volunteers and normally have limited time to help. You are taking up
the time of two people - when one should do it. And that may mean that someone
else gets no help. I hope John can give you a solution. He is one of the many
highly-qualified volunteers in this forum.

Good luck.

 

[Tom Ellison]

John,

Not to worry. Just this once I was taking care of the guy who
otherwise would have received no help. But let's not have that happen
again! <g>

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

I owe everybody that is trying to help me out an apology. It was never my intention to take up unneccesary time of you guys. This is the first time I've ever used the discussion group and am not familiar with the protocol. Again I'm sorry and I ensure you that this will not happen again. Hopefully you are willing to help me in the future.

 

[Tom Ellison]

Dear Bert:

Hope you can take a tiny bit of good natured ribbing. However, with
respect to ever helping you again, I'm sorry to say you're being
suspended. No more assistance for at least 2 minutes.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Spencer (MVP)]

No problem. I was trying to point out with humor that you really need to keep
your problem in one thread. Instead of two or three.

Since your offence was accidental and you aplogized, I'll impose the same
penalty - no more help for 2 minutes.