Query statement conversion to string question

[Euler271]

I have the following SQL statement that works just fine:

INSERT INTO tblWIP ( PROC_CD, Units, Visits, [CMS Rate] )
SELECT PPR.HCPCS AS Expr1, nz(DLookUp("SumOfUNIT_CNT","U","PROC_CD='" & [HCPCS] & "'"),0) AS Expr2, 23456 AS Expr3, [Conv Factor]*((DLookUp("[GPCIw]","tblGPCI","State='MN'")*[Work RVU])+(DLookUp("[GPCIpe]","tblGPCI","State='MN'")*[Non-FAC PE RVU])+(DLookUp("[GPCImp]","tblGPCI","State='MN'")*[MP RVU])) AS Expr4


FROM PPR
ORDER BY PPR.HCPCS;

Now I need to convert that to a string and run it from VBA code. Here's what I have:

str = "INSERT INTO tblWIP ( PROC_CD, Units, Visits, [CMS Rate] ) "
str = str & "SELECT PPR.HCPCS AS Expr1, "
str = str & Nz(DLookup("SumOfUNIT_CNT", "U", "PROC_CD='" & [HCPCS] & "'"), 0) & " AS Expr2, "
str = str & V & " AS Expr3, "
str = str & "[Conv Factor] * ((" & DLookup("[GPCIw]", "tblGPCI", "State='" & StateName & "'") & "* [Work RVU]) + (" & DLookup("[GPCIpe]", "tblGPCI", "State='" & StateName & "'") & " * [Non-FAC PE RVU]) + (" & DLookup("[GPCImp]", "tblGPCI", "State='" & StateName & "'") & "* [MP RVU])) AS Expr4 "
str = str & "FROM PPR "
str = str & "ORDER BY PPR.HCPCS;"




I get an Type Mismatch error on line 3 with the DLookup.

Can anyone tell me how to correctly write the string? Thanks.

[June7]

Should not be necessary but try a number conversion function such as CDbl or CInt.

HCPCS and StateName are both fields/textboxes on this form?

[davegri]

Add a
debug.print str
after the last line to see what it looks like in the immediate window
Also you could try doing the dlookups outside the query.
Do the dlookups into tempvars, then use the tempvars in the SQL

[ssanfu]

Since you are moving the query to VBA, could you calculate the DLookups and append the values ... something like this:

(this is untested!!)

    Dim iUnitCount As Integer
    Dim iRate As Integer

    'calculate values for Expr2 and Expr4
    iUnitCount = Nz(DLookup("SumOfUNIT_CNT", "U", "PROC_CD='" & [HCPCS] & "'"), 0)
    iRate = "[Conv Factor] * ((" & DLookup("[GPCIw]", "tblGPCI", "State='" & StateName & "'") & "* [Work RVU]) + (" & DLookup("[GPCIpe]", "tblGPCI", "State='" & StateName & "'") & " * [Non-FAC PE RVU]) + (" & DLookup("[GPCImp]", "tblGPCI", "State='" & StateName & "'") & "* [MP RVU]))"

    Str = "INSERT INTO tblWIP ( PROC_CD, Units, Visits, [CMS Rate] ) "
    Str = Str & "SELECT PPR.HCPCS AS Expr1, "
    Str = Str & iUnitCount & " AS Expr2, "
    Str = Str & V & " AS Expr3, "
    Str = Str & iRate * [MP RVU] & " AS Expr4 "
    Str = Str & "FROM PPR "
    Str = Str & "ORDER BY PPR.HCPCS;"
    ' Debug.Print Str
    CurrentDb.Execute Str, dbFailOnError

[Euler271]

I could do that for the StateName variable but the main problem is with [HCPCS] which is not a variable but a field in the table PPR. As a result, iUnitCount will be different for each record inserted into tblWIP. I can do this using a VBA loop but that's much slower than doing a bulk insert. So it's important for me to find a way to include the DLookup within the str.

Thanks for responding

[davegri]

Try this as line 3: Use a UDF for the DLookup. In addition, as a debugging aid, you can put a debug print in the function to see what it is returning.
Also, if the function is in a code module, you can see what it returns by plugging in your own value in the immediate window like this: (the question mark means PRINT)
?fcnHCPCS("any valid value")

str = str & fcnHCPCS([PPR].[HCPCS]) & " AS Expr2, "

