setting criteria for date difference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble setting a criteria for a date difference column in a
select query. The colume is this: Diff: (Now()- [datefield]) and returns
values that look like an interger. When I set the criteria for that column
as :
<[# days limited to:], when prompted I enter the value as an interger but
the query still returns all values. When I manually set the criteria to say,
<90, it works fine. Anyone got a fix so that I can enter a value on the fly
to limit the query records?
thanks
Jeff G
 
I am having trouble setting a criteria for a date difference column in a
select query. The colume is this: Diff: (Now()- [datefield]) and returns
values that look like an interger. When I set the criteria for that column
as :
<[# days limited to:], when prompted I enter the value as an interger but
the query still returns all values. When I manually set the criteria to say,
<90, it works fine. Anyone got a fix so that I can enter a value on the fly
to limit the query records?
thanks
Jeff G
Click to expand...

Diff will actually be a Double Float number. Now() returns the current
date and time, accurate to microseconds, as a double; a datefield is
similar (if it's a pure date, it will have a time portion of
midnight).

Try two things:

Diff: DateDiff("d", [datefield], Date())

will return the number of days ago as an integer; and use a criterion
of

< [# days limited to:]

with the less-than sign before the prompt.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John- I tried your suggestion but the query is still returning all records.
With the DateDiff function the number of days being returned looks like an
interger, but the criteria is not being recognized. I am still having the
same problem. If I hard wire the criteria in, it works. I played around
with the query parameter but it didn't like the date data type i specified
for the [# days limited to:] criteria.
Jeff G
John Vinson said:
I am having trouble setting a criteria for a date difference column in a
select query. The colume is this: Diff: (Now()- [datefield]) and returns
values that look like an interger. When I set the criteria for that column
as :
<[# days limited to:], when prompted I enter the value as an interger but
the query still returns all values. When I manually set the criteria to say,
<90, it works fine. Anyone got a fix so that I can enter a value on the fly
to limit the query records?
thanks
Jeff G
Click to expand...

Diff will actually be a Double Float number. Now() returns the current
date and time, accurate to microseconds, as a double; a datefield is
similar (if it's a pure date, it will have a time portion of
midnight).

Try two things:

Diff: DateDiff("d", [datefield], Date())

will return the number of days ago as an integer; and use a criterion
of

< [# days limited to:]

with the less-than sign before the prompt.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
John- I tried your suggestion but the query is still returning all records.
With the DateDiff function the number of days being returned looks like an
interger, but the criteria is not being recognized. I am still having the
same problem. If I hard wire the criteria in, it works. I played around
with the query parameter but it didn't like the date data type i specified
for the [# days limited to:] criteria.
Click to expand...

Please post the SQL view of the query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
here is the sql:

SELECT qryInspectionLastx.[Customer Number], qryInspectionLastx.MaxOfDate,
tblContrAssign.ContractorID, tblContrAssign.ProposalID,
tblContrAssign.DateCntrStart, tblContrAssign.DateCntrStop,
qryInspectionLastx.PriceStart, DateDiff("d",[Maxofdate],Date()) AS Diff
FROM qryInspectionLastx LEFT JOIN tblContrAssign ON
qryInspectionLastx.[Customer Number] = tblContrAssign.ProposalID
WHERE (((tblContrAssign.DateCntrStart)=[pricestart]) AND
((tblContrAssign.DateCntrStop) Is Null Or
(tblContrAssign.DateCntrStop)>[maxofdate])) OR
(((tblContrAssign.DateCntrStart)<[maxofdate] Or
(tblContrAssign.DateCntrStart)=[maxofdate]) AND
((tblContrAssign.DateCntrStop) Is Null Or
(tblContrAssign.DateCntrStop)>[maxofdate])) OR
(((tblContrAssign.DateCntrStart)=[maxofdate]) AND
((tblContrAssign.DateCntrStop) Is Null Or
(tblContrAssign.DateCntrStop)>[maxofdate]))
GROUP BY qryInspectionLastx.[Customer Number], qryInspectionLastx.MaxOfDate,
tblContrAssign.ContractorID, tblContrAssign.ProposalID,
tblContrAssign.DateCntrStart, tblContrAssign.DateCntrStop,
qryInspectionLastx.PriceStart, DateDiff("d",[Maxofdate],Date())
HAVING (((qryInspectionLastx.PriceStart)>[maxofdate] Or
(qryInspectionLastx.PriceStart)<[maxofdate] Or
(qryInspectionLastx.PriceStart)=[maxofdate]) AND
((DateDiff("d",[Maxofdate],Date()))=[# days limited to:])) OR
(((DateDiff("d",[Maxofdate],Date()))=[# days limited to:])) OR
(((DateDiff("d",[Maxofdate],Date()))=[# days limited to:]));


John Vinson said:
John- I tried your suggestion but the query is still returning all records.
With the DateDiff function the number of days being returned looks like an
interger, but the criteria is not being recognized. I am still having the
same problem. If I hard wire the criteria in, it works. I played around
with the query parameter but it didn't like the date data type i specified
for the [# days limited to:] criteria.
Click to expand...

Please post the SQL view of the query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top