change code to be literal

[Kurt]

Hello Everyone,

I would like to change this code to specifically get a month of code let's say June.

Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Dim Month$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
        "SELECT Apps FROM track apps Where Month = June"
            'Month = InputBox("Please input the Month" & _
            '"(for example, 'August').")
    comm.Parameters(0) = ""
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

I also want to take out the input box.

Any suggestions?

TIA

Kurt

 

[Richard Schollar]

Hi Kurt

You need to surround the month with single quotes:

Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Dim Month$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
        "SELECT Apps FROM track apps Where Month = 'June'"
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

You don't need the parameters line either.

Richard

 

[Kurt]

error messge

Hello Richard,

Thanks I am getting closer now.

Here is the code

Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    'Dim Month$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
        "SELECT Apps FROM track apps WHERE Month = 'June'"
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

It stops on the select statement and states it cannot find the table or query track?

What's going on?

Thanks,

Kurt

 

[Norie]

Kurt

That's probably because of the space in the table name.

Generally you should try to name objects, eg tables, without spaces.

If you do have names with spaces try enclosing them in square brackets.

comm.CommandText = "SELECT Apps FROM [track apps] WHERE Month = 'June'"

 

[Kurt]

thanks Norie hopefully one last question

Hello Norie,

Thanks again!

Now how would I use this select statement to add the apps for the month of June. It is working now, but I would like to have the total for the month of June.

Again a big thanks!!

How have you been?

Kurt

 

[Norie]

Kurt

I think you would need a totals query.

The best way to get the correct SQL syntax would be to create a query in the database and goto View>SQL.

 

[Kurt]

got sql view

Hello Norrie,

Here is what I have now on the SELECT statement:

 Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    'Dim Month$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    'comm.CommandText = "SELECT Apps FROM [track apps] WHERE Month = 'June'"
    comm.CommandText = TRANSFORM Sum([track apps].Apps) AS SumOfApps _
        SELECT dbo_SMT_Branches.BranchTranType _
        FROM [track apps] LEFT JOIN dbo_SMT_Branches ON [track apps].Branch = dbo_SMT_Branches.BranchNbr _
        GROUP BY dbo_SMT_Branches.BranchTranType _
        PIVOT [track apps].MONTH;
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

I get a syntax code now on the sql statement.

Thanks,

Kurt

 

[Norie]

Kurt

Shouldn't the SQL be enclosed in quotes?

 

[Kurt]

quotes

Norie,

Yes you are right.

I tried putting the quotes like this:

    comm.CommandText = "TRANSFORM Sum([track apps].Apps) AS SumOfApps _
        "SELECT dbo_SMT_Branches.BranchTranType _
        FROM [track apps] LEFT JOIN dbo_SMT_Branches ON [track apps].Branch = dbo_SMT_Branches.BranchNbr _
        GROUP BY dbo_SMT_Branches.BranchTranType _
        PIVOT [track apps].MONTH";

and i get expected end of statement

I have also tried:

    comm.CommandText = "TRANSFORM Sum([track apps].Apps) AS SumOfApps _
        "SELECT dbo_SMT_Branches.BranchTranType _
        FROM [track apps] LEFT JOIN dbo_SMT_Branches ON [track apps].Branch = dbo_SMT_Branches.BranchNbr _
        GROUP BY dbo_SMT_Branches.BranchTranType _
        PIVOT [track apps].MONTH;"

I still get the same error message.

TIA

Kurt

 

[Norie]

Kurt

Try this.

    strSQL = "TRANSFORM Sum([track apps].Apps) AS SumOfApps "
    strSQL = strSQL & "SELECT dbo_SMT_Branches.BranchTranType "
    strSQL = strSQL & "FROM [track apps] LEFT JOIN dbo_SMT_Branches ON [track apps].Branch = dbo_SMT_Branches.BranchNbr "
    strSQL = strSQL & "GROUP BY dbo_SMT_Branches.BranchTranType "
    strSQL = strSQL & "PIVOT [track apps].MONTH;"
    comm.CommandText = strSQL