CODEWITHSUNDEEP
ms-accessms-access-2010
Rose6891
Rose6891

Reputation: 23

Calculating days between dates and parameters in MS Access

I'm trying to write a query that allows me to get records for all users whose items are more than X number of days overdue. I want to be able to specify whether that number be 30 or 40 or 50. I am able to get the results I need when I specify a specific number in the sql query:

   SELECT USER_TRANSACTIONS.PATRON_ID, USER_TRANSACTIONS.CURRENT_DUE_DATE,        
    DateDiff("d",USER_TRANSACTIONS.CURRENT_DUE_DATE,Date()) AS Expr1
  FROM USER_TRANSACTIONS
  WHERE (DateDiff("d", USER_TRANSACTIONS.CURRENT_DUE_DATE, Date()))>50

However, when I try to use a parameter in place of the number, 50. And then type in the number 50 when I run the query and am prompted

     SELECT USER_TRANSACTIONS.PATRON_ID,USER_TRANSACTIONS.CURRENT_DUE_DATE,   
     DateDiff("d",USER_TRANSACTIONS.CURRENT_DUE_DATE,Date()) AS Expr1
     FROM USER_TRANSACTIONS
     WHERE (DateDiff("d", USER_TRANSACTIONS.CURRENT_DUE_DATE, Date()))>[MinimumNumDays]

The query gives me everything instead of limiting the records to those overdue by more than 50 days. What am I missing??

Upvotes: 2

Views: 832

Answers (2)

ChrisPadgham
ChrisPadgham

Reputation: 870

if you are running this query from a form you can add an unbouand text box to the form. Enter the number of days in this. In query definition right click on in the criteria row for Expr1 and select build, locate your text box in forms -> Loaded Forms -> your form

double click on it and click OK

Upvotes: 0

ron tornambe
ron tornambe

Reputation: 10780

You will need to add [MinimumNumDays] as an Integer query parameter. (Click [?] Parameters on the upper right the query builder)

Upvotes: 1

Related Questions