Getting a Query result to a unbound text box in a form.

[johnnyBQue]

This is probably an issue that I see the solution to, but don't because it's right under my eye's...but here's the stump...


Got one Record, one Query supporting one Form...it's just the basic employee data base with the key field being 'EmployeeID". I have a field in the table called "HireDate" which is used in the Query ending up in the form. got no issues with anything every things working as it should. I wanted to enter a unbound text box in the form to generate the years an employee has worked. I used the unbound text box because when I tried to generate a calculated field in the table I kept getting an error because the expression (Round(DateDiff("d",[HireDate],NOW())/365.25,2) wasn't supported because of "DateDiff" I think the expression supported is "DateSerial". SO I made a Query to calculate the the years employed using "EmployeeID" in one column and the "round" expression above and it works just fine. I get the result I need, EmployeeID with the years worked in the next column.
My problem now is getting this result to the unbound text box in the form. I can't get the data to appear when the information for a particular employees is retrieved (by "EmployeeID" or "FullName" which is generated in the Query). This may be a SQL solution I haven't looked into that as of yet and will be doing so while I wait for any suggestions,

Thanks guys

[ranman256]

put the calcd field in the query, and bind the text box to the field, just as you would any other field.

[johnnyBQue]

Okay I took the field from the Query "YrsEmp" and made it the control source in the unbound text box, and when I run the form (bring up data on an employee) all I get in the unbound text box is "#Name?"... now I know this is Access's way of saying "I don't know what you want". It could be a misspelled field, an unknown function or incorrect syntax (i can't see any issues here).

Now answer me this I have "YrsEmp" in my only table as a text field because I can't enter a Calculated expression that is relevant to what I need...But "YrsEmp" is also the field heading in my Query where the (round) calculation is performed and the results that I want appear for years employed. Now in my only Form like I mentioned above, I've bound the field "YrsEmp" from the "YrsEmp" Query to the unbound text box using =[QYrsEmp]![YrsEmp] this may not be correct. Should I be bounding it to the "YrsEmp" from the table? and updating the table field with the query results?

Another note that may be relevant to this issue is my Form is sourced to another Query not the single table in my database. ALL the fields (except the unbound text box) are sourced through fields within this Query.

Could it be an issue that I'm pulling from two different Queries into one form? or that the single Table filed "YrsEmp" isn't set as a Calculated field?

again thanks for the help...

[johnnyBQue]

Lets marked this as Solved I really have issues with explaining what it is I'm having trouble with so I re-posted my issue in the "Form" Forums if anyone would like to go there and comment.