Trying to query data with field date - 30 days

[digitalbots]

Hello all,

My english is not so good but this is what I am trying to do.

I lend out records to my friends and I am trying to run a query to see who borrowed what over a 30 day time span.

I have two tables one that holds my data

Friends
* FriendID
* Name
* Location

Records
* FriendID
* RecordID
* RecordName
* RecordArtest
* DateBorrowed
* DateReturned
* DateArchived

I put my records in the "archives" when I want to keep them in house but I want to see who was the last people to return the record 30 days before I archived it.

I keep trying different queries but i am not sure how to do it.

any help would be really great.

:banghead:

 

[Ranman256]

use DATEDIFF and make query:
Select *,DateDiff("d",[dateBorrowed],Date()) as DaysBorrowed from Records where [datereturned] is null

then make a query on this query , and ask for [DaysBorrowed]>30

 

[digitalbots]

use DATEDIFF and make query:
Select *,DateDiff("d",[dateBorrowed],Date()) as DaysBorrowed from Records where [datereturned] is null

then make a query on this query , and ask for [DaysBorrowed]>30

how do you make query on this query?

 

[arnelgp]

you can also write in sql:

SELECT [Name], Location, RecordName, DateBorrowed From Records Left Join Friends On Friends.FriendID=Records.FriendID Where ((DateBorrowed) = (Date()-30));

 

[digitalbots]

you can also write in sql:

SELECT [Name], Location, RecordName, DateBorrowed From Records Left Join Friends On Friends.FriendID=Records.FriendID Where ((DateBorrowed) = (Date()-30));

This query kinda works for me. Question about this part.

Where ((DateBorrowed) = (Date()-30))

I know Date() is = todays date. What if I wanted it 30 days before the album was archived?

That way i can see every person that touched an archived album?

 

[arnelgp]

Same thing but instead of borrowed Date you substitute archive date.