If multiple tables have that field name, the reference to the table is necessary.
OP:
As a general advice, when creating queries, first make sure you get the values using the simplest reference to them. In this case, you should start with just:
Run it and see if you got the value. If you have multiple tables/queries with FirstName as a field, then you must specify the table/query name before the field and then the dot, like this:
Code:
SocialAttendiesTbl.FirstName
Run it and see if you got the value. If your table/query or your field had spaces or symbols, then you must add square brackets (Access adds them anyway to make sure), for example:
Code:
[Social Attendies Table].FirstName
Run it and see if you got the value.
Once you have the reference right, use that reference as a whole to create expressions with it. For example, if you just add a Left function, access adds an alias automatically, like:
Input:
Becomes:
Code:
Expr1: Left(FirstName,2)
"
Speaking of aliases, you can also specify an alias for your SocialAttendiesTbl in its properties within the query builder. If you assign "s" for that table, then you can write it like this: s.FirstName within the columns, making things easier for you.
Finally, your expression can be any of the following:
Code:
FN:Left(FirstName,2)
FN:Left(SocialAttendiesTbl.FirstName,2)
FN:Left([SocialAttendiesTbl].[FirstName],2)
or if aliased
Code:
FN:Left(s.FirstName,2)
which Access modifies to
Code:
FN:Left([s].[FirstName],2)
EDIT:
Instead of
Code:
AbbreviatedName:= [SocialAttendiesTbl]![Left([FirstName],2)] & " " & [SocialAttendiesTbl]![Lastname]
Try
Code:
AbbreviatedName: Left([SocialAttendiesTbl].[FirstName],2) & " " & [SocialAttendiesTbl].[Lastname]