Today plus 5 years?

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a query that selects all records from a table but I want it to select
only records where the value of the field txtdatephotorecd is more than 5
years ago. I have tried putting this in the criteria for that field in the
query but it doesn't work.

=DateDiff("“yyyyâ€",Now(),[tblindividual].[txtlastphotorecd])-5

Can someone help? I've looked at the help but it doesn't give me an example
that seems to cover what I want to do.
Cheers
Tony
 
Tony said:
I have a query that selects all records from a table but I want it to select
only records where the value of the field txtdatephotorecd is more than 5
years ago. I have tried putting this in the criteria for that field in the
query but it doesn't work.

=DateDiff("“yyyy”",Now(),[tblindividual].[txtlastphotorecd])-5

Can someone help? I've looked at the help but it doesn't give me an example
that seems to cover what I want to do.
Click to expand...


Use:
DateAdd)"yyyy", -5, Date())
 
Hi Marshall. I assumed the first bracket should have been ( and not ) but it
still did not work. I have one record where the date is 01/01/92 and so it
should have found this but it didn't?
Any idea why?
Thanks
Tony

Marshall Barton said:
Tony said:
I have a query that selects all records from a table but I want it to select
only records where the value of the field txtdatephotorecd is more than 5
years ago. I have tried putting this in the criteria for that field in the
query but it doesn't work.

=DateDiff("“yyyyâ€",Now(),[tblindividual].[txtlastphotorecd])-5

Can someone help? I've looked at the help but it doesn't give me an example
that seems to cover what I want to do.
Click to expand...


Use:
DateAdd)"yyyy", -5, Date())
Click to expand...
 
Did you use the < operator? As in
txtlastphotorecd < DateAdd("yyyy", -5, Date())

Tony said:
Hi Marshall. I assumed the first bracket should have been ( and not )
but it still did not work. I have one record where the date is
01/01/92 and so it should have found this but it didn't?
Any idea why?
Thanks
Tony

Marshall Barton said:
Tony said:
I have a query that selects all records from a table but I want it
to select only records where the value of the field
txtdatephotorecd is more than 5 years ago. I have tried putting
this in the criteria for that field in the query but it doesn't
work.

=DateDiff(""yyyy"",Now(),[tblindividual].[txtlastphotorecd])-5

Can someone help? I've looked at the help but it doesn't give me an
example that seems to cover what I want to do.
Click to expand...


Use:
DateAdd)"yyyy", -5, Date())
Click to expand...
Click to expand...
 
Hi Bob. No I put the DateAdd definition in the criteria of the field
txtlastphotorecd.
Where would I have put your expression in the query?
Tony

Bob Barrows said:
Did you use the < operator? As in
txtlastphotorecd < DateAdd("yyyy", -5, Date())

Tony said:
Hi Marshall. I assumed the first bracket should have been ( and not )
but it still did not work. I have one record where the date is
01/01/92 and so it should have found this but it didn't?
Any idea why?
Thanks
Tony

Marshall Barton said:
Tony Williams wrote:

I have a query that selects all records from a table but I want it
to select only records where the value of the field
txtdatephotorecd is more than 5 years ago. I have tried putting
this in the criteria for that field in the query but it doesn't
work.

=DateDiff(""yyyy"",Now(),[tblindividual].[txtlastphotorecd])-5

Can someone help? I've looked at the help but it doesn't give me an
example that seems to cover what I want to do.


Use:
DateAdd)"yyyy", -5, Date())
Click to expand...
Click to expand...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
 
Here is the SQL for the query

SELECT [txtinitials] & " " & [txtsurname] AS [Full Name],
tblindividual.txtcurrent, tblindividual.txtidcardreq,
tblindividual.txtmemnumber, tblindividual.txtempmemnumber,
tblindividual.txtbusinessname, tblindividual.txtlastphotorecd
FROM tblindividual
GROUP BY [txtinitials] & " " & [txtsurname], tblindividual.txtcurrent,
tblindividual.txtidcardreq, tblindividual.txtmemnumber,
tblindividual.txtempmemnumber, tblindividual.txtbusinessname,
tblindividual.txtlastphotorecd
HAVING (((tblindividual.txtcurrent)=Yes) AND
((tblindividual.txtidcardreq)=Yes) AND
((tblindividual.txtlastphotorecd)=DateAdd("yyyy",-5,Date())));

Does that help?
Tony


Tony Williams said:
Hi Bob. No I put the DateAdd definition in the criteria of the field
txtlastphotorecd.
Where would I have put your expression in the query?
Tony

Bob Barrows said:
Did you use the < operator? As in
txtlastphotorecd < DateAdd("yyyy", -5, Date())

Tony said:
Hi Marshall. I assumed the first bracket should have been ( and not )
but it still did not work. I have one record where the date is
01/01/92 and so it should have found this but it didn't?
Any idea why?
Thanks
Tony

:

Tony Williams wrote:

I have a query that selects all records from a table but I want it
to select only records where the value of the field
txtdatephotorecd is more than 5 years ago. I have tried putting
this in the criteria for that field in the query but it doesn't
work.

=DateDiff(""yyyy"",Now(),[tblindividual].[txtlastphotorecd])-5

Can someone help? I've looked at the help but it doesn't give me an
example that seems to cover what I want to do.


Use:
DateAdd)"yyyy", -5, Date())
Click to expand...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
Click to expand...
 
Tony said:
((tblindividual.txtlastphotorecd)=DateAdd("yyyy",-5,Date())));
Click to expand...

Change that to

((tblindividual.txtlastphotorecd)<DateAdd("yyyy",-5,Date())));
 
Back
Top