Updating fields (1 Viewer)

[RichieP]

I've attached an example access 97' database.


I need the unbound "Estimate of duration" box on the right to automatically update the "Duration" box on the left.

Any ideas cause I'm all out of em. I have a feeling that I don't need an unbound text box at all and I just need a bit of code in an after update event somewhere.

Cheers for any help.

 

[missinglinq]

Don't think you really need even that. Just need to copy a couple of things from your unbound box to your "Duration" box. Did that and it's attached.

Hope this helps. (Yes, you can think too much!)

The Missinglinq

 

[RichieP]

I see what you've done - I tried that - but it doesn't store the value in the table which is what I need. When the control source is changed it stops it being related to the table.



Can anyone help?

Cheers

 

[Robert Dunstan]

Hi RichieP

Ok I had a look at missinglinq's example and modified it slightly.

In your text box 'Duration' change the control source to 'Duration' so it's bound to the field in your table. Change the name of 'Estimate of Duration' text box (the one on the right of your form) to txtEstimate. Add a command button and place the following code in its On_Click event.

Private Sub cmdCalc_Click()

' Calculate estimate

    Me.Duration.Value = Me.txtEstimate
    
End Sub

HTH
Rob

 

[RichieP]

Cheers, did the trick.

Rich

 

[RichieP]

another problem

Ha ha new problem!

I've changed it slightly.

When the estimation of something is a whole year (eg. say 2 years or 3 years) then the estimation comes out as "1 year and 12 months" and "2 years and 12 months" and so on.

I need this to change to "02 years and 00 months" and "3 years and 00 months" and so on . . . . . for every whole year when the user types in the duration.

This has been anoying me for quite a while now so please help! Thanks.

 

[Mile-O]

Get rid of the format and put this as the control source:


=DateDiff("yyyy",[Start Date],[End Date]) & IIf(DateDiff("yyyy",[Start Date],[End Date])=1," year and "," years and ") & (DateDiff("m",[Start Date],[End Date]) Mod 12) & IIf((DateDiff("m",[Start Date],[End Date]) Mod 12)=1," month"," months")

 

[RichieP]

That is so close!
It works apart from the fact that when the months don't = 12 then the year still adds on 1 (e.g. instead of "12 years and 9 months" it shows "13 years and 9 months").

Is there anyway of getting the year to add 1 ONLY if the months = 12? (and obviously if the months = 12 then they need to be set to 0).

Thanks for you time, really appreciated!

Rich

One more thing - will changing the control source stop the "Year and months" being stored in the table?

 

[Mile-O]

That is so close!
It works apart from the fact that when the months don't = 12 then the year still adds on 1 (e.g. instead of "12 years and 9 months" it shows "13 years and 9 months").

It seems okay to me.


What dates are you putting into the start and end date boxes?

Can't you come up with something better than those crude Input Masks as I don't know whether this is dd/mm/yy or mm/dd/yy format just by looking at the form.

 

[RichieP]

Day / month / year

Sorry, this was just an example I knocked up really quickly.

Look at the attached picture. Start date is 6th August 1981 and the end date is 7th July 2003.

That is 21 years and 11 months (I think). Your estimate (bottom in black) makes it 22 years and 11 months.

 

[Mile-O]

I put my estimate expression in the top field, the one marked Estimate; not Duration.

 

[RichieP]

Ok, sorry that was just me being stupid. The estimate is still wrong on some dates tho . . . .

For example . . . . . .

Put in 06/08/1981 (6th August 1981) in the start date and 06/08/2003 (6th August 2003) in the end date. This gives the correct answer of 22 years and 0 months.

However

Put in 06/08/1981 (6th August 1981) in the start date and 06/07/2003 (6th July 2003) in the end date. You would expect it to give 21 years and 11 months but it give 22 years and 11 months.

Do you see?

Thanks very much for your time.

Richie