Hi all, a quick query...

[Desmondo]

I have an expression in a query which has text and a field i.e. Expr1:"this is the"&[School] in which case the school shows as the primary key number instead of the full text name. As soon as I remove the text from in front of the [] it reverts to the text name. I am sure this must be something simple I have overrlooked but can't seem to get it.

[June7]

That's because Lookup aliases are not carried into fields created with an expression. You need to join the tables on the PK/FK and then all the related info will be available, including the field that actually has the school name.

[Desmondo]

Thanks for your reply June7. Due to the amount of cross referenced data required I am using 2 query's to make this new query where the PK/FK have been joined and all expressions work except this one. The [school] field is in one of those joined query's and when the new query is viewed as a datasheet the school shows up as the full name not the PK. When I create an expression in this new query e.g. Expr1: [school] it also shows as the full name (how I need it) however, as soon as I place quoted text in front of it, e.g. Expr1: "This is the"&[school] it reverts to displaying the PK. I hope I am explaining this well enough. In the past I have always managed to find the answers somewhere online however, this is the first time I have had to try to explain a problem to someone.

[June7]

My comment is the same. Either include the school name field in other query so it will also be available in this new query or include the table with the school name in this query.

[Desmondo]

My comment is the same. Either include the school name field in other query so it will also be available in this new query or include the table with the school name in this query.

Thanks so much again June7 for attempting to assist with my stupidity. I am usually very careful with my tables etc and couldn't quite work out this one. You're response verified I had done that side right however, something was still amiss so I kept checking things and found it to be a Data Type Mismatch between the original school list table and the table that stores the school from a combo lookup. The field in my list was TEXT and my combo stored field was NUMBER... So simple, I can't believe I missed it ! Dugghhh ! Cheers

[June7]

I am not understanding how that was the cause of your issue but if you feel it is resolved, congratulations.

Also occurred to me that instead of putting that text before the field in query, put it in a label on form or report and textbox bound to the field next to label.