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])
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])
FROM [testing records Table]
WHERE ((([testing records Table].StiP3) Between [startdate] And [enddate]));
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])
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])
FROM [testing records Table]
WHERE ((([testing records Table].StiP3) Between [startdate] And [enddate]));
As you wish to combine LD, Line, Sump, IC, Spill, and Stip3 into one field, [Test Due Date], you will need all the partial queries combined in one union query using the where criteria as now.
However, as Pat shows, exchange the day counts with full year counts with DateAdd("yyyy", n, DateField)
/gustav
However, as Pat shows, exchange the day counts with full year counts with DateAdd("yyyy", n, DateField)
/gustav
ASKER
Sorry I copyed and pasted into new sql view query and I get this error
invalid SQL staement;expected 'DELETE','INSERT','SELECT'
invalid SQL staement;expected 'DELETE','INSERT','SELECT'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok if I put in 12/01/14 as start and 12/31/14 it pulls not data, but if I put 12/01/13 and 12/31/13 it does.
when they input data into the table they always do it when the job is done.
when they input data into the table they always do it when the job is done.
ASKER
I want to be able to put start and end in the future
That would indicate that your datefields contain data from 2010 and/or 2012 but none from 2011 or 2013.
Or you may have to insert this line as the very first line in your query:
Parameters [startdate] Date, [enddate] Date;
/gustav
Or you may have to insert this line as the very first line in your query:
Parameters [startdate] Date, [enddate] Date;
/gustav
> I want to be able to put start and end in the future
Nothing stops you from that. It will not change the data in the table.
/gustav
Nothing stops you from that. It will not change the data in the table.
/gustav
Correction:
Parameters [startdate] DateTime, [enddate] DateTime;
/gustav
Parameters [startdate] DateTime, [enddate] DateTime;
/gustav
ASKER
I am sorry for being so dense this is what I have now
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE [LD] Between [startdate] And [enddate];
I tried
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE [LD] Between [startdate] DateTime, [enddate] DateTime;
syntax error (missing operator)
and also
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE [LD] Parameters [startdate] DateTime, [enddate] DateTime;
syntax error (missing operator)
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE [LD] Between [startdate] And [enddate];
I tried
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE [LD] Between [startdate] DateTime, [enddate] DateTime;
syntax error (missing operator)
and also
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE [LD] Parameters [startdate] DateTime, [enddate] DateTime;
syntax error (missing operator)
It is as the first line:
Parameters [startdate] DateTime, [enddate] DateTime;
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE [LD] Between [startdate] And [enddate];
/gustav
Parameters [startdate] DateTime, [enddate] DateTime;
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE [LD] Between [startdate] And [enddate];
/gustav
ASKER
ok I copied and pasted all and it still will not pull data if I put start 12/01/14 and end 12/31/14 but if I put start 12/01/13 and end 12/31/13 I get data
Then I assume you have no data for 2013.
Remove the Where clause and you'll see.
/gustav
Remove the Where clause and you'll see.
/gustav
ASKER
yes for the month of December I have 44 records
ASKER
your sql code works well if I put in 12/01/2013 and 12/31/2013 not 12/01/2014 and 12/31/2014
but mine sql code does the same thing
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]));
but mine sql code does the same thing
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]));
What is the data type of the field LD?
/gustav
/gustav
ASKER
Date/Time
format m/d/yyyy
not indexed
format m/d/yyyy
not indexed
ASKER
I could send you the whole database if you like I could just put dummy info in it.
That would be fine. Something is strange.
/gustav
/gustav
ASKER
ok here you go
You need a second try ...
/gustav
/gustav
ASKER
here you go
Testing2.accdb
Testing2.accdb
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
with this code nothing else I get
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE DateAdd("yyyy", 1, [LD]) Between [startdate] AND, [enddate];
Syntax error
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE DateAdd("yyyy", 1, [LD]) Between [startdate] AND, [enddate];
Syntax error
ASKER
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE DateAdd("yyyy", 1, [LD]) Between [startdate] AND, [enddate];
the comma behind the AND was wrong so if I did it this way
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE DateAdd("yyyy", 1, [LD]) Between [startdate] AND [enddate];
no syntax error but it give me old dates too 2002 to 2013
FROM [testing records Table]
WHERE DateAdd("yyyy", 1, [LD]) Between [startdate] AND, [enddate];
the comma behind the AND was wrong so if I did it this way
SELECT [Company Name], [Store Name], Contact, Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE DateAdd("yyyy", 1, [LD]) Between [startdate] AND [enddate];
no syntax error but it give me old dates too 2002 to 2013
ASKER
I even tried this
SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE ((([testing records Table].LD) Between [startdate] And [enddate]));
doesn't work on 12/01/2014 12/31/2014 but does work on 12/01/2013 12/31/2013
SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("yyyy",1,[LD]) AS [Test Due Date]
FROM [testing records Table]
WHERE ((([testing records Table].LD) Between [startdate] And [enddate]));
doesn't work on 12/01/2014 12/31/2014 but does work on 12/01/2013 12/31/2013
ASKER
were you able to download the testing2 database?
There is an incompatibility between this website and certain browsers that prevents the downloading of .accdb file types. Whenever you post a database to any forum, it is considerate to compact it first, then zip it before uploading to minimize it's size.
ASKER
ok here you go again
Testing2.zip
Testing2.zip
Sorry, I mistyped the where clause. The query should read:
< first select query >
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 1, [LD]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [LD]) Between [startdate] And [enddate]
UNION ALL
< second select query and so on ... for a total of six select queries >
The first query now returns 18 records.
/gustav
< first select query >
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 1, [LD]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [LD]) Between [startdate] And [enddate]
UNION ALL
< second select query and so on ... for a total of six select queries >
The first query now returns 18 records.
/gustav
I guess I don't understand the problem. I just copied this query from your database and with the modification I suggested, it returned one row. It looks like Gus came to the same conclusion.
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 (((DateAdd("yyyy",1,[LD]))
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 (((DateAdd("yyyy",1,[LD]))
ASKER
ok this is what I did and now I get 3 records I should get 6, It drops at line what did I do wrong?
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 3, [Spill]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Spill]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 1, [Sump]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [Sump]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 1, [LD]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [LD]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 1, [Line]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [Line]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 3, [CP]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy",31, [CP]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 3, [Stip3]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Stip3]) Between [startdate] And [enddate]
UNION ALL SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 3, [IC]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [IC]) Between [startdate] And [enddate];
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 3, [Spill]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Spill]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 1, [Sump]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [Sump]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 1, [LD]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [LD]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 1, [Line]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [Line]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 3, [CP]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy",31, [CP]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 3, [Stip3]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Stip3]) Between [startdate] And [enddate]
UNION ALL SELECT
[Company Name],
[Store Name],
Contact,
Comment,
DateAdd("yyyy", 3, [IC]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [IC]) Between [startdate] And [enddate];
A typo here. It's 3:
DateAdd("yyyy",31, [CP]) Between [startdate] And [enddate]
It returns many records for me. Which startdate/enddate do you use?
/gustav
DateAdd("yyyy",31, [CP]) Between [startdate] And [enddate]
It returns many records for me. Which startdate/enddate do you use?
/gustav
ASKER
Ok I fixed the type O of 31 to 3 and it did the same thing. Please download database again and look at query 2. This is what I have found out if I change in table the dates to the third day of the month or less they all work. As soon as I change to 4th day of the month it quits. I am putting start at 12/01/2014 and end at 12/31/2014. Please try it and see what you think.
Testing2-new.zip
Testing2-new.zip
I guess you miss the top line:
PARAMETERS [startdate] DateTime, [enddate] DateTime;
SELECT ...
It makes quite a difference here.
/gustav
PARAMETERS [startdate] DateTime, [enddate] DateTime;
SELECT ...
It makes quite a difference here.
/gustav
ASKER
Ok Great! Thank you so much! All of you are awesome! Now I thought of one more thing to add to query 2 how about showing each of the data types in the query also (Spill, Sump, LD, Line, CP, StiP3 and IC) if you would that help as well.
As you are using the same table throughout, that would be nothing more than to include these fields.
Along the line:
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
Spill,
Sump,
LD,
Line,
CP,
StiP3,
IC,
DateAdd("yyyy", 3, [Stip3]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Stip3]) Between [startdate] And [enddate]
/gustav
Along the line:
SELECT
[Company Name],
[Store Name],
Contact,
Comment,
Spill,
Sump,
LD,
Line,
CP,
StiP3,
IC,
DateAdd("yyyy", 3, [Stip3]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Stip3]) Between [startdate] And [enddate]
/gustav
Again - you don't need the union. You can use a compound Where clause as I showed in my first example.
That wouldn't give you the common field [Test Due Date].
/gustav
/gustav
ASKER
Ok but it would tell what test is do
Yes. Also, you will get only one record no matter which field matches.
/gustav
/gustav
ASKER
Ok but it would tell what test is do
ASKER
sorry posted it twice. I am sure client may what that any other idea
ASKER
this below works but it want give me the dates right. all I did was put the check box on the CP query to show
SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("d",+1095,[CP]) AS [Test Due Date], [testing records Table].CP
FROM [testing records Table]
WHERE ((([testing records Table].CP) Between [startdate] And [enddate]));
SELECT [testing records Table].[Company Name], [testing records Table].[Store Name], [testing records Table].Contact, [testing records Table].Comment, DateAdd("d",+1095,[CP]) AS [Test Due Date], [testing records Table].CP
FROM [testing records Table]
WHERE ((([testing records Table].CP) Between [startdate] And [enddate]));
ASKER
on that you have to put in 12/01/2011 12/31/2011 to get data
ASKER
maybe a where statement to the data type as a test type?
ASKER
would a new table be needed for test types?
ASKER
with a relationship between
> this below works but it want give me the dates right. all I did was put the check box on the CP query to show
No, you reverted to the old code that didn't work.
Use the recent SQL that works.
/gustav
No, you reverted to the old code that didn't work.
Use the recent SQL that works.
/gustav
ASKER
I know I was just giving the example
But that brings a lot of confusion.
All I know is that you the solution for your original question.
/gustav
All I know is that you the solution for your original question.
/gustav
ASKER
Ok I think I have got it. I uploaded changes take a look and run qury 2. I put in a new data type and called it TestType added code and it works great!
code is
PARAMETERS [startdate] DateTime, [enddate] DateTime;
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 3, [Spill]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Spill]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 1, [Sump]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [Sump]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 1, [LD]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [LD]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 1, [Line]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [Line]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 3, [CP]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy",3, [CP]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 3, [Stip3]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Stip3]) Between [startdate] And [enddate]
UNION ALL SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 3, [IC]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [IC]) Between [startdate] And [enddate];
Testing2--2-.zip
code is
PARAMETERS [startdate] DateTime, [enddate] DateTime;
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 3, [Spill]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Spill]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 1, [Sump]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [Sump]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 1, [LD]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [LD]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 1, [Line]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 1, [Line]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 3, [CP]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy",3, [CP]) Between [startdate] And [enddate]
UNION ALL
SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 3, [Stip3]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [Stip3]) Between [startdate] And [enddate]
UNION ALL SELECT
[Company Name],
[Store Name],
[TestType],
Contact,
Comment,
DateAdd("yyyy", 3, [IC]) AS [Test Due Date]
FROM
[testing records Table]
WHERE
DateAdd("yyyy", 3, [IC]) Between [startdate] And [enddate];
Testing2--2-.zip
ASKER
I have been a member since 2007 and I had let my experts-exchange almost expire and thought it wasn’t worth the money for what I had paid for because last time I asked a question about a superscope issue no one responded. But I decided to give it one more chance and I am glad I did! Thank you so much for your help!
You are welcome!
/gustav
/gustav
WHERE DateAdd("yyyy",1, [testing records Table].IC) Between [startdate] And [enddate]
OR DateAdd("yyyy", 1, [testing records Table].Line) Between [startdate] And [enddate]
OR DateAdd("yyyy", 1, [testing records Table].Sump) Between [startdate] And [enddate]
WHERE DateAdd("yyyy",3, [testing records Table].IC) Between [startdate] And [enddate]
OR DateAdd("yyyy", 3, [testing records Table].Spill) Between [startdate] And [enddate]
OR DateAdd("yyyy", 3, [testing records Table].StiP3) Between [startdate] And [enddate]