Textbox Concatenation Problem

[modest]

I was wondering if someone has had this problem before:

I have a textbox on a report with the following control source, where SupF and SupL are the supervisor's first and last name respectively:
="Supervisor: " & [SupF] & " " & [SupL]


What is being displayed when the report is run is Supervisor: [SupF], where SupF is an actual name from the query the report is based on, but SupL is being left off. Both fields exist in the query, if I try it the other way around ="Supervisor: " & [SupL] & " " & [SupF] then the last name shows, but not the first name.


Things to consider:
- The width of the textbox is fine. I have even expanded it to the width of the report
- Can Grow/Can Shrink/Hide Duplicates are all set to No, but these shouldn't matter
- I renamed the textbox to txtEmployee_Sup, so no name conflicts should be a problem
- The problem exists when the control source is =[SupF] & [SupL] or the reverse =[SupL] & [SupF] - only one field is being retrieved


If you have any suggestions, please let me know

 

[KenHigg]

Concat in the query not the report/form?

ken

 

[neileg]

I would do the concatenation in the underlying query. Doing it in the report requires the data to exist in the report.

 

[modest]

I'd rather not use a redundant field in the query if I don't have to - and I do need the names separated for formatting. The same query is used for form filtering and the formatting of the supervisor name on the form is [SupL] & ", " & [SupF].

 

[KenHigg]

Hum... I used the following & it worked fine:

="Name: " & [lname] & " " & [fname]


ken

 

[modest]

I know, I have an employee textbox just like it, right next to it, which works perfectly.

= [EmpF] & " " & [EmpL]

There are some groupings on the report, but I don't think that it would be affecting this, since [SupF] and [SupL] work fine by themselves. I just don't know what the problem could be. When push comes to shove, I will add it in the query, but I was hoping you guys could help me brainstorm what the problem is.

 

[modest]

For some reason there is a problem (maybe a lag) with processing the data at runtime. It seems the best way to handle the problem was to create two invisible textboxes to store the first and last name. Then concatenate their values in a visible text box.

 

[GaryPanic]

Darn - i was just going to suggest this ...
g

 

[modest]

It's okay, next time.

 

[KenHigg]

I wonder if you put it in an eval()?

 

[modest]

eval brings error.

I do thank you though, because I wish I would have remembered eval() with this other thing I was doing: calling multiple functions with similar names

 

[KenHigg]

You really have my curosity up now. Any chance to post a sample db that contains the error?

 

[neileg]

I'd rather not use a redundant field in the query if I don't have to

Why? I can't think of any reason why this might be a problem. I even suspect that doing it in the query will be more efficient, since stored queries are compiled.

 

[Rich]

The problem with a calculated field in a query is that the query has to complete all the calculations before opening, the same is not true for calculated fields on the report

 

[neileg]

Thanks, Rich. You have reduced my level of ignorance by a small fraction!

 

[Rich]

Glad to be of so little help!

 

[modest]

Unfortunately, company policy will not allow me to post a copy of the database, and it would be too much time to break it down into a prototype that may/may not reproduce the same error.

If I find time, I will try to recreate the problem and let you guys play with it until your heart is content . Thank you all for attempting.