SQL string not being accepted??

[timnicholls]

Hi all,

I am trying to pass a different set of names (clients or staff) to a single combobox given a selection of an option group.

I can get them to the combox if the query is simple. That is if there is nothing like SELECT [Surname] & ", " & [First Name]. But SELECT [Surname] FROM ... I can get to work, however its not what I want.

In the code below the first hurdle is (& ", " &) just after [Surname] in the first line.

The SQL is just from a query which works fine.

Can anybody help to get this SQL to work?

Thanks

Private Sub Frame106_AfterUpdate()
Dim strSQL As String

If Me.Frame106 = 1 Then

      strSQL=  "SELECT [Surname] & ", " & [First Name] AS Client_Name, Clients.[First Name], Clients.Surname, Clients.Status FROM Clients WHERE (((Clients.Status) = "A")) ORDER BY [Surname];"

      Me.Combo24.RowSource = strSQL

End If
    
If Me.Frame106 = 2 Then
    
      strSQL = "SELECT Employees.[Surname],Employees.[First Name], Employees.[Status] FROM Employees WHERE (((Employees.Status) = "C" Or (Employees.Status) = "F" Or (Employees.Status) = "P")) ORDER BY [Surname] & ", " & [First Name];" 

       Me.Combo24.RowSource = strSQL
  
    End If

 

[scriverb]

Try this:

If Me.Frame106 = 1 Then

      strSQL=  "SELECT ([Surname] & ', ' & [First Name] AS Client_Name, Clients.[First Name], Clients.[Surname], Clients.Status FROM Clients WHERE (((Clients.Status) = 'A')) ORDER BY [Surname];"

      Me.Combo24.RowSource = strSQL

End If
    
If Me.Frame106 = 2 Then
    
      strSQL = "SELECT Employees.[Surname],Employees.[First Name], Employees.[Status] FROM Employees WHERE (((Employees.Status) = "C" Or (Employees.Status) = 'F' Or (Employees.Status) = 'P')) ORDER BY [Surname] & ', ' & [First Name];" 

       Me.Combo24.RowSource = strSQL

When building a string where you need quotes within the string use the single-quotes('). You can also use a double-double to designate a double quote but that is quite confusing to follow many times. So, just use the singles and ACCESS can then interpret the difference between the beginning and ending of the string from the need to identify a string within the string.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[timnicholls]

Bob,

Thank You. It works ;-)

Do you know how to break a long line of code up to go on several lines?

Putting a <space><underscore> at the end of a line doesn't work on the code above.

Thanks Again

 

[scriverb]

When building a long string in code you must close the string on one line with double quotes, space, ampersand(&), space and then underscore(_) and finally start the quote on the next line with a double quote again.

EXAMPLE:

strSQL=  "SELECT ([Surname] & ', ' & " & _
"[First Name] AS Client_Name, Clients.[First Name], " & _
"Clients.[Surname], Clients.Status FROM Clients WHERE " & _
"(((Clients.Status) = 'A')) ORDER BY [Surname];"

Post back if you need more assistance.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]