Formula to pick up on future data when table is updated

B

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
 
G

Guest

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]))
 
B

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



[email protected] said:
Hi,
Click to expand...
Was wondering if anyone might be able to help me. I have the following
formula set up
Click to expand...
=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])))))))))
Click to expand...
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.
Click to expand...
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.
Click to expand...
Thanks very much- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
G

Guest

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


[email protected] said:
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



[email protected] said:
Hi,
Click to expand...
Was wondering if anyone might be able to help me. I have the following
formula set up
Click to expand...
=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])))))))))
Click to expand...
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.
Click to expand...
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.
Click to expand...
Thanks very much- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report with disappearing subreports - null values 3
Mapping strings to integers 5
Variable Field on A report 1
SQL Crosstab Question 2
How do I custom sort records in a report? 2
Excel Formula, HELP!!! 2
6 month time bracket in report header 2
=AVERAGE(IF(INT(MONTH(C:C)+2/3)=4 5

Top