Dates

[Anna Anna]

Hi!
This is my query: I want to get number of graduates still
operating a business of those who graduated:
- less than a year ago
- two years ago (entire two years from now)
- three years ago (entire three years from now) etc.

Would you help me to write 'criteria' in a 'Date' field to get
these numbers?
Thanks,
Anna

 

[bjackson]

>= dateadd("yyyy",-1,date()) would give you all
dates from 1 year ago to today
>= dateadd("yyyy",-2,date()) would give you all
dates from 2 years ago to today

etc etc

 

[aneats]

I am also trying to return a date. it's for 'financial year', based on the criteria that if a month is >03 (or March) then return the year 2002/03 and if month is <03, then return the year 2001/02.

I have the following expression, however i continually get error messages....

IIf(Month([D_PAID])>03,(Year([D_PAID]) &"/"& Right(Year([D_PAID]),2) +1)),(Year([D_PAID])-1&"/"& Right(Year([D_PAID]),2))

What i want it to return is either '2001/02' or '2002/03'

help!

 

[neileg]

IIf(Month([D_PAID])>03,(Year([D_PAID]) &"/"& Right(Year([D_PAID]),2) +1)),(Year([D_PAID])-1&"/"& Right(Year([D_PAID]),2))

Right is a string function and your adding a number to the string result. Try moving your +1 adjustment inside the brackets to give you:
IIf(Month([D_PAID])>03,(Year([D_PAID]) &"/"& Right(Year([D_PAID])+1,2))),(Year([D_PAID]-1)&"/"& Right(Year([D_PAID]),2))

I haven't tried this so no money back if it doesn't work!

 

[Anna Anna]

Thanks bjackson!!!
I will try it in a moment.
Anna

 

[aneats]

Unfortunately this didn't work.
i'll keep trying, and if anyone else can help me...

 

[Rich]

Public Function FinancialYear(dDate As Date) As Integer
FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), 4, 1), 1, 0)
End Function

 

[bjackson]

IIf(DatePart("m",[D_paid])>3,DatePart("yyyy",[D_paid]) & "/" & DatePart("m",[D_paid]),DatePart("yyyy",[D_paid]-1) & "/" & DatePart("m",[D_paid]))

 

[aneats]

This is as close i have got to what i want so far. thanks. however, the return isn't quite what i am after... when the date is 19/04/1994, the return is 1994/4. this gives me the correct year in the first part, but the second part (4) is the month. what i need it to return is 1994/95 (in that format - yyyy/yy). also when i changed the '04' part in the date 19/04/1994, to '02', what is displayed is 1994/2 (the month has changed, but what i want to display is 1993/94, as this would be the previous financial year)

if you can help anymore, that would be great!

 

[aneats]

=IIf(DatePart("m",[txt_D_PAID])>3,DatePart("yyyy",[txt_D_PAID]) & "/" & Right(DatePart("yyyy",[txt_D_PAID])+1,2),DatePart("yyyy",[txt_D_PAID]-1) & "/" & Right(DatePart("yyyy",[txt_D_PAID]),2))

The above works in so far that it returns eg. 1994/95 if the month is greater than 3, however when the month is less than 3, it return 1994/94. it won't subtract the year, yet it will add the year in the first part.
has it got something to do with negative numbers?

 

[aneats]

I got it to work. I moved the bracket after the '-1' to in front of the '-1'.

=IIf(DatePart("m",[txt_D_PAID])>3,DatePart("yyyy",[txt_D_PAID]) & "/" & Right(DatePart("yyyy",[txt_D_PAID])+1,2),DatePart("yyyy",[txt_D_PAID]-1) & "/" & Right(DatePart("yyyy",[txt_D_PAID]),2))

The solution is...

=IIf(DatePart("m",[txt_D_PAID])>3,DatePart("yyyy",[txt_D_PAID]) & "/" & Right(DatePart("yyyy",[txt_D_PAID])+1,2),DatePart("yyyy",[txt_D_PAID])-1 & "/" & Right(DatePart("yyyy",[txt_D_PAID]),2))

 

[bjackson]

good to see that you are willing to have a go
and work it out your self,thats better than letting
some one do it all for you,you will certainly learn a lot
more

regards bjackson

 

[ahson88]

I NEED TO WORK OUT AN EXPRESSION WHICH FINDS THE DATE DIFFERENCE BETWEEN THE CURRENT DATE AND THE DATE ENTERED IN A FIELD IN A TABLE???

HELP ANY ONE??

 

[bjackson]

DateDiff("y", [fieldname], date())

that gives you the difference in days