Date Query

[Travler]

I have the following query which should return records more than 14 days old
for loaned equipment. The query is as follows:
SELECT AD_LOANEDASSETS.AssetNumber, AD_LOANEDASSETS.ComponentModel,
AD_LOANEDASSETS.SerialNumber, AD_LOANEDASSETS.DateIssued,
AD_LOANEDASSETS.AUNumber, AD_LOANEDASSETS.AuthorisedBy
FROM AD_LOANEDASSETS
WHERE (((AD_LOANEDASSETS.DateIssued)>[dateissued]-14) AND
((AD_LOANEDASSETS.AssetReturned)="no") AND ((AD_LOANEDASSETS.IsDeleted)=0))
ORDER BY AD_LOANEDASSETS.ComponentModel;

I know that it should return one 1 record at present, but it is not. Playing
around with the '<>' returns either 0 records or 2 (which meet the
AssetReturned and IsDeleted criteria).
Access 2003, date format is dd/mm/yyyy.
I was wondering if it was to do with the way SQL looks at dates?
Any help please.

 

[Wayne Morgan]

WHERE (((AD_LOANEDASSETS.DateIssued)>[dateissued]-14) AND

It appears that you are comparing DateIssued to itself minus 14 days. Should
the second DateIssued be Date()? If so, then the > should be <. You need
dates that are less than (before) the date you're testing against.

 

[Travler]

Hi Wayne,
I am trying to return records that have been out on loan for 14 days or more
from the date of issue. When I tried to use the Date() it returned a compile
error, my first attempt was:
WHERE (((AD_LOANEDASSETS.DateIssued)>Date()-14) AND
it returned a compile error no matter which way I set the expression
(<,>,-,+).
The only way I could get a result was to use the Dateissue field. Any Ideas?

Thanks,

Stephen Martin

WHERE (((AD_LOANEDASSETS.DateIssued)>[dateissued]-14) AND

It appears that you are comparing DateIssued to itself minus 14 days. Should
the second DateIssued be Date()? If so, then the > should be <. You need
dates that are less than (before) the date you're testing against.

--
Wayne Morgan
MS Access MVP

I have the following query which should return records more than 14 days
old
for loaned equipment. The query is as follows:
SELECT AD_LOANEDASSETS.AssetNumber, AD_LOANEDASSETS.ComponentModel,
AD_LOANEDASSETS.SerialNumber, AD_LOANEDASSETS.DateIssued,
AD_LOANEDASSETS.AUNumber, AD_LOANEDASSETS.AuthorisedBy
FROM AD_LOANEDASSETS
WHERE (((AD_LOANEDASSETS.DateIssued)>[dateissued]-14) AND
((AD_LOANEDASSETS.AssetReturned)="no") AND
((AD_LOANEDASSETS.IsDeleted)=0))
ORDER BY AD_LOANEDASSETS.ComponentModel;

I know that it should return one 1 record at present, but it is not.
Playing
around with the '<>' returns either 0 records or 2 (which meet the
AssetReturned and IsDeleted criteria).
Access 2003, date format is dd/mm/yyyy.
I was wondering if it was to do with the way SQL looks at dates?
Any help please.

 

[Wayne Morgan]

Do you have a field named Date? If so, it may be conflicting. "Date" is a
reserved word. However, it is more likely a References problem. See this
link for information of solving those.

http://www.allenbrowne.com/ser-38.html

--
Wayne Morgan
MS Access MVP

Hi Wayne,
I am trying to return records that have been out on loan for 14 days or
more
from the date of issue. When I tried to use the Date() it returned a
compile
error, my first attempt was:
WHERE (((AD_LOANEDASSETS.DateIssued)>Date()-14) AND
it returned a compile error no matter which way I set the expression
(<,>,-,+).
The only way I could get a result was to use the Dateissue field. Any
Ideas?

Thanks,

Stephen Martin

WHERE (((AD_LOANEDASSETS.DateIssued)>[dateissued]-14) AND

It appears that you are comparing DateIssued to itself minus 14 days.
Should
the second DateIssued be Date()? If so, then the > should be <. You need
dates that are less than (before) the date you're testing against.

--
Wayne Morgan
MS Access MVP

I have the following query which should return records more than 14 days
old
for loaned equipment. The query is as follows:
SELECT AD_LOANEDASSETS.AssetNumber, AD_LOANEDASSETS.ComponentModel,
AD_LOANEDASSETS.SerialNumber, AD_LOANEDASSETS.DateIssued,
AD_LOANEDASSETS.AUNumber, AD_LOANEDASSETS.AuthorisedBy
FROM AD_LOANEDASSETS
WHERE (((AD_LOANEDASSETS.DateIssued)>[dateissued]-14) AND
((AD_LOANEDASSETS.AssetReturned)="no") AND
((AD_LOANEDASSETS.IsDeleted)=0))
ORDER BY AD_LOANEDASSETS.ComponentModel;

I know that it should return one 1 record at present, but it is not.
Playing
around with the '<>' returns either 0 records or 2 (which meet the
AssetReturned and IsDeleted criteria).
Access 2003, date format is dd/mm/yyyy.
I was wondering if it was to do with the way SQL looks at dates?
Any help please.