Using LEFT, FIND In VBA Query

[Carl the DVD Collector]

I'll try to make this as clear as possible given my lack of knowledge in the area.

I'm trying to take the value of a cell, which will be a variable as it is pulled from another query and depending on that value another cell will be populated. This is done with another query.

The value of this first cell is made up of a code, and a decode, so there is a comma "," in place or "-", it doesn't really matter.

I am able to create a formula in Excel to pull the desired result, but I would like to use VBA, as the cell value of the new cell is the formula and my query is looking for a value, not a formula.

=LEFT(C29,((FIND(",",C29))-1))

For instance the value of cell C29 is: "X,XXXX Text"

so this would pull back X before the ","

Sometimes this value will be 1, 2 or 3 digits before the "," so it will not be fixed.

I have gotten it to work if I hard code the value (see code below where I commented it out), I'm just not sure how to use a variable in junction with a LEFT command in the query.

Here is a part of the query in the VBA code:



Public Function FuncAuthorities1()
Province = Sheets("Sheet1").Cells(2, 2)
CodeClass = Sheets("Sheet1").Cells(29, 3)
query = "SELECT DISTINCT a.code || ', ' || a.code_two || ', ' || name "
query = query + "From schema.province_code_class a, schema.class_decode b, schema.code_decode c "
query = query + "WHERE a.code = b.code "
query = query + "AND a.class = c.class "
query = query + "AND a.province_code = '" & Province & "' "
query = query + "AND a.code = '" & CodeClass & "' "
'query = query + "AND a.code = '7' "
query = query + "ORDER BY 1 asc "
Sheets("Sheet1").Activate
Row = 30
While Sheets("Sheet1").Cells(Row, 1) <> ""
Row = Row + 1
Wend

RunQuery Sheets("Sheet1").Range("T2"), query 'pastes query result in cell ("")
Sheets("Sheet1").Cells(2, 20).Select
Selection.Delete


End Function

Thanks in advance for any help you can give.