Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help!!! Database lookup from excel
I keep getting Syntax error (missing operator) in query
I have tried all ican change the statement but it is still not working Sub DATABASE() Const BILL = "a" Const DOC = "b" Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim looper As Long Dim BILLNO As String, DOCNO Dim cellPointer As Variant strConn = "C:\Documents and Settings\Desktop\smart.mdb" Set con = New ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConn laname = InputBox("Enter SENDING Traping Partner") Cells(1, 1).Value = laname For looper = 1 To Range(BILL & Rows.Count).End(xlUp).Row Set cellPointer = Worksheets("Sheet1").Range(BILL & looper) sSQL = "SELECT tblTradePartner.* FROM tblTradePartner WHERE tblTradePartner.BILL NO = " & cellPointer & " " Set rs = New ADODB.Recordset rs.Open sSQL, con, adOpenStatic, adLockOptimistic If Not IsNull(rs.Fields("DOC NO").Value) Then Range(DOC & looper) = rs.Fields("DOC NO").Value End If rs.Close Set rs = Nothing Next looper cnn.Close Set cnn = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help!!! Database lookup from excel
I think this line is your problem
Set cellPointer = Worksheets("Sheet1").Range(BILL & looper) You can't pass a Range value to the SQL. You can only pass the value in the rnage try this cellPointer = Worksheets("Sheet1").Range(BILL & looper).Value If the Rage is more than one cell you will still have a problem because excel will make cellpointer an array. I always get my Queries working by first manualy doing the query from the worksheet by doing the following Data - Import External Data - New Database Query. Use a fix value for the cellpointer value. Get this working. Then modify the query to use a Range location from the worksheet. If you still get errors post the recorded query and I will make the necessary changes. "mju" wrote: I keep getting Syntax error (missing operator) in query I have tried all ican change the statement but it is still not working Sub DATABASE() Const BILL = "a" Const DOC = "b" Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim looper As Long Dim BILLNO As String, DOCNO Dim cellPointer As Variant strConn = "C:\Documents and Settings\Desktop\smart.mdb" Set con = New ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConn laname = InputBox("Enter SENDING Traping Partner") Cells(1, 1).Value = laname For looper = 1 To Range(BILL & Rows.Count).End(xlUp).Row Set cellPointer = Worksheets("Sheet1").Range(BILL & looper) sSQL = "SELECT tblTradePartner.* FROM tblTradePartner WHERE tblTradePartner.BILL NO = " & cellPointer & " " Set rs = New ADODB.Recordset rs.Open sSQL, con, adOpenStatic, adLockOptimistic If Not IsNull(rs.Fields("DOC NO").Value) Then Range(DOC & looper) = rs.Fields("DOC NO").Value End If rs.Close Set rs = Nothing Next looper cnn.Close Set cnn = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help!!! Database lookup from excel
Thanks. I recorded the macro and this is what i got. it works fine but how do
i incorporate an input box to ask user for the bill no and then return the corresponding doc number? Thanks alot!!! With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DBQ=C:\Documents and Settings\spring-004\Desktop\ -DB_BE.mdb;DefaultDir=C:\Documents and Settings\\Desktop;D" _ ), Array( _ "river={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeou t=5;SafeTransactio" _ ), Array("ns=0;Threads=3;UserCommitSync=Yes;")), Destination:=Range("B12")) .CommandText = Array( _ "SELECT tblPartner.ClientBillNo, tblPartner.DocumentNumber" & Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\Desktop\ DB_BE`.tblPartner tblPartner" & Chr(13) & "" & Chr(10) & "WHERE (tblPartner.Tp" _ , "ClientBillNo='10003') ") .Name = "Query from practice2_3" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub "Joel" wrote: I think this line is your problem Set cellPointer = Worksheets("Sheet1").Range(BILL & looper) You can't pass a Range value to the SQL. You can only pass the value in the rnage try this cellPointer = Worksheets("Sheet1").Range(BILL & looper).Value If the Rage is more than one cell you will still have a problem because excel will make cellpointer an array. I always get my Queries working by first manualy doing the query from the worksheet by doing the following Data - Import External Data - New Database Query. Use a fix value for the cellpointer value. Get this working. Then modify the query to use a Range location from the worksheet. If you still get errors post the recorded query and I will make the necessary changes. "mju" wrote: I keep getting Syntax error (missing operator) in query I have tried all ican change the statement but it is still not working Sub DATABASE() Const BILL = "a" Const DOC = "b" Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim looper As Long Dim BILLNO As String, DOCNO Dim cellPointer As Variant strConn = "C:\Documents and Settings\Desktop\smart.mdb" Set con = New ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConn laname = InputBox("Enter SENDING Traping Partner") Cells(1, 1).Value = laname For looper = 1 To Range(BILL & Rows.Count).End(xlUp).Row Set cellPointer = Worksheets("Sheet1").Range(BILL & looper) sSQL = "SELECT tblTradePartner.* FROM tblTradePartner WHERE tblTradePartner.BILL NO = " & cellPointer & " " Set rs = New ADODB.Recordset rs.Open sSQL, con, adOpenStatic, adLockOptimistic If Not IsNull(rs.Fields("DOC NO").Value) Then Range(DOC & looper) = rs.Fields("DOC NO").Value End If rs.Close Set rs = Nothing Next looper cnn.Close Set cnn = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Database Type Lookup In Excel | Excel Discussion (Misc queries) | |||
lookup database | Excel Worksheet Functions | |||
Please help! Database lookup from excel | Excel Programming | |||
Lookup In Excel from Access Database | Excel Programming | |||
Database Lookup | Excel Worksheet Functions |