fiscal year formula but with a error

[Joe8915]

Our physical year starts 10/01. This is what I have for the criteria, but I still getting a error. Can anyone tell me where Iam going wrong.

FYear: Year([Award Dtd])-IIf([Award Dtd]< _ DateSerial(Year([Award dtd]),10,01),1,0)

 

[DrSnuggles]

Can you expand on what you are trying to do please.

 

[DrSnuggles]

Looks fine, apart from dropping the '_'
FYear: Year([Award Dtd])-IIf([Award Dtd]< _ DateSerial(Year([Award dtd]),10,01),1,0)
to become:
FYear: Year([Award Dtd])-IIf([Award Dtd]<DateSerial(Year([Award dtd]),10,01),1,0)

What is the error?

 

[Joe8915]

The expression you have entered has an invalid .(dot) or ! operator or invalid parentheses

 

[Joe8915]

I have taken out the "_" and still get a error message. It has to be something real stupid that I am doing.

 

[Joe8915]

The expression you have entered has an invalid .(dot) or ! operator or invalid parentheses following a null constant. My dates start from 01/01/01 to 9/25/07.

 

[DrSnuggles]

What MS Access version are you running this in?

Plus I would remove this field and just check for Null values in the [Award Dtd] field first.

 

[Joe8915]

Iam running 2003

 

[Joe8915]

I think I trying to do this backwards. Maybe I should create a qry for each Physical Year, then create a new querry and in sert the criteria of FYear: Year([Award Dtd])-IIf([Award Dtd]<DateSerial(Year([Award dtd]),10,01),1,0)
.

 

[DrSnuggles]

What happened when running the query without this column but searching for Is Null results?
Or using this column and setting a criteria Not Is Null for the column [Award dtd]

 

[Joe8915]

comes up as a null

 

[Joe8915]

Damn, when I do this it shows up nothing. I love a good challange on a monday.
Year([Award Dtd])-IIf([Award Dtd]<DateSerial(Year([Award dtd]),10,1),0)

 

[boblarson]

Damn, when I do this it shows up nothing. I love a good challange on a monday.
Year([Award Dtd])-IIf([Award Dtd]<DateSerial(Year([Award dtd]),10,1),0)

Okay, first thing to ask yourself is this...

If I try to do a calculation like 2007-10/1/2007 is that going to give me an answer. Answer is NO. Well, that is what your calculation is doing.

What are you really wanting for an answer? What SHOULD the answer be?

 

[pbaldy]

In a brief test, this worked fine:

FYear: Year([Award Dtd])-IIf([Award Dtd]<DateSerial(Year([Award dtd]),10,01),1,0)

Are you sure the error is in that field?

(Bob, the function should subtract 1 or 0 from the year of the record, as appropriate)

 

[Joe8915]

Thanks pbadly and DrSnuggles........ its me......... Iam just not thinking it all way thru

Hey bob, good point. I am not thinking this problem thru, must be a monday thing.

It has to come from my querry to get it to come out right in the graph. So some how I have to use in the crietera, under the Award dtd. I have several Awards from 1997 to 2007. Now I have to break it down into Physical years.

Our Physical years start from 1 Oct to Sep 28 the following year. So this is where I thought I could put the following code:

FYear: Year([Award Dtd])-IIf([Award Dtd]<DateSerial(Year([Award dtd]),10,01),1,0)

So just how would I do it? This is where I am stump.

 

[pbaldy]

Let's recap, because when I put that in a query, I correctly get a "fiscal" year column. What error do you get? What is the full SQL of the query?

 

[Joe8915]

ok guys, kick me where sun don't shine. I was placing the code in the critera instead of the field in the querry.

I call it a ID Ten T error. If you never heard of this error just write it down on paper and it spells it out.

 

[Joe8915]

Well dang it, I thought I had it solved. My fiscal year for 2007 started on 1 Oct 2006 and ended on 28 Sep 2007. It picks up all the data except for 1 Oct 2006 to 31 Dec 2006. Any idea how I can alter the code to make this work.

Uh guys..................... thanks again

 

[boblarson]

I think you are having a problem since you are returning a single date field. Wouldn't you want a column for both the beginning year and the ending year?

 

[Joe8915]

Well, this will be my last post on this issue. I would like to thank everyone who help me out

The final out come was:

1. Put the formula in the correct area...............duh
2. The correct formula for me :
FYear: Year([Award Dtd])-IIf([Award Dtd]<DateSerial(Year([Award dtd]),10,1),1,0)+1