Sql union query-where clause dates

[m0aje]

Hello,

I am trying to pull records for computer equipment that the test date has exceeded 90 days. I am trying to modify a union query to make this work. I have several tables that I have to search. I cannot change the structure so I have adapted to it.
This is the code I am trying to modify.I am trying to get the WHERE statement correct so it will flag all the records where the test date has exceeded 90 days.

SELECT [SERVER].UnitPart,UnitSerial,TestDate,Technician
FROM [SERVER]
WHERE (((DateDiff("d"[TestDate]>90,Date())); Am I close?????
UNION ALL
SELECT [PRINTER].UnitPart,UnitSerial,TestDate,Technician
FROM [PRINTER]
WHERE (((DateDiff("d"[TestDate]>90,Date()));
UNION ALL
SELECT [LAPTOP].UnitPart,UnitSerial,TestDate,Technician
FROM [LAPTOP]
WHERE (((DateDiff("d"[TestDate]>90,Date()));
UNION ALL
SELECT [WORKSTATION].UnitPart,UnitSerial,TestDate,Technician
FROM [WORKSTATION]
WHERE (((DateDiff("d"[TestDate]>90,Date()));


I would be most grateful for help with this.

THANK YOU,

m0aje

 

[plog]

As Homer Simpson would say, ' you're close but way off'. You do your calculation, then your comparison, DateDiff has no idea what a > means. Do the calculation, then compare the result of it. Also, you're missing a comma.

First, since you can't change the structure, my advice is to do it by proxy. Build a UNION query with all data in it and use that query whenever you need to run data. So, don't add your criteria to this query. Use this query as the data source in another query and then use your criteria there. You'll only have to get it right once instead of however many tables you have times.

So let's call this query (the main UNION query without any criteria) MasterQuery. You're SQL would become:

SELECT UnitPart,UnitSerial,TestDate,Technician
FROM MasterQuery
WHERE DateDiff("d", [TestDate],Date())>90;

 

[m0aje]

As Homer Simpson would say, ' you're close but way off'. You do your calculation, then your comparison, DateDiff has no idea what a > means. Do the calculation, then compare the result of it. Also, you're missing a comma.

First, since you can't change the structure, my advice is to do it by proxy. Build a UNION query with all data in it and use that query whenever you need to run data. So, don't add your criteria to this query. Use this query as the data source in another query and then use your criteria there. You'll only have to get it right once instead of however many tables you have times.

So let's call this query (the main UNION query without any criteria) MasterQuery. You're SQL would become:

SELECT UnitPart,UnitSerial,TestDate,Technician
FROM MasterQuery
WHERE DateDiff("d", [TestDate],Date())>90;

Hello plog,

Thank you for your reply. For a MasterQuery, are you suggesting something like this:

SELECT UnitPart,UnitSerial,TestDate,Technician
FROM [LAPTOP]
UNION ALL
SELECT UnitPart,UnitSerial,TestDate,Technician
FROM [WORKSTATION]
UNION ALL
SELECT UnitPart,UnitSerial,TestDate,Technician
FROM [PRINTER]
etc....
Then save this Union query as 'MasterQuery'?
In reference to - WHERE DateDiff("d", [TestDate],Date())>90;
It opens a window 'Enter Parameter Value"
"d"
I also forgot about the SHIPPEDTO field. In this query the SHIPPEDTO field is blank.
Can I use WHERE DateDiff("d", [TestDate],Date())>90 AND [SHIPPEDTO]=Null --- or something like that???

Again, thanks for your response plog. I appreciate it.

m0aje
In reference to

 

[plog]

Yes, that's how you should build the MasterQuery (except bring in every field you need, e.g [SHIPPEDTO]).

For your where clause issue, can you post your full SQL? The syntax you have is correct and shouldn't show that window.

 

[m0aje]

Yes, that's how you should build the MasterQuery (except bring in every field you need, e.g [SHIPPEDTO]).

For your where clause issue, can you post your full SQL? The syntax you have is correct and shouldn't show that window.

Hello again plog,
Well, that is pretty much the full SQL. There is additional equipment such as SWITCHES, MONITORS, MEDIA CONVERTERS, etc...
All of those tables have other fields besides the part numbers, serial numbers, etc..., but, with respect to those fields, the tables are structured identical throughout. I inherited this database and cannot change anything. So I am linking to the tables themselves to extract the data I need in my queries. I am still a novice at this, but I get there and try though it frustrates the hell out of me sometimes.
As for the
WHERE DateDiff("d", [TestDate],Date())>90;I don't know why I am getting that parameter. I will keep plugging at it. Maybe something is off that I can't see.

Also, can I use something like WHERE DateDiff("d",[Testdate],Date())>90 AND Shippedto=Null ? Is that correct?

THANKS AGAIN plog for your help. You guys are the greatest.

 

[plog]

Yes, you seperate multiple conditions that must both be true with an AND. The Null syntax is this:

Shippedto IS NULL

You don't use equals sign when testing for nulls (Shippedto IS NOT NULL).

 

[m0aje]

Yes, you seperate multiple conditions that must both be true with an AND. The Null syntax is this:

Shippedto IS NULL

You don't use equals sign when testing for nulls (Shippedto IS NOT NULL).

Hello plog,

I created the MasterQuery as you suggested and used the DateDiff code you provided and SUCCESS! THANKYOU!
However, as the records in this database are old, it pulled up stuff from 2002. Is there a way to keep the results within 2013 and this year??
Also for adding the AND Shippedto should it be:
WHERE DateDiff("d",[TestDate],Date())>90 AND ShippedTo IS NULL;
Is that the right syntax??
Thanks again!
m0aje

 

[plog]

I believe the syntax is correct, give it a shot to make sure. For results within 2013 and 2014 you should use the Year() function around whichever date you need and then put >=2013 for the criteria.

 

[m0aje]

I believe the syntax is correct, give it a shot to make sure. For results within 2013 and 2014 you should use the Year() function around whichever date you need and then put >=2013 for the criteria.

Hello plog,
I think I got everything working properly. I have one more small issue with the dates I am going to try to resolve myself. But for now I am extremely grateful for your assistance. You folks up here are the greatest and I appreciate all the help I can get.THANK YOU AGAIN !