Today plus 5 years?

[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

 

[Marshall Barton]

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())

 

[Tony Williams]

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

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())

 

[Bob Barrows [MVP]]

Did you use the < operator? As in
txtlastphotorecd < 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

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())

 

[Tony Williams]

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

Did you use the < operator? As in
txtlastphotorecd < 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

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())

--
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.

 

[Tony Williams]

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

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

Did you use the < operator? As in
txtlastphotorecd < 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

:

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())

--
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.

 

[Bob Barrows [MVP]]

((tblindividual.txtlastphotorecd)=DateAdd("yyyy",-5,Date())));

Change that to

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

 

[Marshall Barton]

Put this in the criteria:
< DateAdd("yyyy", -5, Date())

 

[Tony Williams]

Thanks Marshall that did it!!!!!!
Tony

Put this in the criteria:
< DateAdd("yyyy", -5, Date())
--
Marsh
MVP [MS Access]

Where would I have put your expression in the query?