SQL comile error. PLEASE see example. Thanks.

[INeedAccessHelp]

I get a compile error: Expected: End of statement.

I think it may have something to do with the comma to separate the first name and last name in the Username, but I can't figure it out. Any ideas???

Me![DeveloperNameDisp].RowSource = "SELECT DISTINCT User.UserID, [User]![Last Name] & ", " & [User]![First Name] AS UserName, tblDeveloper.DeveloperName FROM User INNER JOIN tblDeveloper ON User.UserID = tblDeveloper.DeveloperName WHERE ProgReqID=" & [ProgReqID] & ";"

 

[Philly44]

SLECT DISTINCT User.UserID, User.Last Name, " & "," & "User.First Name As UserName, tblDeveloper.DeveloperName FROM User INNER JOIN tblDeveloper ON User.UserID = tblDeveloper.DeveloperName WHERE ProgReqID=" & [ProgReqID] & ";"

The problem I think was with the comma, try the SQL I have posted and let me know, it should work

 

[tempclerk]

Totally beside the point, but User.UserID = tblDeveloper.DeveloperName?

 

[INeedAccessHelp]

DeveloperName in tblDeveloper refers to the UserID in the User table.

 

[MichaelRed]

SLECT DISTINCT User.UserID, User.Last Name, " & "," & "User.First Name As UserName, tblDeveloper.DeveloperName FROM User INNER JOIN tblDeveloper ON User.UserID = tblDeveloper.DeveloperName WHERE ProgReqID=" & [ProgReqID] & ";"

but first, correct the spellling of "SELCT", Rrmove the Quotation mark Before "User.First Name as UserName" and place the "squarg brackests around "First Name" in the same string and in the initial assignment. After these, the 'problem' appears (to me) to be in the use of the BANG "!&quot operator instead of the DOT (".&quot operator.

Probably easier to use your initial post and replace:

[User]![Last Name]

with

[User].[Last Name]

... but then it is only a guess.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[INeedAccessHelp]

This is what I have now:

Me![DeveloperNameDisp].RowSource = "SELECT DISTINCT User.UserID, User.[Last Name] " & ", " & " User.[First Name] As UserName, tblDeveloper.DeveloperName FROM User INNER JOIN tblDeveloper ON User.UserID = tblDeveloper.DeveloperName WHERE ProgReqID=" & [ProgReqID] & ";"

It is giving me the correct UserID number, but I want the UserName (Last Name, First Name) associated with that UserID number. Any ideas?

 

[MichaelRed]

Not with out more info.

In the query Results:
[tab]What is in [UserName]
[tab]What is in [User.UserID]

Add the following to the query output (at least temporaily):
tblDeveloper.DeveloperName
ProgReqID

post these for a sample.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Philly44]

Sorry about the spelling mistakes, but heres another go at it that I think will work fine

SELECT DISTINCT User.UserID, Trim([Last Name] "," [First Name]) as UserName, tblDeveloper.DeveloperName
From User INNER JOIN tblDeveloper ON User.UserID = tblDeveloper.DeveloperName

Before anyone corrects me I know there is no WHERE clause because thats where the real problem is. Where are you getting that value from? If its on a form the syntax is
[Forms]![Form Name]![ProgReqID]

I hope this clears things up a little better, again sorry about the spelling mistakes