Right format of dates for query criteria

[Chris Asaipillai]

I have a Microsoft Access application which front ends onto a SQL Server 2005 database.

I am using a Access query that queries a Datetime field called FollowUpDate as follows:

SELECT tblClientsRoot.Forename, tblClientsRoot.MiddleName, tblClientsRoot.Surname, tblClientsRoot.PhoneH, tblClientsRoot.PhoneW, tblClientsRoot.PhoneM, tblClientsRoot.Email, tblClientsRoot.PhoneO, tblStaff.informal, tblClientsRebate.FollowUpStaff, Date()+3 AS dte, tblClientsRebate.FollowUpDate, tblClientsRebate.FollowUpComments, "Rebate" AS Category, "" AS CompanyName, "" AS CoNum, tblClientsTax.NI, tblClientsTax.UTR, tblClientsRebate.PAYERef, IIf([PAYEOfficeCode] Is Null,"",DLookUp("[Phone]","tblPAYETaxOffices","[ID] = " & [PAYEOfficeCode])) AS PAYEPh, tblPAYETaxOffices.District, tblClientsTax.DateOfBirth
FROM ((tblClientsRoot INNER JOIN tblClientsTax ON tblClientsRoot.ID = tblClientsTax.ClientID) INNER JOIN (tblStaff INNER JOIN tblClientsRebate ON tblStaff.StaffCode = tblClientsRebate.FollowUpStaff) ON tblClientsTax.ClientID = tblClientsRebate.ClientID) LEFT JOIN tblPAYETaxOffices ON tblClientsRebate.PAYEOfficeCode = tblPAYETaxOffices.ID
WHERE (((tblClientsRebate.FollowUpDate)<Date()+3))
ORDER BY tblClientsRebate.FollowUpDate DESC;

Now this should exclude all follow up dates which are less than todays date +3 but instead
I still get the following rows of data returned

Date() +3 Follow Up Date



01/02/2007 05/02/2003

01/02/2007 02/09/2004

01/02/2007 27/04/2005

01/02/2007 12/12/2005

Is there something I am doing wrong with with my Access query?

Thanks

Chris

 

[Damian S]

Hi Chris,

The problem lies in the where clause, you have:

WHERE (((tblClientsRebate.FollowUpDate)<Date()+3))

This will return all items with a FollowUpDate BEFORE today's date plus 3
days.

You said you wanted items with a FollowUpDate AFTER today's date plus 3
days, so you will need this:

WHERE (((tblClientsRebate.FollowUpDate)>=Date()+3))

= if you want to include those that fall on 3 days from today, just > if you want after 3 days from today.

Hope this helps.

Damian.

I have a Microsoft Access application which front ends onto a SQL Server 2005 database.

I am using a Access query that queries a Datetime field called FollowUpDate as follows:

SELECT tblClientsRoot.Forename, tblClientsRoot.MiddleName, tblClientsRoot.Surname, tblClientsRoot.PhoneH, tblClientsRoot.PhoneW, tblClientsRoot.PhoneM, tblClientsRoot.Email, tblClientsRoot.PhoneO, tblStaff.informal, tblClientsRebate.FollowUpStaff, Date()+3 AS dte, tblClientsRebate.FollowUpDate, tblClientsRebate.FollowUpComments, "Rebate" AS Category, "" AS CompanyName, "" AS CoNum, tblClientsTax.NI, tblClientsTax.UTR, tblClientsRebate.PAYERef, IIf([PAYEOfficeCode] Is Null,"",DLookUp("[Phone]","tblPAYETaxOffices","[ID] = " & [PAYEOfficeCode])) AS PAYEPh, tblPAYETaxOffices.District, tblClientsTax.DateOfBirth
FROM ((tblClientsRoot INNER JOIN tblClientsTax ON tblClientsRoot.ID = tblClientsTax.ClientID) INNER JOIN (tblStaff INNER JOIN tblClientsRebate ON tblStaff.StaffCode = tblClientsRebate.FollowUpStaff) ON tblClientsTax.ClientID = tblClientsRebate.ClientID) LEFT JOIN tblPAYETaxOffices ON tblClientsRebate.PAYEOfficeCode = tblPAYETaxOffices.ID
WHERE (((tblClientsRebate.FollowUpDate)<Date()+3))
ORDER BY tblClientsRebate.FollowUpDate DESC;

Now this should exclude all follow up dates which are less than todays date +3 but instead
I still get the following rows of data returned

Date() +3 Follow Up Date



01/02/2007 05/02/2003

01/02/2007 02/09/2004

01/02/2007 27/04/2005

01/02/2007 12/12/2005

Is there something I am doing wrong with with my Access query?

Thanks

Chris