none
Query parameter in Excel VBA RRS feed

  • Question

  • Hi Expert,

      I use a VBA macro to run a query for excel sheet, but in this query, I need to use 2 parameter in where clause .but after run this macro, I got error for 2nd parameter "BEProdLine". does expert can help ?

    The VBA is this.

                                                                       

    Public Sub SQLQuery()

        Dim SQL As String
        Dim Connected As Boolean

        Dim MsgType As String
        Dim TitleMsg As String
        Dim PLCType As String

        Dim MsgLine As String
        Dim TitleMsgLine As String
        Dim BEProdLine As String

        MsgType = "Enter the Type 0~5, 0, Rav, 1, Tort, 2, Cuts, 3, Sauce"
        TitleMsg = "PLC Type Code"
        PLCType = Application.InputBox(MsgType, TitleMsg, Type:=2)

        MsgLine = "Enter Production Line Code Like BEL1, BES1, BEL2"
        TitleMsgLine = "Production Line"
        BEProdLine = Application.InputBox(MsgLine, TitleMsgLine, Type:=2)



        'Our query
        SQL = "Select   T0.[Production Line],Convert(Varchar(8), T0.[Due Date], 1) as [MM/dd/yyyy],T0.[No_],T0.[Prod Sequence],T0.[Source No_],T0.[Description],T0.[Quantity], " & _
            "T1.[Die Shape],(T2.[FormTargetRate]/100) as FTRate,(T2.[FormTargetOEE]/100) as FTOEE,(T2.[FormCSPERSig]/100) as FCSIG,  (T2.[PAKTargetRate]/100) as PTRate, " & _
            "(T2.[PAKTargetOEE]/100) as PTOEE,(T2.[PAKCsPerSig]/100) as PCSIG, T1.[Net Weight] " & _
            "from   dbo.[Valley Fine Foods$Production Order] T0 INNER JOIN " & _
            "dbo.[Valley Fine Foods$Item] T1 ON T1.[No_] = T0.[Source No_] INNER JOIN " & _
            "dbo.[Valley Fine Foods$PLC Basic] T2 ON T2.[Item No_] = T0.[Source No_] and T2.[ProdLineCode] = T0.[Production Line] " & _
            "where  [Due Date] >= (Getdate() - 1) and [Due Date] <= Getdate() and T0.[Production Line] = BEProdLine and T2.[Type] = PLCType "
        'Connect to the database
        Connected = Connect("vffserver9m", "vffnav2013")

        If Connected Then
        'If connected run query and disconnect
            Call Query(SQL)
            Call Disconnect
            Else
        'Couldn't connect
            MsgBox "Could Not Connect!"
            End If
        Columns("A:I").Select
        Columns("A:I").EntireColumn.AutoFit
        Worksheets("Sheet1").Range("I2").Select


    End Sub


    James Liang

    Wednesday, May 27, 2015 12:02 AM

Answers

  • Jan,

      I fixed, in query, I used Format(PLCType, "#") to get data.

    Thanks


    James Liang

    • Marked as answer by L.HlModerator Wednesday, June 3, 2015 11:04 AM
    Wednesday, May 27, 2015 3:28 PM
  • and where command will be like this.

     "where  [Due Date] >= (Getdate() - 1) and      [Due Date] <= Getdate() and " & _

     "T0.[Production Line] = '" & BEProdLine & "' and " & _

     "T2.[Type] = '" & Format(PLCType, "#") & "';"


    James Liang

    • Marked as answer by JamesLiang Friday, June 5, 2015 5:59 PM
    Wednesday, May 27, 2015 4:18 PM

All replies

  • Expert,

      I add comma in parameter, it's working now. but another issue happen, The PLCType in SQL is Int, so I modify the "Dim PLCType as String to "Integer" and Inputbox's Type := 1. but after run, program show "conversion failed when converting the varchar 'PLCType' to data type int".

    how to convert to int in where clause ?

    Thanks


    James Liang

    Wednesday, May 27, 2015 12:20 AM
  • James,

    I'm not sure of, but I think you have to declare PLCType as Long because Int in SQL is saved in 4 bytes where Integer in VBA is only a 2 bytes type.

    Long in VBA is saved as a 4 bytes type.

    Jan

    Wednesday, May 27, 2015 10:25 AM
  • Jan,

      I fixed, in query, I used Format(PLCType, "#") to get data.

    Thanks


    James Liang

    • Marked as answer by L.HlModerator Wednesday, June 3, 2015 11:04 AM
    Wednesday, May 27, 2015 3:28 PM
  • and where command will be like this.

     "where  [Due Date] >= (Getdate() - 1) and      [Due Date] <= Getdate() and " & _

     "T0.[Production Line] = '" & BEProdLine & "' and " & _

     "T2.[Type] = '" & Format(PLCType, "#") & "';"


    James Liang

    • Marked as answer by JamesLiang Friday, June 5, 2015 5:59 PM
    Wednesday, May 27, 2015 4:18 PM