date greater than

  • Thread starter Thread starter JK
  • Start date Start date
J

JK

I feel like I've tried everything and I can't seem to make this work... I
want to display all records where the "LastPurchaseDate" is greater than 60
day's.

The ultimate goal is to display all accounts (and their list of loaned
equipment) if they have equipment on loan and if they have not purchased
product in over 60 or 90 day's using the "LastPurchaseDate" field.

here is the SQL ----> please help!! Thx...

SELECT Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.PhoneNumber,
Customers.AccountNumber, Customers.SalesmanNumber, Customers.SalesmanName,
Customers.ManagerNumber, Customers.ManagerName, [SalesmanNumber] & " - " &
[SalesmanName] AS Salesperson, [ManagerNumber] & " - " & [ManagerName] AS
Manager, Equipment.FAKIT, Equipment.FAASID, Equipment.FADL01,
Equipment.FADAJ, Equipment.COUNCS, DateDiff("d",[LastPurchaseDate],Date()) AS
DateCompare, Format([LastPurchaseDate],"mm/dd/yyyy") AS PurchaseDate,
Customers.LastPurchaseDate
FROM Customers INNER JOIN Equipment ON Customers.AccountNumber =
Equipment.FAAN8
WHERE ((Not (Customers.SalesmanNumber) Is Null) AND (Not
(Customers.SalesmanName) Is Null) AND (Not (Equipment.FAKIT) Is Null) AND
((Format([LastPurchaseDate],"mm/dd/yyyy"))<Date()-60));
 
I feel like I've tried everything and I can't seem to make this work... I
want to display all records where the "LastPurchaseDate" is greater than 60
day's.

The ultimate goal is to display all accounts (and their list of loaned
equipment) if they have equipment on loan and if they have not purchased
product in over 60 or 90 day's using the "LastPurchaseDate" field.

here is the SQL ----> please help!! Thx...

SELECT Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.PhoneNumber,
Customers.AccountNumber, Customers.SalesmanNumber, Customers.SalesmanName,
Customers.ManagerNumber, Customers.ManagerName, [SalesmanNumber] & " - " &
[SalesmanName] AS Salesperson, [ManagerNumber] & " - " & [ManagerName] AS
Manager, Equipment.FAKIT, Equipment.FAASID, Equipment.FADL01,
Equipment.FADAJ, Equipment.COUNCS, DateDiff("d",[LastPurchaseDate],Date()) AS
DateCompare, Format([LastPurchaseDate],"mm/dd/yyyy") AS PurchaseDate,
Customers.LastPurchaseDate
FROM Customers INNER JOIN Equipment ON Customers.AccountNumber =
Equipment.FAAN8
WHERE ((Not (Customers.SalesmanNumber) Is Null) AND (Not
(Customers.SalesmanName) Is Null) AND (Not (Equipment.FAKIT) Is Null) AND
((Format([LastPurchaseDate],"mm/dd/yyyy"))<Date()-60));
Click to expand...

You're using the DateDiff function above, use it in your WHERE clause

WHERE ... AND DateDiff("d",[LastPurchaseDate],Date()) >60;
 
I feel like I've tried everything and I can't seem to make this work... I
want to display all records where the "LastPurchaseDate" is greater than 60
day's.

The ultimate goal is to display all accounts (and their list of loaned
equipment) if they have equipment on loan and if they have not purchased
product in over 60 or 90 day's using the "LastPurchaseDate" field.

here is the SQL ----> please help!! Thx...

SELECT Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.PhoneNumber,
Customers.AccountNumber, Customers.SalesmanNumber, Customers.SalesmanName,
Customers.ManagerNumber, Customers.ManagerName, [SalesmanNumber] & " - " &
[SalesmanName] AS Salesperson, [ManagerNumber] & " - " & [ManagerName] AS
Manager, Equipment.FAKIT, Equipment.FAASID, Equipment.FADL01,
Equipment.FADAJ, Equipment.COUNCS, DateDiff("d",[LastPurchaseDate],Date()) AS
DateCompare, Format([LastPurchaseDate],"mm/dd/yyyy") AS PurchaseDate,
Customers.LastPurchaseDate
FROM Customers INNER JOIN Equipment ON Customers.AccountNumber =
Equipment.FAAN8
WHERE ((Not (Customers.SalesmanNumber) Is Null) AND (Not
(Customers.SalesmanName) Is Null) AND (Not (Equipment.FAKIT) Is Null) AND
((Format([LastPurchaseDate],"mm/dd/yyyy"))<Date()-60));
Click to expand...

The Format() function returns a *TEXT STRING* so your comparison will fail:
the text string "12/25/2007" is in fact less than the text string "9/15/2004"
even though it's an earlier date.

Instead, use a criterion on your calculated DateCompare expression:

SELECT Customers.CompanyName, Customers.BillingAddress, Customers.City,
Customers.StateOrProvince, Customers.PostalCode, Customers.PhoneNumber,
Customers.AccountNumber, Customers.SalesmanNumber, Customers.SalesmanName,
Customers.ManagerNumber, Customers.ManagerName, [SalesmanNumber] & " - " &
[SalesmanName] AS Salesperson, [ManagerNumber] & " - " & [ManagerName] AS
Manager, Equipment.FAKIT, Equipment.FAASID, Equipment.FADL01,
Equipment.FADAJ, Equipment.COUNCS, Format([LastPurchaseDate],"mm/dd/yyyy") AS
PurchaseDate, Customers.LastPurchaseDate
FROM Customers INNER JOIN Equipment ON Customers.AccountNumber =
Equipment.FAAN8
WHERE Customers.SalesmanNumber Is Not Null
AND Customers.SalesmanName Is Not Null
AND Equipment.FAKIT Is Not Null
AND DateDiff("d",[LastPurchaseDate],Date()) > 60;


Note that you can put the datediff expression in the criteria without
displaying the calculated value. I've also used the IS NOT NULL criterion and
removed Access's extraneous parentheses.

John W. Vinson [MVP]
 
Back
Top