DateDiff Select Statement

[Toni]

I have the below select statement to basically pull all records that have
data for a full year. The below statement works.

SELECT Member.[Store#], Member.StoreName, Member.TermDate,
SIVolume.VolumeDate, (DateDiff("yyyy",VolumeDate,TermDate)) AS YearDiff
FROM Member
INNER JOIN SIVolume ON Member.[Store#]=SIVolume.StoreNumber
WHERE ((((DateDiff("yyyy",VolumeDate,TermDate))) Is Null
Or ((DateDiff("yyyy",VolumeDate,TermDate)))>0))
ORDER BY Member.[Store#], SIVolume.VolumeDate;

We then realized that the company has an unusual fiscal year. Starts in Nov
and ends in Oct.

Example Data

Store Volume volumeDate TermDate (in a different table)
100 $500.00 10/31/05 10/15/06
100 $700.00 10/31/06

Because we want a full year worth of data the data for store 100 should
appear for 2005 only.

Another Example
Store Volume volumeDate TermDate (in a different table)
999 $300.00 10/31/05 11/13/06
999 $400.00 10/31/06

Data for store 999 should show both 2005 and 2006 data, because 11/13/06 is
part of FY07.

I tried adding 2 months to the termDate and compare that date to volumeDate.
Below is what I have tried to do but was unsuccessful, it now prompts me for
FYTermDate.

SELECT Member.[Store#], Member.StoreName, Member.TermDate,
SIVolume.VolumeDate, (Format(DateAdd("m",+2,TermDate))) AS FYTermDate,
(DateDiff("yyyy",VolumeDate,FYTermDate)) AS YearDiff
FROM Member
INNER JOIN SIVolume ON Member.[Store#] = SIVolume.StoreNumber
WHERE ((((DateDiff("yyyy",[VolumeDate],[FYTermDate]))) Is Null
Or ((DateDiff("yyyy",[VolumeDate],[FYTermDate])))>0))
ORDER BY Member.[Store#], SIVolume.VolumeDate;

Any help would be greatly appreciated
Tsharp

 

[Toni]

THANK YOU. Thanks for explaining it and showing me.
Thsarp

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

The Format() function returns a string. You want a date, so you need to
remove the Format() function from the DateAdd() of the FYTermDate
definition.

Sometimes Access doesn't recognize column aliases (what you're using in
the YearDiff definition), so you'll need to put the complete definition
in place of the alias. E.g.:


DateAdd("m",+2,TermDate) AS FYTermDate,
DateDiff("yyyy",VolumeDate,DateAdd("m",+2,TermDate)) AS YearDiff

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSRoh9YechKqOuFEgEQK5CgCg3aqGk7nWtTrAzdMBfLorc+lQi30An2GU
JwN8SGD3Xogt0xe4vi74UxTW
=tbgU
-----END PGP SIGNATURE-----

I have the below select statement to basically pull all records that have
data for a full year. The below statement works.

SELECT Member.[Store#], Member.StoreName, Member.TermDate,
SIVolume.VolumeDate, (DateDiff("yyyy",VolumeDate,TermDate)) AS YearDiff
FROM Member
INNER JOIN SIVolume ON Member.[Store#]=SIVolume.StoreNumber
WHERE ((((DateDiff("yyyy",VolumeDate,TermDate))) Is Null
Or ((DateDiff("yyyy",VolumeDate,TermDate)))>0))
ORDER BY Member.[Store#], SIVolume.VolumeDate;

We then realized that the company has an unusual fiscal year. Starts in Nov
and ends in Oct.

Example Data

Store Volume volumeDate TermDate (in a different table)
100 $500.00 10/31/05 10/15/06
100 $700.00 10/31/06

Because we want a full year worth of data the data for store 100 should
appear for 2005 only.

Another Example
Store Volume volumeDate TermDate (in a different table)
999 $300.00 10/31/05 11/13/06
999 $400.00 10/31/06

Data for store 999 should show both 2005 and 2006 data, because 11/13/06 is
part of FY07.

I tried adding 2 months to the termDate and compare that date to volumeDate.
Below is what I have tried to do but was unsuccessful, it now prompts me for
FYTermDate.

SELECT Member.[Store#], Member.StoreName, Member.TermDate,
SIVolume.VolumeDate, (Format(DateAdd("m",+2,TermDate))) AS FYTermDate,
(DateDiff("yyyy",VolumeDate,FYTermDate)) AS YearDiff
FROM Member
INNER JOIN SIVolume ON Member.[Store#] = SIVolume.StoreNumber
WHERE ((((DateDiff("yyyy",[VolumeDate],[FYTermDate]))) Is Null
Or ((DateDiff("yyyy",[VolumeDate],[FYTermDate])))>0))
ORDER BY Member.[Store#], SIVolume.VolumeDate;

Any help would be greatly appreciated
Tsharp