change code to be literal

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Hello Everyone,

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

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"
            '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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Kurt

You need to surround the month with single quotes:

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

You don't need the parameters line either.

Richard
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
error messge

Hello Richard,

Thanks I am getting closer now.

Here is the code
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
comm.CommandText = "SELECT Apps FROM [track apps] WHERE Month = 'June'"
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 23, 2002
Messages
1,664

ADVERTISEMENT

got sql view

Hello Norrie,

Here is what I have now on the SELECT statement:

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'"
    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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Kurt

Shouldn't the SQL be enclosed in quotes?
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
quotes

Norie,

Yes you are right.

I tried putting the quotes like this:

Code:
    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:

Code:
    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

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Kurt

Try this.
Code:
    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
 

Forum statistics

Threads
1,141,755
Messages
5,708,320
Members
421,565
Latest member
Lastadiego

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top