Textbox to display percentage variance

[craigprice]

Hi all,

I'm new to access programing and have been working on a handover form for shift workers, as part of the handover we have to include call volumes forcast and actual calls received over a period.

I have a form which links to my table (Handover) the two fields are named, "239 Forcast 18:30-00:00" & "239 Actual 18:30-00:00" I then want to add a third column with percentage variance to show wether the percentage of calls is higher or lower than forcast.

I manually input the data into the forcast and actual fields and want this to then populate a textbox for the variance percentage next to this.

So far I have tried:
=Sum(100-[239 Forcast 18:30-00:00]/[239 Actual 18:30-00:00]*100)

The above sum seems to be a little unstable and when switching to view different handovers the value in the variance stays the same, regardless of the figures in the mentioned fields, does anyone have a solution to this?

I believe I would need to create a query of some sort to do this but once again I am new to this, I have trawled the internet in hope of finding an answer but have so far been unsuccessful.

Thanks in advance for any help,

Craig

 

[CJ_London]

May need to know a bit more but for now, your formula would appear to be wrong.

try

=([239 Actual 18:30-00:00]/[239 Forcast 18:30-00:00])-1

and format your field as percentage. This will show the percentage increase or decrease of actual over forecast.

The names [239 Actual 18:30-00:00] and [239 Forcast 18:30-00:00] should be the name of the relevant controls on your form.

As a side note, your field names are truly and may cause other issues (ideally you should exclude spaces and none alphanumeric characters)- can you not rename them to say ChangeOverForecast and ChangeOverActual?

 

[craigprice]

That works exactly as I wanted, can't believe how simple that actually was....

Yeah my field names are a bit of a mess, I didnt realise until I'd started developing much further into it that names formatted as such can cause such problems so I will be working at re-naming all the fields before putting it out live.

Thanks very much for your help!