String Building for Where Clause in query
 

Hi,

I'm hoping someone can help with a very simple problem. I have a
column in excel that contains numbers of 9 digits each. I have an
access table containing these numbers and a lot of additional
information. I am trying to execute a query through code, that will
return this additional data. I have made the query work by going down
each line in excel and opening a new query and pulling back the
information WHERE the article number = a variable containing the
number from excel. This works fine but is slow. What I would like to
do is build a string containing all the article numbers, to act as a
multiple criteria WHERE query. I have had this work when I have used
the numbers directly, but when I try it through code, I get a "Type
mismatch" error as soon as the macro reaches the query. Below is the
string building and query code. Can anyone point out why this is not
working?

saparticle = ActiveCell
criteriastring = "(`Final Output`.Article='" & saparticle & "') "
While ActiveCell.Offset(1, 0) < ""
ActiveCell.Offset(1, 0).Select
saparticle = ActiveCell
criteriastring = criteriastring & "OR (`Final Output`.Article='" &
saparticle & "') "
Wend
Range("R4").Name = "onedctarget"

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=H:\*******.mdb;DefaultDir=H:
\*****;DriverId=281;FIL=MS Access;MaxBuffe" _
), Array("rSize=2048;PageTimeout=5;")),
Destination:=Range("onedctarget"))
.CommandText = Array( _
"SELECT `Final Output`.Article,`Final Output`.Site,`Final
Output`.Listed, `Final Output`.OneDC" & Chr(13) & "" & Chr(10) & "FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output`" & Chr(13)
& "" & Chr(10) & "WHERE " & criteriastring)
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With


Any help would be much appreciated.

Thanks,

Dan

String Building for Where Clause in query
 

Build the SQL as Query String & Criteria
ie
"SELECT Article,Site,Listed, OneDC FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output` WHERE Article
in(1,2,3,4,5)

The (12,3,4,5) is actually your Criteria field , which can be built by this
funtion:



Function BuildListForAccessString(StartAddress)
Dim ListString As String
Dim xTemp
Dim x
x = 0
Range(StartAddress).Activate
xTemp = LTrim(RTrim(ActiveCell.Value))
Do Until xTemp = ""
If x 0 Then ListString = ListString & ", " & xTemp Else ListString =
xTemp
ActiveCell.Offset(1, 0).Activate
x = x + 1
xTemp = LTrim(RTrim(ActiveCell.Value))
Loop
BuildListForAccessString = (ListString)
End Function

Try it. put 1,2,3 in rows 1-3 and run:
Sub get1()
MsgBox (BuildListForAccessString("A1"))
End Sub


So your final query will be :
"SELECT Article,Site,Listed, OneDC FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output` WHERE Article
in(" & Function BuildListForAccessString(StartAddress) & ")"
HTH
Carl

" wrote:

Hi,

I'm hoping someone can help with a very simple problem. I have a
column in excel that contains numbers of 9 digits each. I have an
access table containing these numbers and a lot of additional
information. I am trying to execute a query through code, that will
return this additional data. I have made the query work by going down
each line in excel and opening a new query and pulling back the
information WHERE the article number = a variable containing the
number from excel. This works fine but is slow. What I would like to
do is build a string containing all the article numbers, to act as a
multiple criteria WHERE query. I have had this work when I have used
the numbers directly, but when I try it through code, I get a "Type
mismatch" error as soon as the macro reaches the query. Below is the
string building and query code. Can anyone point out why this is not
working?

saparticle = ActiveCell
criteriastring = "(`Final Output`.Article='" & saparticle & "') "
While ActiveCell.Offset(1, 0) < ""
ActiveCell.Offset(1, 0).Select
saparticle = ActiveCell
criteriastring = criteriastring & "OR (`Final Output`.Article='" &
saparticle & "') "
Wend
Range("R4").Name = "onedctarget"

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=H:\*******.mdb;DefaultDir=H:
\*****;DriverId=281;FIL=MS Access;MaxBuffe" _
), Array("rSize=2048;PageTimeout=5;")),
Destination:=Range("onedctarget"))
.CommandText = Array( _
"SELECT `Final Output`.Article,`Final Output`.Site,`Final
Output`.Listed, `Final Output`.OneDC" & Chr(13) & "" & Chr(10) & "FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output`" & Chr(13)
& "" & Chr(10) & "WHERE " & criteriastring)
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With


Any help would be much appreciated.

Thanks,

Dan

String Building for Where Clause in query
 

By the way, you don't need all that extra baggage in default settings:
just list the important ones
.. . .

..RefreshStyle = xlOverwriteCells
..Refresh BackgroundQuery:=False
End With



"Candyman" wrote:

Build the SQL as Query String & Criteria
ie
"SELECT Article,Site,Listed, OneDC FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output` WHERE Article
in(1,2,3,4,5)

The (12,3,4,5) is actually your Criteria field , which can be built by this
funtion:



Function BuildListForAccessString(StartAddress)
Dim ListString As String
Dim xTemp
Dim x
x = 0
Range(StartAddress).Activate
xTemp = LTrim(RTrim(ActiveCell.Value))
Do Until xTemp = ""
If x 0 Then ListString = ListString & ", " & xTemp Else ListString =
xTemp
ActiveCell.Offset(1, 0).Activate
x = x + 1
xTemp = LTrim(RTrim(ActiveCell.Value))
Loop
BuildListForAccessString = (ListString)
End Function

Try it. put 1,2,3 in rows 1-3 and run:
Sub get1()
MsgBox (BuildListForAccessString("A1"))
End Sub


So your final query will be :
"SELECT Article,Site,Listed, OneDC FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output` WHERE Article
in(" & Function BuildListForAccessString(StartAddress) & ")"
HTH
Carl

" wrote:

Hi,

I'm hoping someone can help with a very simple problem. I have a
column in excel that contains numbers of 9 digits each. I have an
access table containing these numbers and a lot of additional
information. I am trying to execute a query through code, that will
return this additional data. I have made the query work by going down
each line in excel and opening a new query and pulling back the
information WHERE the article number = a variable containing the
number from excel. This works fine but is slow. What I would like to
do is build a string containing all the article numbers, to act as a
multiple criteria WHERE query. I have had this work when I have used
the numbers directly, but when I try it through code, I get a "Type
mismatch" error as soon as the macro reaches the query. Below is the
string building and query code. Can anyone point out why this is not
working?

saparticle = ActiveCell
criteriastring = "(`Final Output`.Article='" & saparticle & "') "
While ActiveCell.Offset(1, 0) < ""
ActiveCell.Offset(1, 0).Select
saparticle = ActiveCell
criteriastring = criteriastring & "OR (`Final Output`.Article='" &
saparticle & "') "
Wend
Range("R4").Name = "onedctarget"

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=H:\*******.mdb;DefaultDir=H:
\*****;DriverId=281;FIL=MS Access;MaxBuffe" _
), Array("rSize=2048;PageTimeout=5;")),
Destination:=Range("onedctarget"))
.CommandText = Array( _
"SELECT `Final Output`.Article,`Final Output`.Site,`Final
Output`.Listed, `Final Output`.OneDC" & Chr(13) & "" & Chr(10) & "FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output`" & Chr(13)
& "" & Chr(10) & "WHERE " & criteriastring)
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With


Any help would be much appreciated.

Thanks,

Dan