Avatar of Soupbone79
Soupbone79

asked on 

Help with start date and end date access query

I have a database I built that I need help with one part. Below I have two union queries one that does a one year, the second that does three year.  The way I did them I have to put one and three years before dates to give me the future date. Here is a example for a one year test that I want to know what is due for December 2014 I have to put in a start date of 12/01/13 and end date 12/31/13. Here is the example for the three year test that I want to know what is due for December 2014 I have to put in a start date of 12/01/11 and end date 12/31/11. What I want is to be able to put in 12/01/14 as start and 12/31/14 as end and get the same results. But also combine both union queries into one query.
union 1 year test Query
SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("d",+365,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE ((([testing records Table].LD) Between [startdate] And [enddate]))
UNION ALL
SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("d",+365,[Line]) AS [Test Due Date]
FROM [testing records Table]
WHERE ((([testing records Table].Line) Between [startdate] And [enddate]))
UNION ALL SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("d",+365,[Sump]) AS [Test Due Date]
FROM [testing records Table]
WHERE ((([testing records Table].Sump) Between [startdate] And [enddate]));

union 3 year test Query
SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("d",+1095,[IC]) AS [Test Due Date]
FROM [testing records Table]
WHERE ((([testing records Table].IC) Between [startdate] And [enddate]))
UNION ALL
SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("d",+1095,[Spill]) AS [Test Due Date]
FROM [testing records Table]
WHERE ((([testing records Table].Spill) Between [startdate] And [enddate]))
UNION ALL SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("d",+1095,[StiP3]) AS [Test Due Date]
FROM [testing records Table]
WHERE ((([testing records Table].StiP3) Between [startdate] And [enddate]));
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon