if statement error

  • Thread starter Thread starter annysjunkmail
  • Start date Start date
A

annysjunkmail

I am using the following code (gleamed from this Newsgroup) which
calculates financial years (1 April - 31 March).

Financial Year:
IIf(Format([LOOAcceptedDate],"m")<4,Format([LOOAcceptedDate],"yyyy")-1
& "-" &
Format([LOOAcceptedDate],"yyyy"),Format([LOOAcceptedDate],"yyyy") & "-"
& Format([LOOAcceptedDate],"yyyy")+1)

I have run into a problem in that if a date does not exist in field
LOOAcceptedDate then the statement returns an #Error. I am not sure how
to resolve this problem and was wondering could someone help me

Regards
Tony
 
I am using the following code (gleamed from this Newsgroup) which
calculates financial years (1 April - 31 March).

Financial Year:
IIf(Format([LOOAcceptedDate],"m")<4,Format([LOOAcceptedDate],"yyyy")-1
& "-" &
Format([LOOAcceptedDate],"yyyy"),Format([LOOAcceptedDate],"yyyy") & "-"
& Format([LOOAcceptedDate],"yyyy")+1)

I have run into a problem in that if a date does not exist in field
LOOAcceptedDate then the statement returns an #Error. I am not sure how
to resolve this problem and was wondering could someone help me
Click to expand...

If IsDate([LOOAcceptedDate]) Then
IIf(Format([LOOAcceptedDate],"m")<4,Format([LOOAcceptedDate],"yyyy")-1
& "-" & Format([LOOAcceptedDate],"yyyy"),Format([LOOAcceptedDate],"yyyy") &
"-"
& Format([LOOAcceptedDate],"yyyy")+1)
End If
 
Thank you for your reply.
This method does not seem to work in the Query Design Grid, which is
what I use when I run the code, i.e I have the code in a colum in a
query like so...

SELECT tblApplication.LOOAcceptedDate,
IIf(Format([LOOAcceptedDate],"m")<4,Format([LOOAcceptedDate],"yyyy")-1
& "-" &
Format([LOOAcceptedDate],"yyyy"),Format([LOOAcceptedDate],"yyyy") & "-"
& Format([LOOAcceptedDate],"yyyy")+1) AS [Financial Year1]
FROM tblApplication;


Any ideas?

Thanks
Tony
 
Thank you for your reply.
This method does not seem to work in the Query Design Grid, which is
what I use when I run the code, i.e I have the code in a colum in a
query like so...

SELECT tblApplication.LOOAcceptedDate,
IIf(Format([LOOAcceptedDate],"m")<4,Format([LOOAcceptedDate],"yyyy")-1
& "-" &
Format([LOOAcceptedDate],"yyyy"),Format([LOOAcceptedDate],"yyyy") & "-"
& Format([LOOAcceptedDate],"yyyy")+1) AS [Financial Year1]
FROM tblApplication;

Any ideas?
Click to expand...


SELECT tblApplication.LOOAcceptedDate,
IIF(IsDate(LOOAcceptedDate),

IIf(Format([LOOAcceptedDate],"m")<4,Format([LOOAcceptedDate],"yyyy")-1 & "-"
&
Format([LOOAcceptedDate],"yyyy"),Format([LOOAcceptedDate],"yyyy") &
"-"
& Format([LOOAcceptedDate],"yyyy")+1) AS [Financial Year1],"No Date")
AS Years
FROM tblApplication;
 
Hi Tony,

Adapting method to find quarter
when fiscal year starts on 1 April
(subtract 3 months from date), I
believe this works also for finding
fiscal year of a date -- plus it handles
NULL's.

Here be my limited testing in
Immediate window:

MyDate=#3/12/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate))
2004
MyDate=#4/12/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate))
2005
MyDate=#12/31/04#
?DatePart("yyyy",DateAdd("m",-3,MyDate))
2004
MyDate=NULL
?DatePart("yyyy",DateAdd("m",-3,MyDate))
Null


FinancialYear: DatePart("yyyy",DateAdd("m",-3,[LOOAcceptedDate]))

I don't know why you need anything other
than this in your query (why bog it down
with further calculations?). In a report you
could take FinancialYear and make your
range with simple calc in a textbox.

But hopefully you can see what you
need to further do if you insist.

good luck,

gary
 
Gary,

This is an excellent and simple solution and works perfectly.
I am using the original posted method as this is how Management wish to
see financial reports, i.e total spend in fiscal year style like
2001-2002, 2002-2003, etc.

Thanks for your advice
Tony
 
This is an excellent and simple solution and works perfectly.
I am using the original posted method as this is how Management wish to
see financial reports, i.e total spend in fiscal year style like
2001-2002, 2002-2003, etc.
Click to expand...
Hi Tony,

For me personally, all non-action queries
provide sources for forms or reports.

In either, I believe you will display your
"fiscal year" in a textbox.

And I would have no problem making
the source of that textbox

= CStr([FinancialYear]) & '-' & CStr(([FinancialYear] + 1))

Just one man's opinion....

good luck,

gary
 
Back
Top