Using an Unbound Text boxes and calculation

JohnnyBQue

Registered User.
Local time
Today, 03:01
Joined
Oct 31, 2014
Messages
25
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 Qurey 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...) :D
 
You should be able to uses that expression ; Round(DateDiff("d"'[HireDate],NOW()/365.25,2))

In your forms unbound control source just put an = sign in front of it, assuming HireDate is on the form?
 
well I GOT A NUMBER BACK THIS TIME!...but it's not the right value....lol...my HireDate is 6/25/2001 in my Query using the above formula I get 13.38 (years) and in my form using the formula in the unbound text box source control I get -36953...It's obviously performing a calculation but is it getting confuse by the data type not being set correctly or something...within the fields?....

also when you wrote "In your forms unbound control source" you did mean the unbound text box"s "control source" that I add to the form?... not the actual form's control source which is currently bound to a Query.
 
Looks like a bracketing problem. The logic Minty intended, I think, is this . . .
Code:
dim daysSinceHiring as long
dim yearsSinceHiring as single

daysSinceHiring = DateDiff("d", Now(), [HireDate]) 
yearsSinceHiring = daysSinceHiring / 365.25
yearsSinceHiring = Round(yearsSinceHiring, 2)
So that would be . . .
Code:
=Round( DateDiff("d", Now(), [HireDate]) / 365.25, 2)
 
I was on my tablet so couldn't check the actual formula- I just copied it as the OP has said it was working...

In answer to
"In your forms unbound control source" you did mean the unbound text box"s "control source" that I add to the form?.
Yes the unbound text boxes control source.
 
Give Mark Half a cigar...and Minty cookie....I changed the formula and I now get the same answer as my query returned ....BUT I'm getting a negative number (-13.39) SO not being totally helpless 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...
 
Oh you get the whole cigar and two cookies....That did it no need for the "Abs"
=Round(DateDiff("d",[HireDate],Now())/365.25,2)
BUT if you look at my earlier post this is the exact formula that wouldn't work...and I found the reason was the extra parenthesis at the end of my earlier version was the culprit.

Thanks again.... now no i not dotted and no t not crossed very nice...
 

Users who are viewing this thread

Back
Top Bottom