Is it able to pass the VBA variable into ADO SQL statement?

Question

I use ADO to run the SQL in excel .

The SQL is

 sSQLSting = "SELECT  officer ,sum(mkt) from [$DB] where month=2 group by officer"

In my system , there is a combobox that consist of the options of YES & NO.

For Yes , the SQL where statement would become where month=3

For No , the SQL where statement would become where month=4

In my plan , I want to Dim an integer variable to store month in VBA.Then , variable -month gets the combobox result so that 3 will be stored into month.

Lastly , the integer variable-month can pass to the SQL statement ***

     sSQLSting = "SELECT  officer ,sum(mkt) from [$DB] where month=***group by officer

This is my plan . Is it possible to catch the combobox result and pass the value to SQL statement ?

KyloRen · Accepted Answer · 2016-07-15 09:23:55Z

1

Something like this will be able to pass a variable in a SQL statement. It is still a string so you just need to treat it like one.

Dim month As Long
 month = 2
 sSQLSting = "SELECT  officer ,sum(mkt) from [$DB] where month= " & month & " group by officer"

For a combobox, something like this,

Dim month As Long
     month = combobox1.Value
     sSQLSting = "SELECT  officer ,sum(mkt) from [$DB] where month= " & month & " group by officer"

Of coarse you will have to edit the code to suit your combo_box name etc.

answered Jul 15, 2016 at 9:23

KyloRen

2,7715 gold badges33 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Vito Over a year ago

now i change the combobox value to 1-12 .Dim month As Long .AddItem 1 .AddItem 2 .... month = combobox1.Value where month= " & month &" it doesn't work

KyloRen Over a year ago

@Vito if you are using yes/no you will have to pass an integer into the variable.

Vito Over a year ago

How about wt if i change the combobox options to additem 1 .additem 2 .... .additem 12 (12 month in integer format) .Then catch the month by month = combobox1.Value.Lastly , pass the value to SQL where month= " & month & .it doesn't work

KyloRen Over a year ago

@Vito what exactly is the combo box out putting?

Vito Over a year ago

the combobox let the user select the month . so the user can select the month from 1-12 .Then the choice will be store into variable month and pass to the SQL - where clause where month ='&month&"

|

Collectives™ on Stack Overflow

Is it able to pass the VBA variable into ADO SQL statement?

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related