add a year to date problem

  • Thread starter Thread starter AndyB
  • Start date Start date
A

AndyB

I am trying to add a year to a query of DOB from the Table 30log250 to be
compared against Table war_M2

I have tried different variations of WHERE
((([30LOG250].DOB)=[war_m2.dob]));
which works fine for an exact match but I thought if I added WHERE
((([30LOG250].DOB)=[war_m2.dob],"YYYY,+1));
I would get the next year so ie 12/27/84 and 12/27/85 would be a match.

It does not work. What am I missing

Andy Benjamin
 
AndyB said:
I am trying to add a year to a query of DOB from the Table 30log250 to be
compared against Table war_M2

I have tried different variations of WHERE
((([30LOG250].DOB)=[war_m2.dob]));
which works fine for an exact match but I thought if I added WHERE
((([30LOG250].DOB)=[war_m2.dob],"YYYY,+1));
I would get the next year so ie 12/27/84 and 12/27/85 would be a match.

It does not work. What am I missing
Click to expand...

Use DateAdd() function

30lOG250.DOB = DateAdd("yyyy", 1, war_m2.dob)
 
It works fine with one minor problem

when I want to close the window of the resulting query I get a message that
states "this action will reset the current code in break mode" and when I
click yes nothing happens and I have to go to design view to close it.
BTW my resulting query is.

SELECT [30LOG250].LAST, [30LOG250].FIRST, WAR_M2.OCA, [30LOG250].DOB
FROM 30LOG250 INNER JOIN WAR_M2 ON ([30LOG250].LAST = WAR_M2.LNAME) AND
([30LOG250].FIRST = WAR_M2.FNAME)
WHERE ((([30LOG250].DOB)=[war_m2.dob] Or
([30LOG250].DOB)=DateAdd("yyyy",2,[war_m2].[dob])));


Andy Benjamin

MGFoster said:
AndyB said:
I am trying to add a year to a query of DOB from the Table 30log250 to be
compared against Table war_M2

I have tried different variations of WHERE
((([30LOG250].DOB)=[war_m2.dob]));
which works fine for an exact match but I thought if I added WHERE
((([30LOG250].DOB)=[war_m2.dob],"YYYY,+1));
I would get the next year so ie 12/27/84 and 12/27/85 would be a match.

It does not work. What am I missing
Click to expand...

Use DateAdd() function

30lOG250.DOB = DateAdd("yyyy", 1, war_m2.dob)
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Compile & compact the db file. That error message usually occurs, when
you've stopped a VBA routine & then click a Cancel button or an End
button on a debug message box; or, try to change a VBA command or line
of code.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJafuoechKqOuFEgEQJlegCg+vNR5PVhd06fSt6pWnLXFJGt6/EAn1/Q
tj0rtbMJeCP+d8ULvTtr3I7A
=5vRX
-----END PGP SIGNATURE-----

It works fine with one minor problem

when I want to close the window of the resulting query I get a message that
states "this action will reset the current code in break mode" and when I
click yes nothing happens and I have to go to design view to close it.
BTW my resulting query is.

SELECT [30LOG250].LAST, [30LOG250].FIRST, WAR_M2.OCA, [30LOG250].DOB
FROM 30LOG250 INNER JOIN WAR_M2 ON ([30LOG250].LAST = WAR_M2.LNAME) AND
([30LOG250].FIRST = WAR_M2.FNAME)
WHERE ((([30LOG250].DOB)=[war_m2.dob] Or
([30LOG250].DOB)=DateAdd("yyyy",2,[war_m2].[dob])));


Andy Benjamin

AndyB wrote:

I am trying to add a year to a query of DOB from the Table 30log250 to
Click to expand...
be
compared against Table war_M2

I have tried different variations of WHERE
((([30LOG250].DOB)=[war_m2.dob]));
which works fine for an exact match but I thought if I added WHERE
((([30LOG250].DOB)=[war_m2.dob],"YYYY,+1));
I would get the next year so ie 12/27/84 and 12/27/85 would be a
Click to expand...
match.
It does not work. What am I missing
Click to expand...

Use DateAdd() function

30lOG250.DOB = DateAdd("yyyy", 1, war_m2.dob)
Click to expand...
Click to expand...
 
Thank you very much it worked.

Andy Benjamin

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Compile & compact the db file. That error message usually occurs, when
you've stopped a VBA routine & then click a Cancel button or an End
button on a debug message box; or, try to change a VBA command or line
of code.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJafuoechKqOuFEgEQJlegCg+vNR5PVhd06fSt6pWnLXFJGt6/EAn1/Q
tj0rtbMJeCP+d8ULvTtr3I7A
=5vRX
-----END PGP SIGNATURE-----

It works fine with one minor problem

when I want to close the window of the resulting query I get a message that
states "this action will reset the current code in break mode" and when I
click yes nothing happens and I have to go to design view to close it.
BTW my resulting query is.

SELECT [30LOG250].LAST, [30LOG250].FIRST, WAR_M2.OCA, [30LOG250].DOB
FROM 30LOG250 INNER JOIN WAR_M2 ON ([30LOG250].LAST = WAR_M2.LNAME) AND
([30LOG250].FIRST = WAR_M2.FNAME)
WHERE ((([30LOG250].DOB)=[war_m2.dob] Or
([30LOG250].DOB)=DateAdd("yyyy",2,[war_m2].[dob])));


Andy Benjamin

AndyB wrote:


I am trying to add a year to a query of DOB from the Table 30log250 to
be

compared against Table war_M2

I have tried different variations of WHERE
((([30LOG250].DOB)=[war_m2.dob]));
which works fine for an exact match but I thought if I added WHERE
((([30LOG250].DOB)=[war_m2.dob],"YYYY,+1));
I would get the next year so ie 12/27/84 and 12/27/85 would be a
match.

It does not work. What am I missing

Use DateAdd() function

30lOG250.DOB = DateAdd("yyyy", 1, war_m2.dob)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top