• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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]));
0
Soupbone79
Asked:
Soupbone79
  • 30
  • 19
  • 5
2 Solutions
 
PatHartmanCommented:
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
 
Gustav BrockCIOCommented:
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
 
Soupbone79Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Gustav BrockCIOCommented:
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
 
Soupbone79Author Commented:
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
 
Soupbone79Author Commented:
I want to be able to put start and end in the future
0
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
> 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
 
Gustav BrockCIOCommented:
Correction:

Parameters [startdate] DateTime, [enddate] DateTime;

/gustav
0
 
Soupbone79Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
Soupbone79Author Commented:
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
 
Gustav BrockCIOCommented:
Then I assume you have no data for 2013.
Remove the Where clause and you'll see.

/gustav
0
 
Soupbone79Author Commented:
yes for the month of December I have 44 records
0
 
Soupbone79Author Commented:
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
 
Gustav BrockCIOCommented:
What is the data type of the field LD?

/gustav
0
 
Soupbone79Author Commented:
Date/Time
format m/d/yyyy

not indexed
0
 
Soupbone79Author Commented:
I could send you the whole database if you like I could just put dummy info in it.
0
 
Gustav BrockCIOCommented:
That would be fine. Something is strange.

/gustav
0
 
Soupbone79Author Commented:
ok here you go
0
 
Gustav BrockCIOCommented:
You need a second try ...

/gustav
0
 
Soupbone79Author Commented:
here you go
Testing2.accdb
0
 
PatHartmanCommented:
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
 
Soupbone79Author Commented:
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
 
Soupbone79Author Commented:
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
 
Soupbone79Author Commented:
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
 
Soupbone79Author Commented:
were you able to download the testing2 database?
0
 
PatHartmanCommented:
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
 
Soupbone79Author Commented:
ok here you go again
Testing2.zip
0
 
Gustav BrockCIOCommented:
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
 
PatHartmanCommented:
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
 
Soupbone79Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
Soupbone79Author Commented:
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
 
Gustav BrockCIOCommented:
I guess you miss the top line:

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

It makes quite a difference here.

/gustav
0
 
Soupbone79Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
PatHartmanCommented:
Again - you don't need the union.  You can use a compound Where clause as I showed in my first example.
0
 
Gustav BrockCIOCommented:
That wouldn't give you the common field [Test Due Date].

/gustav
0
 
Soupbone79Author Commented:
Ok but it would tell what test is do
0
 
Gustav BrockCIOCommented:
Yes. Also, you will get only one record no matter which field matches.

/gustav
0
 
Soupbone79Author Commented:
Ok but it would tell what test is do
0
 
Soupbone79Author Commented:
sorry posted it twice. I am sure client may what that any other idea
0
 
Soupbone79Author Commented:
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
 
Soupbone79Author Commented:
on that you have to put in 12/01/2011 12/31/2011 to get data
0
 
Soupbone79Author Commented:
maybe a where statement to the data type as a test type?
0
 
Soupbone79Author Commented:
would a new table be needed for test types?
0
 
Soupbone79Author Commented:
with a relationship between
0
 
Gustav BrockCIOCommented:
> 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
 
Soupbone79Author Commented:
I know I was just giving the example
0
 
Gustav BrockCIOCommented:
But that brings a lot of confusion.
All I know is that you the solution for your original question.

/gustav
0
 
Soupbone79Author Commented:
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
 
Soupbone79Author Commented:
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
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 30
  • 19
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now