Login form

[Sheba]

hi, I'm having a bit of trouble with representing this query in Access:



SELECT tblUser.UserID, [LastName] & "," & [FirstName] AS Fullname
FROM tblUser
ORDER BY tblUser.LName, tblUser.FName;

In Query design it looks like the table below but I get an error saying that my expression has invalid syntax. Could you point out what I'm doing wrong here please? I can't seem to pick it out. Maybe been staring at the screen for too long..?

The UserID combo box only shows the LastName with the comma

Field	UserID	Expr:1 [LastName] & ","	FullName: & [FirstName]
Table			tblUser

Any help is greatly appreciated!

[June7]

Why are the field names inconsistent? Which is correct:

FName or FirstName

LName or LastName

[NTC]

Build the query using query Design View rather than attempt to construct sql free form....once the query works the way you want it - then change it to SQL View and it will give you the syntax. This is one of the great aspects of the Access product.

[Sheba]

hey thanks June7 and NTC. I've fixed this one. The correct syntax below. I took out the square brackets

SELECT UserID, Lastname & “, ” & Firstname AS Fullname
FROM tblUser
ORDER BY Lastname, Firstname;

[June7]

The brackets don't hurt and are in fact required if field and object names have spaces or special characters/punctuation (underscore is exception). Access will actually throw them into the SQL if you don't. But beware, sometimes Access won't recognize the name and will enclose in " marks. This can happen in expressions.