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
PatHartman

You don't need a union query

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]
Gustav Brock

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
Soupbone79

ASKER
Sorry I copyed and pasted into new sql view query and I get this error
invalid SQL staement;expected 'DELETE','INSERT','SELECT', or 'UDATE'.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Soupbone79

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

ASKER
I want to be able to put start and end in the future
Gustav Brock

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

> 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
Gustav Brock

Correction:

Parameters [startdate] DateTime, [enddate] DateTime;

/gustav
Soupbone79

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)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gustav Brock

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
Soupbone79

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
Gustav Brock

Then I assume you have no data for 2013.
Remove the Where clause and you'll see.

/gustav
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Soupbone79

ASKER
yes for the month of December I have 44 records
Soupbone79

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]));
Gustav Brock

What is the data type of the field LD?

/gustav
Your help has saved me hundreds of hours of internet surfing.
fblack61
Soupbone79

ASKER
Date/Time
format m/d/yyyy

not indexed
Soupbone79

ASKER
I could send you the whole database if you like I could just put dummy info in it.
Gustav Brock

That would be fine. Something is strange.

/gustav
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Soupbone79

ASKER
ok here you go
Gustav Brock

You need a second try ...

/gustav
Soupbone79

ASKER
here you go
Testing2.accdb
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Soupbone79

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
Soupbone79

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
Soupbone79

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Soupbone79

ASKER
were you able to download the testing2 database?
PatHartman

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

ASKER
ok here you go again
Testing2.zip
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gustav Brock

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
PatHartman

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])) Between [startdate] And [enddate]));
Soupbone79

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];
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

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
Soupbone79

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
Gustav Brock

I guess you miss the top line:

PARAMETERS [startdate] DateTime, [enddate] DateTime;
SELECT ...

It makes quite a difference here.

/gustav
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Soupbone79

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

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
PatHartman

Again - you don't need the union.  You can use a compound Where clause as I showed in my first example.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

That wouldn't give you the common field [Test Due Date].

/gustav
Soupbone79

ASKER
Ok but it would tell what test is do
Gustav Brock

Yes. Also, you will get only one record no matter which field matches.

/gustav
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Soupbone79

ASKER
Ok but it would tell what test is do
Soupbone79

ASKER
sorry posted it twice. I am sure client may what that any other idea
Soupbone79

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]));
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Soupbone79

ASKER
on that you have to put in 12/01/2011 12/31/2011 to get data
Soupbone79

ASKER
maybe a where statement to the data type as a test type?
Soupbone79

ASKER
would a new table be needed for test types?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Soupbone79

ASKER
with a relationship between
Gustav Brock

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

ASKER
I know I was just giving the example
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

But that brings a lot of confusion.
All I know is that you the solution for your original question.

/gustav
Soupbone79

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
Soupbone79

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!
Your help has saved me hundreds of hours of internet surfing.
fblack61
Gustav Brock

You are welcome!

/gustav