PDA

View Full Version : is there a limit to how many WHERE clauses you can have in an SQL statement?



samuelimtech
01-29-2015, 05:01 AM
I keep getting a type mistch error when I add a fifth OR in the WHERE clause of my SQL statement.
I actually have upto 30 names in a list on another tab [sheets("Staff").range(cells(2,3),cells(32,3))]
if i cant query my database this way i.e assigning a namevariable to each name is there another way to do it? if so can you please edit the below as im not the best at this.

thanks


With Sheets("Test").ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=MS Access Database;DBQ=Y:\Timesheet - Copy.accdb;DefaultDir=Y:;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT `Import Table`.ID, `Import Table`.`Alpha Name`, `Import Table`.`Employee Number`, `Import Table`.`Work Date`, `Import Table`.`Account Number`, `Import Table`.Explanation, `Import Table`.`Sub- L" _
, _
"edger`, `Import Table`.`Pay Code`, `Import Table`.`Billing Rate`, `Import Table`.Hours, `Import Table`.`Week No`, `Import Table`.`Timesheet Identifier`, `Import Table`.`Week Ending`, `Import Table`.Co" _
, _
"mments" & Chr(13) & "" & Chr(10) & "FROM `Y:\Timesheet - Copy.accdb`.`Import Table` `Import Table`" & Chr(13) & "" & Chr(10) & _
"WHERE (`Import Table`.`Alpha Name`='" & Name1 & "' OR" & _
"`Import Table`.`Alpha Name`='" & Name2 & "' OR" & _
"`Import Table`.`Alpha Name`='" & Name3 & "' OR" & _
"`Import Table`.`Alpha Name`='' )")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_MS_Access_Database_1"
.Refresh BackgroundQuery:=False
End With

I actually upto