Help with VBA SQL Statements

[kaspar]

Hi...

I'm writing some code that takes data out of Access and puts it inot Excel to plot a graph using SQL statements. It works fine, but if I use 'WHERE' in my SQL statement, it does not show anything. Otherwise it works just fine. As a result, I have TOO many values toi plot. I need to use 'WHERE' in the SQL statement to reduce the number of records that are pulled out of Access and put intot Excel.

Can someone help me with this problem?

kaspar

[Memnoch1207]

post your code

[MileOut]

Here's the code [edited by alex_read - 05/06/2003]

Private Sub CommandButton3_Click() 
 
Dim cnn As ADODB.Connection 
Dim rsTemp As ADODB.Recordset 
Dim cmdCommand As ADODB.Command 
 
Dim i As Integer 
 
i = 2 
 
' Open the connection. 
Set cnn = New ADODB.Connection 
With cnn 
.ConnectionString = _ 
"Provider=Microsoft.Jet.OLEDB.4.0" 
.Open ThisWorkbook.Path & "\x.mdb" 
End With 
 
' Set the command text. 
Set cmdCommand = New ADODB.Command 
Set cmdCommand.ActiveConnection = cnn 
With cmdCommand 
.CommandText = "SELECT [Trapping Information].Week, [Mouse-Main].Chip " & _
"FROM [Mouse-Main] INNER JOIN [Trapping Information] " & _
"ON [Mouse-Main].ID = [Trapping Information].[Mouse-Main_ID] " & _
"WHERE ((([Mouse-Main].Chip)=201-87E3));" 
.CommandType = adCmdText 
.Execute 
End With 
 
' Open the recordset. 
Set rsTemp = New ADODB.Recordset 
Set rsTemp.ActiveConnection = cnn 
rsTemp.Open cmdCommand 
 
'Move to start of recordset if not there already 
If Not rsTemp.BOF Then rsTemp.MoveFirst 
 
Do While Not rsTemp.EOF 
 
Cells(i, 1).Value = rsTemp("Chip") 
Cells(i, 2).Value = rsTemp("Week") 
 
i = i + 1 
rsTemp.MoveNext 
Loop 
 
Set cmdCommand = Nothing 
rsTemp.Close 
cnn.Close 
 
End Sub

[alex_read]

What if you used a like clause & not an equal to, does his show anything...

WHERE ((([Mouse-Main].Chip) LIKE 201%

(not sure if that's percentage or * there, one works with access & not SQL server & visa versa.

Also, are you searching a string column & need to enclost the figure in single quotes?

[alex_read]

Btw - kaspar, I've edited your post to include the code - it's easier for everyone reading to place the code here with your subject rather than having two posts and using links between them...

Ephesians, sorry mate, since I've merged the two posts into 1 it means you've lost a whole post from your count when I deleted the other post!