run time error on sql string

[metalman]

this is my string and i am getting a operand missing in the from statment. anyone have any ideas as to what is wrong with it.


sql_str = "SELECT Amount(Amount) as m " & _
"FROM EntryTable INNER JOIN HeaderTable ON EntryTable.Reference # = HeaderTable.REference # " & _
gWhereStrArr(i) & "And ItemTable.Item Discription = " & gColNameArr(j) & " "

 

[cdukes]

What are the # characters for ?

Try putting msgbox sql_str after the statement to see exactly what it is trying to execute and add it to the post.



Hope this helps,

Chris Dukes

 

[metalman]

the # characters are part of the feild in the database reference # = reference number

 

[johnwm]

Put a

Debug.Print sql_str

statement in your code and see what the generated query looks like. Usually the defect is then obvious (missing spaces etc). If it isn't obvious, the post the offending string for us to look at. Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[metalman]

Public Sub Import()
Dim sht As Worksheet
Dim conn_str As String
Dim sql_str As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Dim j As Integer

conn_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\rhinson\My Documents\reporting.mdb"
Set cn = New Connection
cn.Open conn_str

Set sht = Worksheets("Detail&quot
For i = 1 To RowsCount
For j = 1 To ColsCount
sql_str = "SELECT Amount(Amount) as m " & _
"FROM EntryTable INNER JOIN HeaderTable ON EntryTable.Reference # = HeaderTable.REference # " & _
gWhereStrArr(i) & "And ItemTable.Item Discription = " & gColNameArr(j) & " "



Set rs = New Recordset
Debug.Print sql_str
rs.Open sql_str, cn, adOpenStatic, adLockReadOnly

sht.Cells(i + 7, j + 1) = rs.fields("m&quot

rs.Close
Set rs = Nothing
Next j
Next i

cn.Close
Set cn = Nothing
End Sub

 

[Targol]

If there are spaces in your fields names, surround them with "[" and "]".
try :

sql_str = "SELECT Amount(Amount) as m " & _
          "FROM EntryTable INNER JOIN HeaderTable ON EntryTable.[Reference #] = HeaderTable.[REference #] " & _
                gWhereStrArr(i) & "And ItemTable.[Item Discription] = " & gColNameArr(j) & " "

other hint :
are you sure your

gWhereStrArr(i)

variable ends with a blank. If no, it's value will be "glued" with the following "and" and will raise an error. Water is not bad as soon as it stays out human body ;-)

 

[metalman]

would like to thank all you for your help putting the brackets in got me passed that error know i just got to get rid of the rest but thanks again.