Date Query Help

[dantheman]

Hello Everybody. I basically have a table that shows when a Tool was borrowed, when the due is due back, and a date if the tool was returned.


I am trying to make a query to see if their are tools still borrowed and it is past the due date. Anyways I got this so far:
[Date Returned] Is Null And [Date Borrowed]>[Due Date]
I tried putting that in the criteria box and it did not work.
Does anyone have an idea why it is not working? All three dates are specified to be date fields in the table. thanks

[JoeM]

Under which fields are you placing that criteria?
Are you putting them on the same line?
How is it not working (is it returning too many records, not enough, are you getting an error)?

It might be helpful if you paste your query attempt here. Just change your query to SQL View and copy and paste your code here. Then we can see exactly what you are doing.

[BobBridges]

[Date Borrowed]>[Due Date]? Don't you mean Today() rather than Date Borrowed?

Also what he said: How is it not working? "Not working" is almost never helpful; if you expect someone to diagnose your problem you gotta give symptoms, almost always precise symptoms.

[dantheman]

Here is some more information
I have a table, here are some fields in it: [Borrower], [Date Borrowed], [Date Returned], and [Due Date]
Now my query is not producing any results.
I am trying to make a query to show what items are past due.
For example, if someone borrowed an item on 7/14/2011 and the due date was 5/4/2013 it should show only what is past due.
Here is the SQL:
SELECT [Borrowed Log].[CD Borrowed], [Borrowed Log].Borrower, [Borrowed Log].[Date Borrowed]
FROM [Borrowed Log]
WHERE (([Date Returned] Is Null And [Date Borrowed]>[Due Date]));

it is producing no results

[dantheman]

Might have solved it, not exactly what i was trying to do but it should worl.
I went into the query and typed this in the field: Expr1: (DateDiff("d",[Due Date],Date()))
This basically says get the difference between the due date and todays date
then in the criteria portion i typed:
>=1 And ([Date Returned] Is Null)
This says see if that value is greater than or equal to one day and the it is not returned;
the output shows the days over due, let me know what you guys think

[ssanfu]

Anyways I got this so far: [Date Returned] Is Null And [Date Borrowed]>[Due Date]

This was close.
So you have a field that represents when the tool was borrowed and a calculated due date. Your Where clause will never return any records.
For example,
[Date Borrowed] = #8/1/2013# and
[Due Date] = #8/15/2013#

Substituting values
[Date Borrowed]>[Due Date]
#8/1/2013# > #8/15/2013#
You can see this will never be true.. ( I had to think about this for a while ) Presumably [Date Borrowed] would not be changed...


What about if we use
[Date Returned] Is Null And [Due Date] < Date()

If:
[Due Date] = #8/15/2013#
Date() = #9/11/2013#

Substituting:
[Due Date] < Date()
#8/15/2013# < #9/11/2013#

This IS true!!

You might try:

SELECT [Borrowed Log].[CD Borrowed], [Borrowed Log].Borrower, [Borrowed Log].[Date Borrowed]
FROM [Borrowed Log]
WHERE ([Date Returned] Is Null) And ([Due Date]<Date() );

<rant>
( I REALLY hate spaces in object names)
</rant>


My $0.02......