Pass Input Box VBA Variable to SQL Query

Hello,

I need to prompt the user for a date and then send it to the SQL query I have running in my 2007 Excel VBA. I am using Windows Server 2000 if that makes a difference. I know I can use in input box to get the date from the user and assign it to a variable in VB. My issue is how do I then get it into my SQL script? The script works great now with a date hard coded in. It looks in the tables with 4 joins and gets all the data as I need it.

I would also like to total the figures after I am done. I am struggling with that as I can do it with a macro but I want the report to be done with one push of the button for the user. All I am returning is 2 fields from SQL. An invoice number and a Tax. It can be anywhere from 25 to 125 entries each day. I am willing to total all 125 rows each day if I can't select what is there.

Thanks in Advance for any help.

Charlie

Might be useful to share the code you have ...

Thanks. Here is my code.

Private Sub CommandButton1_Click()
  
  Dim con As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim sSql As String
  Dim iRow As Integer
  Dim report_date As String
      
    report_date = InputBox("Enter Date in MM/DD/YYYY format...")
  
    Application.StatusBar = True
    Application.StatusBar = "Opening database connection..."
    DoEvents
    
    Set con = New ADODB.Connection
    con.ConnectionString = "DSN=EVEREST_USP"
    con.CommandTimeout = 360
    con.Open
    
    Application.StatusBar = "Starting Query..."
    DoEvents
  
    sSql = "DELETE FROM USP_CRM..TempOTPNumbers" & vbCrLf
    
    con.Execute sSql
    
    sSql = "INSERT INTO USP_CRM..TempOTPNumbers (INVOICE, OTP_TAX)" & vbCrLf
    sSql = "SET NOCOUNT ON" & vbCrLf
    sSql = sSql & "DECLARE @DATE VARCHAR(10)" & vbCrLf
    sSql = sSql & "SET @DATE = '2011-09-13'" & vbCrLf
    sSql = sSql & "SELECT I.DOC_NO AS INVOICE, SUM(OTP.TAX*X.QTY_SHIP) AS OTP_TAX" & vbCrLf
    sSql = sSql & "FROM EVEREST_USP..INVOICES I WITH (NOLOCK)" & vbCrLf
    sSql = sSql & "INNER JOIN EVEREST_USP..X_INVOIC X WITH (NOLOCK) ON I.DOC_NO = X.ORDER_NO AND I.STATUS = X.STATUS" & vbCrLf
    sSql = sSql & "INNER JOIN EVEREST_USP..ITEMS IT WITH (NOLOCK) ON X.ITEM_CODE = IT.ITEMNO" & vbCrLf
    sSql = sSql & "INNER JOIN EVEREST_USP..CUST C WITH (NOLOCK)ON I.CUST_CODE = C.CUST_CODE" & vbCrLf
    sSql = sSql & "INNER JOIN EVEREST_USP..ADDRESS A WITH (NOLOCK)ON C.SHIPCODE = A.ADDR_CODE" & vbCrLf
    sSql = sSql & "INNER JOIN USP_CRM..PAPIO_ADDR_EXT AX WITH (NOLOCK)ON C.SHIPCODE = AX.ADDR_CODE" & vbCrLf
    sSql = sSql & "INNER JOIN USP_CRM..PAPIO_CATEGORIES CAT WITH (NOLOCK)ON IT.CATEGORY = CAT.CATEGORY" & vbCrLf
    sSql = sSql & "LEFT OUTER JOIN USP_CRM..PAPIO_OTP_TAXATION OTP WITH (NOLOCK)ON X.ITEM_CODE = OTP.ITEMNO AND A.STATE = OTP.STATE_CODE AND AX.STAMPGROUP = OTP.STAMPGROUP" & vbCrLf
    sSql = sSql & "INNER JOIN USP_CRM..PAPIO_RULES R WITH (NOLOCK)ON AX.STAMPGROUP = R.PRULEID" & vbCrLf
    sSql = sSql & "WHERE" & vbCrLf
    sSql = sSql & "I.STATUS IN (9,12)" & vbCrLf
    sSql = sSql & "AND I.ORDER_DATE >= @DATE + ' 00:00:00.000'" & vbCrLf
    sSql = sSql & "AND I.ORDER_DATE <= @DATE + ' 23:59:59.999'" & vbCrLf
    sSql = sSql & "AND I.CUST_CODE <> '10679'" & vbCrLf
    sSql = sSql & "AND (AX.STAMPGROUP <> 94 OR R.NAME NOT LIKE '%TAX-EXEMPT%')" & vbCrLf
    sSql = sSql & "GROUP BY I.DOC_NO" & vbCrLf
    sSql = sSql & "ORDER BY I.DOC_NO" & vbCrLf

    con.Execute sSql
    
    Application.StatusBar = "Opening query..."
    DoEvents
    
    Set rs = con.Execute(sSql)
    
    If rs.EOF Then
      MsgBox "Nothing to do!"
      Application.StatusBar = False
      Exit Sub
    End If
    
    Sheets("Main").Activate
    
    With ActiveSheet
    
      .Range("A1").Select
      .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
      Selection.ClearContents
      
      .Range("A1").Value = "Invoice"
      .Range("B1").Value = "OTP_TAX"
      .Range("C1").Value = report_date

           iRow = 2
      
      Do While Not rs.EOF
        Application.StatusBar = "Loading values..." & iRow
        .Range("A" & iRow).Value = rs.Fields("INVOICE")
        .Range("B" & iRow).Value = rs.Fields("OTP_TAX")
 
        If IsNull(rs.Fields("OTP_TAX")) Then
          .Range("B" & iRow).Value = 0
        Else
          .Range("B" & iRow).Value = rs.Fields("OTP_TAX")
        End If
        
        rs.MoveNext
        iRow = iRow + 1
      Loop
  
   MsgBox ("Running..5.. at TOTALS")
      'Figure Totals here and put in cell C2
      .Range("C2").Select
      .Range("C2").Value = SUM(B2:B125)
      .Range("C3").Select
    
      .Range("A1").Select
    
    End With
        
    MsgBox "Finished!"
    Application.StatusBar = False
    
  End If

End Sub

You need to add code tags as per the forum rules before anyone can provide an answer.

Regards

I can't test this but I'm guessing it would look something like:

sSql = sSql & "SET @DATE = '" & report_date & "'" & vbCrLf

Regards

You'll need to format it first, I think:

sSql = sSql & "SET @DATE = '" & Format(report_date, "yyyy-mm-dd") & "'" & vbCrLf

@RS: thought report_date was coming in as a string.

Regards

You are correct - overlooked that. In that case, I suspect:

sSql = sSql & "SET @DATE = '" & Format(CDate(report_date), "yyyy-mm-dd") & "'" & vbCrLf