Unbound Text box in a form used for a calculation.

[johnnyBQue]

Plain and simple (famous last words)



Got a database that is working great
1 table - Employees
2 Queries
1) - QEmployees Extended
2) - QyrsEmp
2 Forms
1) - Employee Details (source control is "QEmployees Extended")
2) - Employee List (source control is "QEmployees Extended")

What I want to do...

in Form "Employee Details" there is field named "HireDate" this field is also found in the table (records) "Employees" and "QEmployees Extended"

When Form "Employee Details" is ran the "HireDate" appears as it should in the "HireDate" text box.

What I want to do is add a unbound text box to Form "Employee Details" named "txtYrsEmp" (Years Employed). and perform a calculation that will take the "HireDate" (date) and compare it to "Todays Date" and come up with the number of years employed rounded by 2 and show this result in the unbound text box called "txtYrsEmp"

Now I did a Query just to see if I could calculate what I wanted- "QYrsEmp" where in the first column I entered in the Field row (top) Expr1: EmployeeID from table Employee and in the second column I entered in the Field row (top) txtYrsEmp: Round(DateDiff("d"'[HireDate],NOW()/365.25,2))
Now the query returns the exact results I want so I know the calculation is possible at least here in the query.

SO how do I do it in the form as mentioned above?...

I'll take all comments now no matter how involved (I'm learning here...)

[ranman256]

dont use brackets....the calc wants the name of the TEXTBOX (if unbound)

=datediff("yyyy",hiredate, date)

[johnnyBQue]

Your concern about the bracket leaves me to think you are not happy with the formula in the Query. I'm not worrying about the formula in the Query unless you are suggesting this is what I use as the source in the unbound text box? (done that don't work). The formula I used in the Query above works just fine "in the Query" I tried to use it in the unbound textbox (as the control source) but Access doesn't like the "DiffDate" expression it uses "DateSerial"...
but if I understand what you are saying I should use =datediff("yyyy",hiredate,date) as the control source for the unbound text box in the Form? I tried but get the ole "Access has no clue what you are trying" error "#Name?" also I've noticed that when entering a formula into the control source box Access will add Brackets in where they seem to want them. Now I could be all wet and I'm just not understanding where you would like me to use the formula you entered above...I'm thick between the ears and have a hard time seeing the obvious....

What I need from you ranman256 is get your mind off judge wapner... and tell me what "YOU" would do if I ask you to create an unbound text box named "txtYrsEmp", place it in an existing Form, then using the data brought up in an existing text box named "txtHireDate", perform a calculation (within the new unbound text box) where the results will appear when the form is opened representing the years an employee has worked based on today's date...

Now I'll give you a hint that seems to be common to many responses I've gotten here and that is the calculation should be performed within the Form event...not from the Query...

don't stop on me now...lol...

[thebigthing313]

As a control source, your expression needs to be preceded by the = sign.

=Round(DateDiff("d", [HireDate])/365.25,2, Date)

[June7]

bigthing, don't follow the expression you configured. Maybe you meant:

=Round(DateDiff("d", [HireDate], Date)/365.25,2)

ranman's expression should return age to nearest year, which means a 3-month old will show as age 1 year.

bigthing's approach will give ages of less than 1 year as a decimal year.

Yes, the brackets are not the issue.

Either expression should work in query or textbox.

Both are an estimate because years do not all have the same number of days, hence bigthing's use of 365.25 for the number of days in year.

[johnnyBQue]

Thanks guy for all the input there's my Query....this is the results...here's the formula from the Query copied and pasted...txtYrsEmp: Round(DateDiff("d",[HireDate],Now())/365.25,2) the results I get as you see above are correct for the years these employees have been working which was what I'm looking for. So the formula works as is "in the Query"...

lol...I'm not trying to find the age of someone...just how long they've work based on their hire date...

NOW the big thing is I placed the above formula with the equal sign (=) in front meaning =Round(DateDiff("d",[HireDate],Now())/365.25,2)) into the unbound text box's control source in the Form and I ACTUALLY GET A NUMBER BACK! WOOT! but it's the wrong number, looking at the Query result above for Employee 12 the years employed is 13.38 the results I get back in the unbound text box is -36953...SO it's actually performing some type of calculation but it seems that the data it's using for the calculation may not be ....formatted correctly? wrong type?...so this is where I'm hung now...

Keep it coming you guys are doing great....almost there....and I'm enjoying the ride.....

[June7]

Age or work - same calc and same issues.

Why do the calc in textbox anyway? Usually better to do calcs in query, make query the RecordSource, bind textbox to field.

[johnnyBQue]

I tried the to make the Query field the control source, but the results from the query will not show up in the text box (that was really weird should have worked np). BUT....I changed the formula and I now get the same answer as my query returned I put the formula in the control source as =Round(DateDiff("d", Now(), [HireDate] /365.25, 2))....BUT I got a negative number (-13.39) SO I changed the formula to =Abs(Round(DateDiff("d", Now(), [HireDate]) / 365.25, 2)) AND .....wait for it........WA'LA.....13.39

Thanks guys for all your help...this has been a successful lesson and is now resolved you guys have a great day...and I hope to your around to help out in the future...

but...I wonder why the negative?....in the first place...

[thebigthing313]

I guess DateDiff always subtracts arg2 from arg3, and doesn't test which date is earlier.

[johnnyBQue]

yes I believe so because I moved the "arg" around an got different results depending on where i put them...
but I found out why my earlier formula wouldn't work =Round(DateDiff("d",[HireDate],Now())/365.25,2)) normally Access warns you that you got TOO many parenthesis it never did I remove the last parenthesis =Round(DateDiff("d",[HireDate],Now())/365.25,2) and the formula works great and no negative sign


Thanks again guys for all your help "solved" totally...

[rpeare]

do not use the formula /365.25 at all, it is inaccurate. If you want the year to roll over on their hire date you want something like:

Year(Now())-Year(birthdatefield) + (DateSerial(Year(Now()), Month(birthdatefield), Day(birthdatefield))>Now())

[johnnyBQue]

I agree100% if I need to be precise (dead on) but in this case all I actually need is the year... the two decimal places I plan on taking off...I included it just to have a working example of rounding off...but thanks for the code I'll store it for future reference.

But lets take an example hire date 9/13/2010 the number of days that have passed to today 11/13/2014 = 1522 days lets divide that by 365.25 and you get 4.167008 bla bla bla...now most
Calculate duration between two dates – results that i get on the internet return a result of 4 years 2 months.

take the 2 month and figure what percent that is of 12 months.....you get 16.67% or .167..... 0r 4.167.....for me that is close enough... Have a great day....

And thanks guys again for all your help and tips....

[rpeare]

I gave you a formula that would be exact and increment on a birth date (instead of a hire date) There should be no decimals at all. You stated all you wanted was the number of years the person had been employed.

=Year(Date())-Year([HireDate])+(DateSerial(Year(Date()),Month([HireDate]),Day([HireDate]))>Date())

same formula, just substituted the field name, and changed it from 'now' to 'date' because now has some time implications that you don't care about.