NewtoAccess Query Problem

[BassMan50]

Below is the SQL statement of a query. No matter what I do I still get all records back when I run this query.



SELECT T_Sponsor.T_S_LastName, T_Sponsor.T_S_FirstName, T_Sponsor.T_S_MI, T_Sponsor.T_S_SSN, T_Sponsor.T_S_Gender, T_Sponsor.T_S_DOB, T_Sponsor.T_S_PCM, T_MedInfo.T_M_DateRevPCM
FROM T_Sponsor INNER JOIN T_MedInfo ON T_Sponsor.T_S_SponsorId = T_MedInfo.T_M_SponsorId
WHERE (([T_M_DateRevPCM]>7));

Any ideas?

[jzwp11]

Welcome to the forum!

What do you want the query to return?

What is this field? T_M_DateRevPCM
What type of data does it hold? What is the field's datatype?

[BassMan50]

I want the query to return records > 7 days of the current date. That column is the date a physician has reviewed the initial medical record. The data type is date, ( short date format).

[jzwp11]

You need to determine the difference in the dates using the datediff() and the date() function and apply that in the WHERE clause of the query:

WHERE datediff("d",T_M_DateRevPCM,date())>7;

[BassMan50]

Thanks, that worked! Is there a book you could recommend to learn the SQL statements for access?

[orange]

For SQL (dated references) online
Basic
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

Intermediate
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

Advanced
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx


General
http://www.w3schools.com/SQL/sql_datatypes.asp

[BassMan50]

Thanks to both of you for the help!!

[jzwp11]

You're welcome!

[BassMan50]

It is possible to have the query include null values for [T_MedInfo.T_M_DateRevPCM] as well as when a date is entered in that field?

WHERE DateDiff
("d",T_MedInfo.T_M_DateSignINP,T_MedInfo.T_M_DateR evPCM)>7;

[William McKinley]

It is possible to have the query include null values for [T_MedInfo.T_M_DateRevPCM] as well as when a date is entered in that field?

You could add another criteria to your where clause

WHERE DateDiff 
("d",T_MedInfo.T_M_DateSignINP,T_MedInfo.T_M_DateRevPCM)>7 OR [T_MedInfo.T_M_DateRevPCM] Is Null;

[BassMan50]

Thanks William, it works. I was trying to add another criteria and could not get the syntax right. I'm glad I joined this forum, you guys rock!!