Solved

Help with start date and end date access query

Posted on 2014-11-17
54
235 Views
Last Modified: 2014-11-19
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]));
0
Comment
Question by:Soupbone79
  • 30
  • 19
  • 5
54 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 40447321
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]
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40448036
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
0
 

Author Comment

by:Soupbone79
ID: 40448864
Sorry I copyed and pasted into new sql view query and I get this error
invalid SQL staement;expected 'DELETE','INSERT','SELECT', or 'UDATE'.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 300 total points
ID: 40449325
Pat only suggested other Where clauses. You still need the full query:

< first select query >
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]
UNION ALL
< second select query and so on ... for a total of six select queries >

Exchange LD and 1 year to 3 years as you go.

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40449696
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.
0
 

Author Comment

by:Soupbone79
ID: 40449774
I want to be able to put start and end in the future
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40449782
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40449788
> 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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40449811
Correction:

Parameters [startdate] DateTime, [enddate] DateTime;

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40449859
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)
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40449910
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
0
 

Author Comment

by:Soupbone79
ID: 40449996
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40450012
Then I assume you have no data for 2013.
Remove the Where clause and you'll see.

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40450048
yes for the month of December I have 44 records
0
 

Author Comment

by:Soupbone79
ID: 40450053
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]));
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40450068
What is the data type of the field LD?

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40450237
Date/Time
format m/d/yyyy

not indexed
0
 

Author Comment

by:Soupbone79
ID: 40450242
I could send you the whole database if you like I could just put dummy info in it.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40450283
That would be fine. Something is strange.

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40450389
ok here you go
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40450394
You need a second try ...

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40450396
here you go
Testing2.accdb
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 40450428
Take a look at my example again.  I think I mistyped the first field.  It should have been LD rather than IC.  Does that make sense to you now?

Adding a year to the date in the select clause isn't doing anything for you. You have to add it in the WHERE clause as my example showed.

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];
0
 

Author Comment

by:Soupbone79
ID: 40450441
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
0
 

Author Comment

by:Soupbone79
ID: 40450531
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
0
 

Author Comment

by:Soupbone79
ID: 40450557
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
0
 

Author Comment

by:Soupbone79
ID: 40450605
were you able to download the testing2 database?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:PatHartman
ID: 40450652
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.
0
 

Author Comment

by:Soupbone79
ID: 40450680
ok here you go again
Testing2.zip
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40451040
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40451165
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]));
0
 

Author Comment

by:Soupbone79
ID: 40451271
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];
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40451708
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
0
 

Author Comment

by:Soupbone79
ID: 40452242
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40452258
I guess you miss the top line:

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

It makes quite a difference here.

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40452313
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40452328
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40452387
Again - you don't need the union.  You can use a compound Where clause as I showed in my first example.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40452393
That wouldn't give you the common field [Test Due Date].

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40452398
Ok but it would tell what test is do
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40452410
Yes. Also, you will get only one record no matter which field matches.

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40452422
Ok but it would tell what test is do
0
 

Author Comment

by:Soupbone79
ID: 40452429
sorry posted it twice. I am sure client may what that any other idea
0
 

Author Comment

by:Soupbone79
ID: 40452465
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]));
0
 

Author Comment

by:Soupbone79
ID: 40452476
on that you have to put in 12/01/2011 12/31/2011 to get data
0
 

Author Comment

by:Soupbone79
ID: 40452485
maybe a where statement to the data type as a test type?
0
 

Author Comment

by:Soupbone79
ID: 40452505
would a new table be needed for test types?
0
 

Author Comment

by:Soupbone79
ID: 40452511
with a relationship between
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40452520
> 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
0
 

Author Comment

by:Soupbone79
ID: 40452583
I know I was just giving the example
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40452657
But that brings a lot of confusion.
All I know is that you the solution for your original question.

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40452785
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
0
 

Author Comment

by:Soupbone79
ID: 40453375
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!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40454435
You are welcome!

/gustav
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now