Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3

    Question Unanswered: SQL statement problems

    Here is my code. I am trying to populate a combo box with this code. It works, except for the fact that it is not concatonating the last name and first names. All I get ar Last Names. Anybody see what's wrong?

    Private Sub Form_Load()
    Dim strActiveSQL As String

    If IsNull(Me!AssignedTech.Value) Then
    strActiveSQL = "SELECT EmployeeID, [EmployeeLastName]" & ", " & _
    "[EmployeeFirstName] AS [EmployeeFullName] FROM tblUsers " & _
    "WHERE Active = True ORDER BY [EmployeeLastName]" & ", " & _
    "[EmployeeFirstName];"
    Else
    strActiveSQL = "SELECT EmployeeID, [EmployeeLastName]" & ", " & _
    "[EmployeeFirstName] AS [EmployeeFullName] FROM tblUsers " & _
    "ORDER BY [EmployeeLastName]" & ", " & _
    "[EmployeeFirstName];"
    End If

    Me!EmployeeName.RowSource = strActiveSQL

    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    [EmployeeLastName]" & ", " & _
    "[EmployeeFirstName] AS [EmployeeFullName]

    put it on one line:
    Code:
    [EmployeeLastName]" & ", " & "[EmployeeFirstName] AS [EmployeeFullName]
    and do step1 of the concatenation: first " & " disappears
    Code:
    [EmployeeLastName], " & "[EmployeeFirstName] AS [EmployeeFullName]
    and do step2 of the concatenation: second " & " disappears
    Code:
    [EmployeeLastName], [EmployeeFirstName] AS [EmployeeFullName]
    so now i'm confused: looks like you should be getting firstname AS fullname, not lastname as you suggest.

    anyway, i hate the _continuation, so maybe i got this wrong.
    strSQl = strSQL & "whatever seems so much simpler to decypher

    put a
    msgbox "My SQL is ¦" & strActiveSQL & "¦" 'just before
    Me!EmployeeName.RowSource = strActiveSQL
    and see if you get what you think you should get.

    izy

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    aha! now i see this is a rowsource

    you only expect two columns [ID], [full], but you are pulling three columns [ID], [last], and [first] AS [full]

    displayed is the second [last]
    ...which you expected to be [full]
    ......(which is in fact [first] not [last]&[first] which you hoped for)

    izy

  4. #4
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Yes, I am pulling [ID], [FirstName], and [LastName]. I am not showing the ID's in the combo box. I only want to see "Doe, John".
    I'm getting "Doe".
    I've read it over, and over. It looks right to me. I have pasted it into the SQL view of a query, and it seems to work there. Just not in code.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yup, but you seem to be trying to concatenate [last] & [first] AS [full] and your SQL does not say that! it pulls [first] AS [full]

    instead of
    [EmployeeLastName]" & ", " & _
    "[EmployeeFirstName] AS [EmployeeFullName]


    try

    "[EmployeeLastName] & [EmployeeFirstName] AS [EmployeeFullName] "

    (sorry, i just can't handle the _continuation)

    izy

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    or rather:

    "[EmployeeLastName] & " & ", " & [EmployeeFirstName] AS [EmployeeFullName] "

    ...to get the comma-space in there.

    promise it is not the same as your original!


    izy

  7. #7
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    No deal. Thanks anyway.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are right!

    last try:

    "[EmployeeLastName] & '" & ", " & "' & [EmployeeFirstName] AS [EmployeeFullName] "



    izy
    Last edited by izyrider; 09-25-03 at 15:16.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    TRY:

    strActiveSQL = "SELECT EmployeeID, [EmployeeLastName] & ', ' & [EmployeeFirstName] AS [EmployeeFullName] FROM tblUsers " & _
    "WHERE Active = True ORDER BY [EmployeeLastName]" & ", " & _
    "[EmployeeFirstName];"


    What you did with your query construction was concatenate the strings together but you did not build the actual employee full name expression ...

  10. #10
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3

    Thumbs up

    Thanks! That's been driving me crazy all day. I get rusty with this stuff, as I don't get to use it very often.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •