Runtime error retrieving data from MS Access Database

[dan_pafc]

Ive posted this in the Excel forum but after thought it would probably best sit here...

Hi

Im using the following to retrieve data from MS Access Database.

The Code pulls the data fine until I add in the line "WHERE PlayerData.[MatchID] = """ & Sheet3.Range("F1").Value & """"

Where I get a 'Data Type Mismatch in Criteria Expression." run time error. Whilst im guessing its because im making use of a variable (sheet3.range("F1").Value is calculated by a Vlookup) I cannot for the life of me figure out how to fix it.

Sub GetTeamData()
Dim rsData As ADODB.Recordset
Dim sConnect As String
Dim sSQL As String
Dim sPath As String

sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"

'Create the connection string
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & "FFL.mdb;"

'Build the SQL query
sSQL = "SELECT PlayerData.[ID], PlayerData.[MatchID], PlayerData.[SelectionNo] " & _
"FROM PlayerData " & _
"WHERE PlayerData.[MatchID] = """ & Sheet3.Range("F1").Value & """"

'Retrieve the data using ADO

Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, _
adOpenForwardOnly, adLockReadOnly, adCmdText
If Not rsData.EOF Then
Sheet4.Range("M2").CopyFromRecordset rsData
End If

rsData.Close
Set rsData = Nothing

End Sub

Any help anyone can give would be hugely appreciated!!

 

[BrianMH]

What data type is PlayerData.[MatchID]. If it is a number then you shouldn't be adding quotes to your criteria.

[COLOR=#333333]"WHERE PlayerData.[MatchID] = " & Sheet3.Range("F1").Value[/COLOR]

In future please use code tags on posts. If you go advanced and then select your code portion and click the # symbol above the area you type in it will insert them for you.

 

[dan_pafc]

Great that worked a treat

"WHERE PlayerData.[MatchID] = " & Sheet3.Range("F1").Value

Also now know about 'coding' (although I couldnt see the # symbol).

Thanks for the reply.

Dan.

 

[BrianMH]

Glad to help!