passing variable into sql statement

[Champinco]

Hi guys i have a small problem passing a variable into a sql statement. Could i please get some assistance.
Here is my current code:

Private Sub Form_Open(Cancel As Integer)
Dim dc As Variant
Select Case Me.OpenArgs
    Case "Dealer_Totals"
        Me.Date_Combo.Visible = True
        Me.Distributor_Combo.Visible = False
        Me.Date_Combo.RowSource = "SELECT COMMISSION_MONTH FROM COMMISSION_MONTH WHERE (TRANSACTIONS_IMPORTED = 1)"
    Case "Dealer_Product_Group"
        '1st make Date_combo visible = false, until user first selects distributor, then make date_combo visible = true, and pass in distributor value into select statement below
        Me.Date_Combo.Visible = false
        Me.Distributor_Combo.Visible = True
        dc = Me.Distributor_Combo
        Me.Date_Combo.RowSource = "SELECT distinct COMMISSION_MONTH FROM commission_txn WHERE (payment_point = @dc)"
   Case "nilMajor_Movement"
        Me.Date_Combo.Visible = True
        Me.Distributor_Combo.Visible = False
        Me.Date_Combo.RowSource = "SELECT COMMISSION_MONTH FROM COMMISSION_MONTH WHERE (TRANSACTIONS_IMPORTED = 1)"
    End Select
End Sub

As the comment in case Dealer_Product_Group states I wish to do 2 things:
1) make date_combo invisible, then visible once the user firstly selects a distributor in the distributor_combo box.

2) Once this is done i want to pass this value into the dc variable as defined in the sub. Is this the correct way to do it? the problem is in sql to pass a variable requires the @ symbol but i cant do this in VB i.e: cant do dim @dc as variant

The reason why i want to do this is because i want to limit the amount of dates the user inputs once a distributor has been chosen. (type of validation such that the user cant put in bogus dates based on a distributor)
Cheers
Champinco

[Besoup]

Gotta concatenate the variable to the sql string

Me.Date_Combo.RowSource = "SELECT distinct COMMISSION_MONTH FROM commission_txn WHERE (payment_point = '"& dc &"')"

[Champinco]

That seems to work...i.e: the code runs and i get no errors, but it does not return back the dates in that date_combo box. Am i capturing the dc variable correctly from the distributor_combo box? The query itself works and i tested it in sql analyser, i think the '" & dc & "' may not be correct or the way i declare the variable and call it? i.e: Dim dc As Variant

Case "Dealer_Product_Group"
        Me.Distributor_Combo.Visible = True
        Me.Distributor_Combo.SetFocus
        dc = Me.Distributor_Combo
        Me.Date_Combo.RowSource = "SELECT distinct COMMISSION_MONTH FROM commission_txn WHERE (payment_point = '" & dc & "')"
        'Me.Date_Combo.Visible = True

[jeroen79]

What is the value of dc when you put it in the query?
What happens when you dim dc as a string?

[si_the_geek]

Moved to Database Development forum

Variables should not be declared as Variant unless you have no other choice.

If it is a text value, use the data type String. If it is numeric you can use Integer (whole numbers up to 32k), Long (bigger whole numbers), Single (numbers with decimal places), ....

If your payment_point field is not text you need to remove the ' marks. (if it is a Date you should use # in Access, ' for most other systems).

[Al42]

And, as you've pointed out in other threads, Si, if it's a field holding a date, it should be a Date type field if you ever want to do comparisons (before, after, between, etc.)

[theun789]

Hey. I am trying to do something similar in a program I am writing, but my combobox does not have a .rowsource option.

In essence, I want to populate a combobox with data from a query that updates each time another combobox's selected index is changed.

Any thoughts on how I would do this, why my combobox does not have the .rowsource, or what I should use instead of the .rowsource.

I am working in VB 2005

Thanks

[si_the_geek]

The code above is for VBA (basically Classic VB), so is not appropriate for you - I'm afraid I dont know how to use Comboboxes in VB.Net (which is what VB 2005 is), so you'll have to find a method somewhere else.. presumably there are several threads about it in our VB.Net forum.