public function fcnHCPCS (arg as String) as variant ' or Currency or ? what is the type of SumOfUNIT_CNT?
    fcnHCPCS = NZ(Dlookup("SumOfUNIT_CNT", "U", "PROC_CD='" & arg & "'"),0)
end function

[June7]

For your original structure, have to put that DLookup within quotes, using apostrophe delimiters for the DLookup arguments and emdedded quote marks for HCPCS text delimiter.

str = str & "Nz(DLookup('SumOfUNIT_CNT', 'U', 'PROC_CD="' & [HCPCS] & '"' ), 0) & AS Expr2, "

The embedded quote might have to be doubled.

But why can't you just join tables/queries PPR and U on PROC_CD and HCPCS fields instead of DLookup?

[Euler271]

Regarding using the UDF:

V = Nz(DSum("VST_CNT", "Report01_201701_201712", "PRV_STS_RLP='P' AND PROV_ST_ABBR_CD='" & StateName & "' AND PROV_TYP_NM_2 IN (SELECT tblLookupProviderTypesTable2.PROV_TYP_NM_2 FROM tblLookupProviderTypesTable2 WHERE (((tblLookupProviderTypesTable2.ProviderType)='T') )"), 0)
str = "INSERT INTO tblWIP ( PROC_CD, Units, Visits, [CMS Rate] ) "
str = str & "SELECT PPR.HCPCS AS Expr1, "
'str = str & "Nz(DLookUp('SumOfUNIT_CNT','U','PROC_CD="""" & [HCPCS] & """"),0) AS Expr2, "
str = str & Formula1([HCPCS]) & " AS Expr2, "
str = str & V & " AS Expr3, "
str = str & [Conv Factor] & " * ((" & Formula2([GPCIw], StateName) & " * " & [Work RVU] & ") + (" & Formula2([GPCIpe], StateName) & " * " & [Non-FAC PE RVU] & ") + (" & Formula2([GPCImp], StateName) & " * " & [MP RVU] & ")) AS Expr4 "
'str = str & [Conv Factor] & "*((DLookUp('[GPCIw]','tblGPCI','State='" & StateName & ")* " & [Work RVU] & ")+(DLookUp('[GPCIpe]','tblGPCI','State='" & StateName & ")* " & [Non-FAC PE RVU] & ")+(DLookUp('[GPCImp]','tblGPCI','State='" & StateName & ")*" & [MP RVU] & ")) AS Expr4"
'str = str & [Conv Factor] & "*((DLookUp('[GPCIw]','tblGPCI','State="""" & StateName & """")* " & [Work RVU] & ")+(DLookUp('[GPCIpe]','tblGPCI','State="""" & StateName & """")* " & [Non-FAC PE RVU] & ")+(DLookUp('[GPCImp]','tblGPCI','State="""" & StateName & """")*" & [MP RVU] & ")) AS Expr4"
str = str & "FROM HCPCS LEFT JOIN PPR ON HCPCS.PROC_CD = PPR.HCPCS "
str = str & "GROUP BY PPR.HCPCS, "
'str = str & "Nz(DLookUp('SumOfUNIT_CNT','U','PROC_CD="""" & [HCPCS] & """"),0), "
str = str & Formula1([HCPCS]) & " "
str = str & V & ", "
'str = str & [Conv Factor] & "*((DLookUp('[GPCIw]','tblGPCI','State='" & StateName & ")* " & [Work RVU] & ")+(DLookUp('[GPCIpe]','tblGPCI','State='" & StateName & ")* " & [Non-FAC PE RVU] & ")+(DLookUp('[GPCImp]','tblGPCI','State='" & StateName & ")*" & [MP RVU] & ")) "
str = str & [Conv Factor] & " * ((" & Formula2([GPCIw], StateName) & " * " & [Work RVU] & ") + (" & Formula2([GPCIpe], StateName) & " * " & [Non-FAC PE RVU] & ") + (" & Formula2([GPCImp], StateName) & " * " & [MP RVU] & ")) "
str = str & "HAVING ((Not (PPR.HCPCS) Is Null)) ORDER BY PPR.HCPCS;"
Debug.Print str
DoCmd.RunSQL str
End Sub
Function Formula1(arg As String) As Double
Formula1 = Nz(DLookup("SumOfUNIT_CNT", "U", "PROC_CD='" & arg & "'"), 0)
End Function
Function Formula2(arg1 As Double, arg2 As String) As Long
Formula2 = [Conv Factor] * ((DLookup("[GPCIw]", "tblGPCI", "State='" & TempVars!State & "'") * [Work RVU]) + (DLookup("[GPCIpe]", "tblGPCI", "State='" & TempVars!State & "'") * [Non-FAC PE RVU]) + (DLookup("[GPCImp]", "tblGPCI", "State='" & TempVars!State & "'") * [MP RVU])) & " "
End Function

