Access 2003: date calculations

[JC21]

Hi Guys,

I am currently trying to create 2 reports and I need some assistance. I am going to give an example of what I would like to accomplish and hope someone can guide me in the right direction. I have a query which was created from table CustInfo. The query has the fields CoID, Name, Date1. I wanted to calculate the days between Date1 and Now. I was able to do so with this expression =DateDiff(“d”,[Date1], Date()).
I have another table CoInfo with the following CoID, Acct# and Date2.
Example:
CoID ACCT# DAYS
Co 1234 90
Co 3456 180

Report1 I would like for it to only display all CoID where Date1 is equal or greater than Date2. Report2 I would like to display CoID where Date1 is 30 or less from Date2. I hope this makes sense. Hopefully this can be done in a query. Thank you for any advice.

[MMcCarthy]

Not 100% sure what you're looking for.

However, see if this query gives the results you want in Report1.

SELECT * FROM CustInfo
WHERE Date1 >= DLookup("[Date2]", "CoInfo", "[CoID]=" & [CoID])

For Report2

SELECT * FROM CustInfo
WHERE DateDiff("d",[Date1], DLookup("[Date2]", "CoInfo", "[CoID]=" & [CoID])) < 30

Mary

[JC21]

Hi mmccarthy,

I was away for few days; sorry it took so long to reply back. I am trying to build a small db for one of my friends who rents out landscaping equipment. Each equipment can be rented only for a certain amount of days. On the [TblEquipment] I have a list of all the [Equipments] and the [#ofdays] it can be rented for. [Tblcoinfo] contains the fields
CoName, CO#, Equipment, DateRent.
I created a query which links the table by equipment. I created a field/expresion in the query which is [Equipdays=DateD iff(“d”,[DateRent], Date())]. This gives me the number of days its been since the equipment as been rented. I would like to have 2 reports, one to display only accounts where the equipment days [Equipdays] is greater than [#ofdays]. The other report to display equipments that are within 30 days. Hope this makes more sense. If you know a better way to set this up or have other suggestions please let me know, thanks again for any input.