Building SQL string to create query parameters

[GPSPOW]

The following is a VBA code I am trying to create to set the SQL statement for a query based on on a parameter set from a listbox.

If lstSelect.Value = 4 Then
    SQL = "SELECT qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name AS PrimCare_Name"
    SQL = SQL & "FROM qryPhysician_Registration_Data INNER JOIN dbo_DMisProvider ON qryPhysician_Registration_Data.Prim_Care_ID = dbo_DMisProvider.ProviderID"
    SQL = SQL & "GROUP BY qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name;"
Else
    For i = 0 To lstRight.ListCount - 1
        StrIn = StrIn & "'" & lstRight.Column(0, i) & "',"
    Next i
    If Len(StrIn) = 0 Then
        MsgBox "No Physicians Selected!" _
            , vbExclamation, "Returning to Selection Screen!"
        
        Exit Sub
    End If
            
 strWhere = " WHERE ((" & qryPhysician_Registration_Data.Prim_Care_ID = _
            " " & Left(StrIn, Len(StrIn) - 1) & "));"
    SQL = "SELECT qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name AS PrimCare_Name"
    SQL = SQL & "FROM qryPhysician_Registration_Data INNER JOIN dbo_DMisProvider ON qryPhysician_Registration_Data.Prim_Care_ID = dbo_DMisProvider.ProviderID"
    SQL = SQL & "GROUP BY qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name"
    SQL = SQL & strWhere
End If

When I run the module, I get an error, "Variable Not Defined". It has the qryPhysician_Registration_Data.Prime_Care_ID highlighted in the strWhere clause.

Any help will be aprreciated.

Thanks

 

[MarkK]

Probably you mean this...

  strWhere = " WHERE qryPhysician_Registration_Data.Prim_Care_ID = " & _
    "Left(StrIn, Len(StrIn) - 1)"

Certainly how you had it, VBA doesn't know what to do with "qryPhysician_Registration_Data.Prim_Care_ID." That needs to be evaluated in SQL.

 

[MarkK]

And you'll need to add spaces in your SQL at the ends of lines, because this...

sql = "SELECT Field1"
sql = sql & "FROM Table"

...evaluates to...

SELECT Field1FROM Table

See the missing space?

 

[Beetle]

In addition to what Mark said, a few more observations.

Here you appear to be building a comma separated list of values;

For i = 0 To lstRight.ListCount - 1
        StrIn = StrIn & "'" & lstRight.Column(0, i) & "',"
    Next i

Then here you appear to be attempting to compare a single PrimeCare_ID value to that comma separated string, which will fail;

" WHERE ((" & qryPhysician_Registration_Data.Prim_Care_ID = _
            " " & Left(StrIn, Len(StrIn) - 1) & "));"

If you want to see if the single value is in the string you can use the IN clause but you'll need to wrap the string in parenheses.

Also, you can use the continuation character in your SQL to make it a little easier to write. So instead of writing SQL = SQL & "blah blah" over and over;

SQL = "SELECT qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name AS PrimCare_Name"
    SQL = SQL & "FROM qryPhysician_Registration_Data INNER JOIN dbo_DMisProvider ON qryPhysician_Registration_Data.Prim_Care_ID = dbo_DMisProvider.ProviderID"
    SQL = SQL & "GROUP BY qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name;"

just use the continuation character and it becomes;

SQL = "SELECT qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name AS PrimCare_Name " _
    & "FROM qryPhysician_Registration_Data INNER JOIN dbo_DMisProvider ON " _
    & "qryPhysician_Registration_Data.Prim_Care_ID = dbo_DMisProvider.ProviderID" _
    & "GROUP BY qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name;"

 

[GPSPOW]

I am still getting a pucnuation error on the following:

strWhere = " WHERE qryPhysician_Registration_Data.Prim_Care_ID = " & _
"Left(StrIn, Len(StrIn) - 1) ;"
SQL = "SELECT qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name AS PrimCare_Name" _
& "FROM qryPhysician_Registration_Data INNER JOIN dbo_DMisProvider ON " _
& "qryPhysician_Registration_Data.Prim_Care_ID = dbo_DMisProvider.ProviderID" _
& "GROUP BY qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name" _
& strWhere

Thanks

Glen

 

[jdraw]

Always use a leading and/or trailing space in your lines when building SQL in code.

 

[GPSPOW]

I am getting a Run_Time error 3075:

Syntax error (missing operator) in query expression 'dbo_DMisProvider.Name WHERE qryPhysician_Registration_Data.Prim_Care_ID = Left(StrIn, Len(StrIn)-1)'

Here is the new code:

strWhere = " WHERE qryPhysician_Registration_Data.Prim_Care_ID = " & _
"Left(StrIn, Len(StrIn) - 1) ;"
SQL = "SELECT qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name AS PrimCare_Name" _
& " FROM qryPhysician_Registration_Data INNER JOIN dbo_DMisProvider ON " _
& " qryPhysician_Registration_Data.Prim_Care_ID = dbo_DMisProvider.ProviderID" _
& " GROUP BY qryPhysician_Registration_Data.Prim_Care_ID, dbo_DMisProvider.Name " _
& strWhere

Thanks

GPSPOW

 

[MarkK]

Some things are evaluated in VBA, some in SQL. In this case, everything is evaluated in SQL...

"SELECT Field1, 2*3 As Six FROM tblYourTable"

...constrasted with this, where some math is done in VBA...

"SELECT Field1, " & 2 * 3 & " As Six FROM tblYourTable"

...but if you declare a variable in VBA, then that can only be evaluated in VBA, so

Dim YourNum as Long
YourNum = 2 * 3
[COLOR="Green"]'this is fine, since YourNum is evaluated in VBA[/COLOR]
Debug.Print "SELECT Field1, " & YourNum & " As Six FROM tblYourTable"
[COLOR="Green"]'this will fail, because SQL doesn't know about VBA's variable, YourNum[/COLOR]
Debug.Print "SELECT Field1, YourNum As Six FROM tblYourTable"

This is the issue you are having, about where variables get evaluated.