Date Selection Help

[MrSmith]

I currently have a query that takes info from an asset support table.
I have a Support Contract Start Date in the table (Date time)
I have a Support Contract Length In months field in the tabel (numeric)

i am calculating the end date of the query using the following expression.

Support End Date: IIf([Support Start Date] Is Null Or [Support Contract Length] Is Null,"N/A",DateAdd("m",[Support Contract Length],[Support Start Date])

this Works fine and I get a Date however many months in the future that the contract length in months field dictates.

however i am now trying to add criteria to this in order to select certain date spans of when a contract ends.

So i only get records in a query that show records with end dates in 30 days time from now or another query that shows contracts ending in 365 days from now.

Can someone point me in the direction of how to do this effectivly , as i need to generate a report from this query that will show me support contracts expiring in 1 year and in 30 days time.

Thankyou for your time and help

 

[KeithG]

You need to use the DateDiff Function

DateDiff('d',[Support End Date],Now())

 

[MrSmith]

Keith ,

Thanks I changed it to the following to give me remaining days,

Days Remaining: IIf([Support Start Date] Is Null Or [Support Contract Length] Is Null,"N/A",DateDiff('d',Now(),[Support End Date]))

 

[MrSmith]

So now in my column of days remaining - I have a number of days

How do I add criteria for reports -

I.e

all those with greater than 0 and less than 30 days remaining

all those with greater than 0 and less than 365 days remaing

thanks

 

[MrSmith]

bump... any help appreciated thanks

 

[MrSmith]

anyone? someone?

 

[KeithG]

in the criteria row put >0 and <365

 

[Jon K]

Put the criteria in a new column in the query grid like this:-
-------------------------------------------------
Field: IIf([Support Start Date] Is Null Or [Support Contract Length] Is Null, Null, DateDiff('d', Date(), DateAdd("m", [Support Contract Length], [Support Start Date])))

Show: uncheck

Criteria: Between 0 and 30
-------------------------------------------------

Similarly, you can put Between 0 and 365 for the other query.
.

 

[MrSmith]

sorry been tied up with other work stuff -

ok if i use the >0 and <365

i get an error saying data type mismatch

 

[neileg]

The "N/A" turns it into a text field.

 

[MrSmith]

scratch the last comment -

So i use the following expression to get the end date of a contract:

Support End Date: IIf([Support Start Date] Is Null Or [Support Contract Length] Is Null,"N/A",DateAdd("m",[Support Contract Length],[Support Start Date]))


This is based upon a start date + a set number of months (contract length)

So if the start date is 01/01/2006 and contract length is 24 , the end date will come up as 01/01/2008

Now I enter this as another expression to calculate the number of days remaining between now and the end date.


Days Remaining: IIf([Support Start Date] Is Null Or [Support Contract Length] Is Null,"N/A",DateDiff('d',Now(),[Support End Date]))

I get number of days remaining from this

now what i need to get is the query of say contracts with less than 300 days remaining.

If i just enter >0 and <30 in the criteria box

I get a pop up box asking for the support end date -
I dont want to enter this it should auto know when the support end date is

thanku for your time