Query from a Form

[esi]

Having trouble with the coding in this form. I keep getting errors with the
sql for "expected end of statment"

Private Sub Command8_Click()

Dim dbs As Database
Dim qdf As QueryDef
Dim strsql As String
Dim mytable As String

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("WeekBadClaimqry")

mytable = [List4]

strsql = "SELECT [Plan], [Patient_Name], [Rx#], [Fill_Date], [NDC],
[Drug_Name], [B_G], " _
& "EXTRACT_FILE.Basis, [AWP], [Cost], [Price], [Margin],
1-([Price]/[AWP]) AS Expr1 " _
& "FROM [" & mytable & "] LEFT JOIN EXTRACT_FILE ON [NDC] =
EXTRACT_FILE.NDC " _
& "WHERE [Plan]="EXR" " _
& "ORDER BY [B_G], [Margin], 1-([Price]/[AWP])"

qdf.SQL = strsql

Set qdf = Nothing
DoCmd.OpenQuery "WeekBadClaimqry"
End Sub

 

[Dale Fye]

Try modifying your sql string as follows. I made a couple
of changes:
1. I aliased your MyTable table as T, so I could use that
in the join, since both tables contain the NDC field, you
need to be explicit about which table/fields you are
joining.
2. I wrapped your "EXR" value in quotes (chr$(34)). The
way you had it, the sql string was terminating at the
quote in front of the EXR.

One way you can test this in the future is to set a
breakpoint in your code right after you build the sql
string and print that value in the immediate window. This
will allow you to see what the SQL string looks like, and
compare it to what you think it should look like.

HTH
Dale


strsql = "SELECT T.[Plan], T.[Patient_Name], T.[Rx#], " _
& "T.[Fill_Date], T.[NDC], T.[Drug_Name], " _
& "T.[B_G], EF.Basis, T.[AWP], " _
& "T.[Cost], T.[Price], T.[Margin], " _
& "1-(T.[Price]/T.[AWP]) AS Expr1 " _
& "FROM [" & mytable & "] as T " _
& "LEFT JOIN EXTRACT_FILE EF" _
& "ON T.[NDC] = EF.NDC " _
& "WHERE T.[Plan]=" & chr$(34) & "EXR" & chr$(34) _
& " ORDER BY T.[B_G], T.[Margin], " _
& "1-(T.[Price]/T.[AWP])"