Formula to pick up on future data when table is updated

[benonibriggs1978]

Hi,

Was wondering if anyone might be able to help me. I have the following
formula set up

=IIf([Month]="April",[April FY06],IIf([Month]="May",[May
FY06],IIf([Month]="June",[June FY06],IIf([Month]="July",[July
FY06],IIf([Month]="August",[August FY06],IIf([Month]="September",
[September FY06],IIf([Month]="October",[October
FY06],IIf([Month]="November",[November FY06],IIf([Month]="December",
[December FY06])))))))))

Basically what this formula is saying is if a particular month is
selected in the [Month] field, then it should show the results in the
[April FY06] field and so on. The problem here is my table is updated
up to the end of August and does not perform any calculation because
there isn't any data from September to DecemberIt brings up an error
message (#Name). If I leave the formula up to August, like this
=IIf([Month]="April",[April FY06],IIf([Month]="May",[May
FY06],IIf([Month]="June",[June FY06],IIf([Month]="July",[July
FY06],IIf([Month]="August",[August FY06]))))), then the formula works
because the data up to August is available.

The only way for me to do this is by changing the formula each time a
new month is uploaded onto my table. I don't really want to do this
and hoping there is some other way of doing this.

Thanks very much

 

[KARL DEWEY]

Try this instead of IIF statement ---
Your Field Label: [Month] & " FY06"
But what do you do each year? Add a new table or edit all queries, form,
and reports?

If you use real dates and then calculate FY you will not need to change all
the time.
If your FY starts in April then use this ---
DatePart("yyyy",DateAdd("m",-4,[YourDate]))

 

[benonibriggs1978]

Try this instead of IIF statement ---
Your Field Label: [Month] & " FY06"
But what do you do each year? Add a new table or edit all queries, form,
and reports?

If you use real dates and then calculate FY you will not need to change all
the time.
If your FY starts in April then use this ---
DatePart("yyyy",DateAdd("m",-4,[YourDate]))

--
KARL DEWEY
Build a little - Test a little

Hi,

Was wondering if anyone might be able to help me. I have the following
formula set up

=IIf([Month]="April",[April FY06],IIf([Month]="May",[May
FY06],IIf([Month]="June",[June FY06],IIf([Month]="July",[July
FY06],IIf([Month]="August",[August FY06],IIf([Month]="September",
[September FY06],IIf([Month]="October",[October
FY06],IIf([Month]="November",[November FY06],IIf([Month]="December",
[December FY06])))))))))

Basically what this formula is saying is if a particular month is
selected in the [Month] field, then it should show the results in the
[April FY06] field and so on. The problem here is my table is updated
up to the end of August and does not perform any calculation because
there isn't any data from September to DecemberIt brings up an error
message (#Name). If I leave the formula up to August, like this
=IIf([Month]="April",[April FY06],IIf([Month]="May",[May
FY06],IIf([Month]="June",[June FY06],IIf([Month]="July",[July
FY06],IIf([Month]="August",[August FY06]))))), then the formula works
because the data up to August is available.

The only way for me to do this is by changing the formula each time a
new month is uploaded onto my table. I don't really want to do this
and hoping there is some other way of doing this.

Thanks very much- Hide quoted text -

- Show quoted text -

Hi,

Thanks for your response. I don't think I made myself very clear. The
[Month] field doesn't appear in my table.The [Month] were typed out
values in a combo box.My real Month's data appears in this format
[APRIL FY06]. So in my form when I select April in the [Months] field
then it recognises it as [APRIL FY06] and shows the values, hence the
reason for the IIF statements.. What I do each month is update the
table with the monthly values e.g this month will be [SEPTEMBER FY06].
This is how I get it for my table.If I put the all the IFF statements
from september to December, then an error comes up because there isn't
any data for the rest of the year. What can I do so that I don't have
to update the staements each month. Hope you can help.Thanks

 

[KARL DEWEY]

What I do each month is update the table with the monthly values e.g this
month will be [SEPTEMBER FY06]. This is how I get it for my table.
You said 'update' so does that mean you replace the data each month or add
new data?A properly constructed IIF statement should not give an error due to lack of
data but just display the false results. I just noticed you DO NOT have it
correct as you do not have a result for false.
Change the end to this ---
.....IIf([Month]="November",[November FY06],[December FY06]))))))))

Can you post your completed SQL statement.
--
KARL DEWEY
Build a little - Test a little

Try this instead of IIF statement ---
Your Field Label: [Month] & " FY06"
But what do you do each year? Add a new table or edit all queries, form,
and reports?

If you use real dates and then calculate FY you will not need to change all
the time.
If your FY starts in April then use this ---
DatePart("yyyy",DateAdd("m",-4,[YourDate]))

--
KARL DEWEY
Build a little - Test a little

Hi,

Was wondering if anyone might be able to help me. I have the following
formula set up

=IIf([Month]="April",[April FY06],IIf([Month]="May",[May
FY06],IIf([Month]="June",[June FY06],IIf([Month]="July",[July
FY06],IIf([Month]="August",[August FY06],IIf([Month]="September",
[September FY06],IIf([Month]="October",[October
FY06],IIf([Month]="November",[November FY06],IIf([Month]="December",
[December FY06])))))))))

Basically what this formula is saying is if a particular month is
selected in the [Month] field, then it should show the results in the
[April FY06] field and so on. The problem here is my table is updated
up to the end of August and does not perform any calculation because
there isn't any data from September to DecemberIt brings up an error
message (#Name). If I leave the formula up to August, like this
=IIf([Month]="April",[April FY06],IIf([Month]="May",[May
FY06],IIf([Month]="June",[June FY06],IIf([Month]="July",[July
FY06],IIf([Month]="August",[August FY06]))))), then the formula works
because the data up to August is available.

The only way for me to do this is by changing the formula each time a
new month is uploaded onto my table. I don't really want to do this
and hoping there is some other way of doing this.

Thanks very much- Hide quoted text -

- Show quoted text -

Hi,

Thanks for your response. I don't think I made myself very clear. The
[Month] field doesn't appear in my table.The [Month] were typed out
values in a combo box.My real Month's data appears in this format
[APRIL FY06]. So in my form when I select April in the [Months] field
then it recognises it as [APRIL FY06] and shows the values, hence the
reason for the IIF statements.. What I do each month is update the
table with the monthly values e.g this month will be [SEPTEMBER FY06].
This is how I get it for my table.If I put the all the IFF statements
from september to December, then an error comes up because there isn't
any data for the rest of the year. What can I do so that I don't have
to update the staements each month. Hope you can help.Thanks