Parameterized Query in Access SQL

I am a beginner in Access SQL. I am in need of writing a parameter query for Access SQL and found a few tutorials and links for that but that wouldn't satisfy my requirement.

DB: Access DB

This updates a 'PD' column based on the values of 'OG', 'CPTY-BA' in an INPUT table. I need to calculate PD values from other reference tables 'PD', 'PD_MASTER_CPTY' and 'PD_MASTER_BG'.

Below is the VBA code with Access SQL with provided parameters. Is there any other way to write it in a single SELECT statement?

```
ElseIf (outputColumn = "PD") Then

strSQL = "SELECT [OG], CIF,[CPTY-BA], CIF_2 FROM INPUT WHERE RUN_NUMBER='" + runNumber + "' ORDER BY CIF"
Set rs = CurrentDb.OpenRecordset(strSQL)

If Not (rs.EOF And rs.BOF) Then

Do Until rs.EOF

strOrigGroup = rs![Original Group]
strCIF = rs![CIF]
strCIF2 = rs![CIF_2]
strBaselAsset = rs![CPTY-BA]

If (strOrigGroup = "CB" Or strOrigGroup = "FID" Or strOrigGroup = "PB") Then

strSQL1 = " SELECT DISTINCT [INPUT].RUN_NUMBER, [INPUT].CIF, INPUT.CIF_2,[PD].[MID_PD] FROM [PD] INNER JOIN [INPUT] " + _
"ON [PD].CIF=[INPUT].CIF_2 WHERE [PD].[CIF]='" + strCIF2 + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL1
Set rs1 = CurrentDb.OpenRecordset(strSQL1)

If Not (rs1.EOF And rs1.BOF) Then
strPD = CDbl(rs1![MID_PD])
Else
strPD = "0.0229"
End If

strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "' WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL
CurrentDb.Execute (strSQL)

ElseIf (strBaselAsset = "BK" Or strBaselAsset = "BO" Or strBaselAsset = "SK") Then

strSQL1 = "SELECT DISTINCT [INPUT].RUN_NUMBER, [INPUT].CIF, [PD_MASTER_CPTY].[PD] F

Before we can do anything here really, we need to clean this up so we can understand exactly what's going on.

To start with, let's remove some duplication. This code is copy/pasted all over the place, but no matter what happens, it executes.

If Not (rs1.EOF And rs1.BOF) Then
    strPD = CDbl(rs1![PD])
Else
    strPD = "0.0229"
End If

strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "'  WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
Debug.Print strSQL
CurrentDb.Execute (strSQL)

If (strOrigGroup = "CB" Or strOrigGroup = "FID" Or strOrigGroup = "PB") Then

            strSQL1 = " SELECT DISTINCT  [INPUT].RUN_NUMBER, [INPUT].CIF, INPUT.CIF_2,[PD].[MID_PD] FROM [PD] INNER JOIN [INPUT] " + _
                      "ON [PD].CIF=[INPUT].CIF_2  WHERE [PD].[CIF]='" + strCIF2 + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
            Debug.Print strSQL1
            Set rs1 = CurrentDb.OpenRecordset(strSQL1)

    ElseIf (strBaselAsset = "BK" Or strBaselAsset = "BO" Or strBaselAsset = "SK") Then

         strSQL1 = "SELECT DISTINCT [INPUT].RUN_NUMBER,  [INPUT].CIF, [PD_MASTER_CPTY].[PD] FROM PD_MASTER_CPTY INNER JOIN INPUT ON [PD_MASTER_CPTY].COUNTERPARTY=INPUT.[CPTY-BA] WHERE [PD_MASTER_CPTY].[COUNTERPARTY]='" + strBaselAsset + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
         Debug.Print strSQL1
         Set rs1 = CurrentDb.OpenRecordset(strSQL1)
    Else
        strSQL1 = "SELECT [INPUT].RUN_NUMBER, [INPUT].CIF,[PD_MASTER_BG].[PD] FROM [PD_MASTER_BG] INNER JOIN INPUT ON [PD_MASTER_BG].[BUSINESS_GROUP]=INPUT.[ORIGINAL GROUP] WHERE [PD_MASTER_BG].[BUSINESS_GROUP]='" + strOrigGroup + "'AND [INPUT].CIF_2='" + CStr(strCIF2) + "' and [INPUT].RUN_NUMBER='" + runNumber + "'"
        Debug.Print strSQL1
        Set rs1 = CurrentDb.OpenRecordset(strSQL1)
    End If

    If Not (rs1.EOF And rs1.BOF) Then
        strPD = CDbl(rs1![PD])
    Else
        strPD = "0.0229"
    End If

    strSQL = "update output set [PD] = '" + Format(strPD, "#.0000000000000") + "'  WHERE [OUTPUT].[CIF]='" + CStr(strCIF) + "' AND [OUTPUT].RUN_NUMBER='" + runNumber + "'"
    Debug.Print strSQL
    CurrentDb.Execute (strSQL)

    rs.MoveNext
Loop

Next, give your SQL statement variables some meaningful names. I shouldn't have to keep reminding myself that strSQL1 is the select statement and strSQL is the update. Try selectSQL and updateSQL respectively.

Next, I would separate the messy business of creating the string to be executed from the business of actually executing.

Private Function BuildSelectStatement(byval cif as string, byval runNumber as integer, Optional byval baselAsset as String) As String
    ...
End Function

....

selectSQL = BuildSelectStatement(strCIF2, runNumber)

Which brings me to recommending that you use ADODB for this. The only time it's really important to stick with DAO is when you intend on binding a form to the resulting Recordset. Instead of concatenating all of these parameters into strings, you could instead be passing parameter into parameterized queries. It looks and reads much better than this.

StackExchange Code Review Q#92906, answer score: 3