Help with passing 2 variables to Stored Procedure

My application is a Access 2000 front end and SQL server backend.  I have a stored procedure on the server that will accept 2 variables.  From my Front End I have a form with 2 comboboxes that pass that data along.  I have been successful at passing one variable, but 2 is giving me grief.  Here is my module...

    'Set up the DAO for the pass-through SQL query
    Dim db As Database
    Dim MyQueryDef As QueryDef
    Dim SQLString As String
    Dim SPTQueryName As String
    Dim msg As String
   
    Set db = CurrentDb()
    db.QueryTimeout = 240
    SPTQueryName = "qrySponsorReportByDivision_SQL"
    If ObjectExists("Queries", SPTQueryName) = True Then
        db.QueryDefs.Delete SPTQueryName
        db.QueryDefs.Refresh
    End If
    Set MyQueryDef = db.CreateQueryDef(SPTQueryName)
   
    MyQueryDef.Connect = IMO_ODBCConnectionString
   
    'Now is a good time to display the Project Manager Choice Selection Screen.
    If intDivision = 0 Then
        DoCmd.OpenForm "frmSponsorReportByDivision", acNormal, , , acFormEdit, acDialog
    End If
       
    'Set the SQL property and concatenate the variables
    If intDivision <> 0 Then
 I NEED HELP HERE ----->      SQLString = "upSponsorReportByDivision" & "'" & intDivision & "' '" & intSponsor & "'"
        MyQueryDef.SQL = SQLString
        MyQueryDef.ReturnsRecords = True
        MyQueryDef.Close
        db.Close
        DoCmd.OpenQuery SPTQueryName, acViewNormal
    Else
        MyQueryDef.ReturnsRecords = False
        MyQueryDef.Close
        db.Close
    End If

What am I doing wrong?  I have tried to seperate those 2 variables with comma's and that didn't work either.  

Thanks!
Dark
darkmagnetoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
pique_techConnect With a Mentor Commented:
And, if you're using "hungarian" notation where intDivision and intSponsor are both numeric, you won't need the single quote delimiters.  So you would have:

          SQLString = "upSponsorReportByDivision " & intDivision & ", " & intSponsor
0
 
pique_techCommented:
SQLString = "upSponsorReportByDivision " & "'" & intDivision & "', '" & intSponsor & "'"
                                                           ^^                              ^
                                                    You need a space here        and a comma here

I'm assuming the sp your calling is on the SQL server.
0
 
fccoCommented:
 SQLString = "EXEC upSponsorReportByDivision " & intDivision & ", " & intSponsor
0
 
pique_techCommented:
Doh!  Of course.  : )
0
All Courses

From novice to tech pro — start learning today.