The code stops at the line:

str = str & Formula1([HCPCS]) & " AS Expr2, "

giving me a "Type Mismatch" error which doesn't make sense since both the field "SumOfUNIT_CNT" in the table "U" and the field "Units" in "tblWIP" are Double datatypes.

Now if I switch the single quotes:

str = "INSERT INTO tblWIP ( PROC_CD, Units, Visits, [CMS Rate] ) "
str = str & "SELECT PPR.HCPCS AS Expr1, "
str = str & "Nz(DLookUp('SumOfUNIT_CNT','U','PROC_CD="""" & [HCPCS] & """"),0) AS Expr2, "
'str = str & Formula1([HCPCS]) & " AS Expr2, "
str = str & V & " AS Expr3, "
'str = str & [Conv Factor] & " * ((" & Formula2([GPCIw], StateName) & " * " & [Work RVU] & ") + (" & Formula2([GPCIpe], StateName) & " * " & [Non-FAC PE RVU] & ") + (" & Formula2([GPCImp], StateName) & " * " & [MP RVU] & ")) AS Expr4 "
'str = str & [Conv Factor] & "*((DLookUp('[GPCIw]','tblGPCI','State='" & StateName & ")* " & [Work RVU] & ")+(DLookUp('[GPCIpe]','tblGPCI','State='" & StateName & ")* " & [Non-FAC PE RVU] & ")+(DLookUp('[GPCImp]','tblGPCI','State='" & StateName & ")*" & [MP RVU] & ")) AS Expr4"
str = str & [Conv Factor] & "*((DLookUp('[GPCIw]','tblGPCI','State="""" & StateName & """")* " & [Work RVU] & ")+(DLookUp('[GPCIpe]','tblGPCI','State="""" & StateName & """")* " & [Non-FAC PE RVU] & ")+(DLookUp('[GPCImp]','tblGPCI','State="""" & StateName & """")*" & [MP RVU] & ")) AS Expr4"
str = str & "FROM HCPCS LEFT JOIN PPR ON HCPCS.PROC_CD = PPR.HCPCS "
str = str & "GROUP BY PPR.HCPCS, "
str = str & "Nz(DLookUp('SumOfUNIT_CNT','U','PROC_CD="""" & [HCPCS] & """"),0), "
'str = str & Formula1([HCPCS]) & " "
str = str & V & ", "
'str = str & [Conv Factor] & "*((DLookUp('[GPCIw]','tblGPCI','State='" & StateName & ")* " & [Work RVU] & ")+(DLookUp('[GPCIpe]','tblGPCI','State='" & StateName & ")* " & [Non-FAC PE RVU] & ")+(DLookUp('[GPCImp]','tblGPCI','State='" & StateName & ")*" & [MP RVU] & ")) "
str = str & [Conv Factor] & "*((DLookUp('[GPCIw]','tblGPCI','State="""" & StateName & """")* " & [Work RVU] & ")+(DLookUp('[GPCIpe]','tblGPCI','State="""" & StateName & """")* " & [Non-FAC PE RVU] & ")+(DLookUp('[GPCImp]','tblGPCI','State="""" & StateName & """")*" & [MP RVU] & ")) "
'str = str & [Conv Factor] & " * ((" & Formula2([GPCIw], StateName) & " * " & [Work RVU] & ") + (" & Formula2([GPCIpe], StateName) & " * " & [Non-FAC PE RVU] & ") + (" & Formula2([GPCImp], StateName) & " * " & [MP RVU] & ")) "
str = str & "HAVING ((Not (PPR.HCPCS) Is Null)) ORDER BY PPR.HCPCS;"
Debug.Print str


and try it by manipulating the single and double quotes I get a Type Mismatch" error at this line:

