Syntax in access query using square brackets bested in another set

[Budatlitho]

Hello, again...
er... thtle s/b "nested" not "bested" (fat fingers)
I want to select a substring of a field's value in the Expression Builder in an Access query.

Here's what I have:

FN:= [SocialAttendiesTbl].[Left([FirstName],2)]

I *think* I've done this in the past, but I really am getting older.



Thanks in advance.

Bud

[Welshgasman]

Show what does it show?
I would use Left on the WHOLE reference and not just the fieldname, plus you could omit the table name. That is just the access way, as the From signifies where the data comes from.

[Micron]

Square brackets don't go around expressions. Field names, sure, but not expressions. Then again, you can't use a function as part of the field reference. Perhaps you don't need the table reference at all.

FN:Left(FirstName,2)

[Budatlitho]

Square brackets don't go around expressions. Field names, sure, but not expressions. Then again, you can't use a function as part of the field reference. Perhaps you don't need the table reference at all.

FN:Left(FirstName,2)

Guess I poorly worded my problem.. perhaps this will be clearer:
Here's what I'm looking for...
Attendie's name, consisting of the first two characters of their Firstname and a space and their Lastname

AbbreviatedName:= [SocialAttendiesTbl]![Left([FirstName],2)] & " " & [SocialAttendiesTbl]![Lastname]

When running the nested [] throw an error.

I need to reference the table name, as there is another table with Firstname and Lastname in the query.

[Edgar]

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:

FirstName

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:

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:

[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:

Left(FirstName,2)

Becomes:

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:

FN:Left(FirstName,2)
FN:Left(SocialAttendiesTbl.FirstName,2)
FN:Left([SocialAttendiesTbl].[FirstName],2)

or if aliased

FN:Left(s.FirstName,2)

which Access modifies to

FN:Left([s].[FirstName],2)

EDIT:
Instead of

AbbreviatedName:= [SocialAttendiesTbl]![Left([FirstName],2)] & " " & [SocialAttendiesTbl]![Lastname]

Try

AbbreviatedName: Left([SocialAttendiesTbl].[FirstName],2) & " " & [SocialAttendiesTbl].[Lastname]

[Budatlitho]

EUREKA!
FN: Left([SocialAttendiesTbl].[FirstName],2) & " " & [SocialAttendiesTbl].[LastName]

It woikes! Thanks.

[Welshgasman]

So logic says if you have same fields in two tables in the query, then you need to use the Left() function on one of them?

[Edgar]

If your first name was Welsh and your last name was Gasman, OP was trying to get "We Gasman", which is done using the Left() function on FirstName and concatenating the result with LastName. OP needed to include the reference to the table SocialAttendiesTbl because there were other tables or queries that had FirstName as a field being picked up in the final query. Maybe there is another table called OperationStaffTbl where there would be a field for FirstName too, so these are the tables:

SocialAttendiesTbl: Id, FirstName, LastName, EventID
OperationStaffTbl: Id, FirstName, LastName, Salary, EventID

And you need to return return the FirstName of the person from the OperationStaffTbl associated to each person in SocialAttendiesTbl:

You shouldn't do this:

SELECT FirstName, FirstName, LastName
FROM SocialAttendiesTbl
INNER JOIN OperationStaffTbl
ON EventID = EventID;

You should do this:

SELECT os.FirstName AS oFirstName, sa.FirstName AS sFirsName, sa.LastName AS sLastName
FROM SocialAttendiesTbl sa 
INNER JOIN OperationStaffTbl os
ON sa.EventID = os.EventID;

But OP wanted to return "We Gasman", so:

SELECT os.FirstName AS oFirstName, LEFT(sa.FirstName, 2) & " " & sa.LastName AS SocialAttendeeName
FROM SocialAttendiesTbl sa
INNER JOIN OperationStaffTbl os
ON sa.EventID = os.EventID;

[Micron]

OP needed to include the reference to the table SocialAttendiesTbl because there were other tables or queries that had FirstName as a field

Where is this stated, because I can't see it. In post 4 brackets were still around the expression even though I said they cannot be.

[Edgar]

Micron,
#2 and #3 are only partially correct, reference is necessary when there are repeated fields, explained in detail in posts #5 and #8
#4 OP confirms repetition along with the problematic syntax
#5 is how to fix
#6 is confirmation of fix
#7 is wrong
#8 is why #2, #3 and #7 are wrong
#9 ??? what do you mean?

[Micron]

OK, I missed the part where it was declared that more than one table involved had the same field name.
What do I mean?
[Left([FirstName],2)]

I say, you can't wrap an expression/function in brackets.
Maybe I'm full of it.