Order By Clause of Query

[TBOB]

I'm using a function to create an SQL string to select records and order them correspondingly. I'm using the following code:

strSELECT = "RCLSITES.*"
strFROM = "RCLSITES"

If LINKNUM = 5 Then
strSQL = "SELECT " & strSELECT & ", RCLSITES.rcllink, RCLSITES.rclstation "
strSQL = strSQL & "FROM " & strFROM
strSQL = strSQL & " ORDER BY " & "RCLSITES.rcllink, RCLSITES.rclstation;"
GoTo SQLState_Bld
End If

The string gets to the query fine, because I can go to the query and see the SQL string I've just placed there. When I run that string from the query it does exactly what I want it to. When I try to run the query from the form that I'm selecting my parameter from I get a message that says "qryRCLLINK.rclstation could refer to more than one table listed in the From clause of your SQL statements". I don't have duplicate Tables or fields. Any help would be appreciated.

Thanks,

Tony

 

[LNBruno]

if you're selecting from only one table, you don't need to qualify the order by fields:

    strSQL = "SELECT " & strSELECT & ", rcllink, rclstation "
    strSQL = strSQL & "FROM " & strFROM
    strSQL = strSQL & " ORDER BY " & "rcllink, rclstation;"

maybe that will help?

< M!ke >

 

[TBOB]

Mike,

Tried that and got the same result.

Thanks,

Tony

 

[dhookom]

What do you see if you Debug.Print strSQL?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JerryKlmns]

strFROM = "RCLSITES"

If LINKNUM = 5 Then
strSQL = "SELECT * FROM " & strFROM & " ORDER BY rcllink, rclstation;"
GoTo SQLState_Bld 'This is spaghetti programming
End If

Since you select all fields, selecting again those two for sorting reasons, isnt necessary.