str = str & [Conv Factor] & "*((DLookUp('[GPCIw]','tblGPCI','State="""" & StateName & """")* " & [Work RVU] & ")+(DLookUp('[GPCIpe]','tblGPCI','State="""" & StateName & """")* " & [Non-FAC PE RVU] & ")+(DLookUp('[GPCImp]','tblGPCI','State="""" & StateName & """")*" & [MP RVU] & ")) AS Expr4"




Interestingly, there's no error on this line:

str = str & "Nz(DLookUp('SumOfUNIT_CNT','U','PROC_CD="""" & [HCPCS] & """"),0) AS Expr2, "

So maybe I'm doing something wrong with the quotes in the line giving the error.

Thanks for your suggestions.

[davegri]

I thought this might be of interest. It appears that the variable name passed to the function must be the alias.
I made up the data in the tables.
Maybe if you broke down your SQL into short sections like this for debugging, you could get a portion working, then advance to the next, and so on.

Option Compare Database
Option Explicit


Public Function Formula1(arg As String) As Double
    Formula1 = Nz(DLookup("SumOfUNIT_CNT", "U", "PROC_CD='" & arg & "'"), 0)
End Function


Public Sub stuff()
    Dim str As String
    Dim rs As DAO.Recordset
    str = "SELECT PPR.HCPCS AS Expr1, Formula1(Expr1) AS Expr2 FROM PPR;"
    Set rs = CurrentDb.OpenRecordset(str)
    rs.MoveLast
    rs.MoveFirst
    Debug.Print rs!expr1
    Debug.Print rs!expr2
End Sub

And the immediate window to show the debug.prints

[Euler271]

Sorry, but I get a message asking for a parameter value for Expr1:

INSERT INTO tblWIP ( PROC_CD, Units, Visits ) SELECT PPR.HCPCS AS Expr1, Formula1(Expr1), 1920 AS Expr3, FROM HCPCS LEFT JOIN PPR ON HCPCS.PROC_CD = PPR.HCPCS GROUP BY PPR.HCPCS, Formula1(Expr1), 1920, HAVING ((Not (PPR.HCPCS) Is Null)) ORDER BY PPR.HCPCS;

Thanks for responding.

[davegri]

Sorry, but I get a message asking for a parameter value for Expr1:

INSERT INTO tblWIP ( PROC_CD, Units, Visits ) SELECT PPR.HCPCS AS Expr1, Formula1(Expr1), 1920 AS Expr3, FROM HCPCS LEFT JOIN PPR ON HCPCS.PROC_CD = PPR.HCPCS GROUP BY PPR.HCPCS, Formula1(Expr1), 1920, HAVING ((Not (PPR.HCPCS) Is Null)) ORDER BY PPR.HCPCS;

Thanks for responding.

Don't see how you got far enough to see that error. The SQL contains syntax errors. Those 2 red commas should not be there.

Here's a database that I ginned up that does the insert above correctly. I had to guess at some of the table structures and contents, but the example works.
Double click on basCode, then in the immediate window type
Call subInsertCode
and Enter.
Code will insert 2 records in tblWIP.

Euler271-davegri-v01.zip

[Euler271]

This code does work:

str = "INSERT INTO tblWIP ( PROC_CD, Units, Visits ) " _
& "SELECT PPR.HCPCS AS PROC_CD, Formula1(PROC_CD), " & V & " AS Visits FROM HCPCS " _
& "LEFT JOIN PPR ON HCPCS.PROC_CD = PPR.HCPCS " _
& "GROUP BY PPR.HCPCS, Formula1(PROC_CD), " & V & " " _
& "HAVING ((Not (PPR.HCPCS) Is Null)) ORDER BY PPR.HCPCS;"

but this code does not work and asks for a parameter for Expr1:

str = "INSERT INTO tblWIP ( PROC_CD, Units, Visits ) " _
& "SELECT PPR.HCPCS AS Expr1, Formula1(Expr1), " & V & " AS Visits FROM HCPCS " _
& "LEFT JOIN PPR ON HCPCS.PROC_CD = PPR.HCPCS " _
& "GROUP BY PPR.HCPCS, Formula1(Expr1), " & V & " " _
& "HAVING ((Not (PPR.HCPCS) Is Null)) ORDER BY PPR.HCPCS;"


Thank you very much for spending the time fixing my code. I appreciate it very much.

[davegri]

Happy to help. I hope the example will help you get the remaining query syntax working.