Variable in SQL Statement

Code:

---


Public Sub LoadStats(Tourney As String, Entry As String, Entrants As String)

rs.Open "Select Count(*) from tblData Where Type = ' &Tourney& ' and Entry = ' &Entry& '"

If Not rs.EOF Then
    Text1.Text = rs.Fields(0).Value
End If

End Sub

---

i have three variables in my call but i can't seem to write them properly in the SQL box and getting fustrated because its probably simple.

rs.Open "Select Count(*) from tblData Where Type = '" & Tourney & "' and Entry = '" & Entry & "';"

gave me invalid syntax error

Remove the ; from just before the end (some databases want it, others dont).

rs.Open "Select Count(*) from tblData Where Type = '" & Tourney & "' and Entry = '" & Entry & "'"

stil getting an error, to many qoutas?

rs.Open "Select Count(*) AS CountRows from tblData Where [Type] = '" & Tourney & "' and [Entry] = '" & Entry & "'"

Code:

---

Public Sub LoadStats(Tourney As String, Entry As String, Entrants As String)

rs.Open "Select Count(*) from tblData Where [Type] = '" & Tourney & "' and [Entry] = '" & Entry & "'"

If Not rs.EOF Then
    Text1.Text = rs.Fields(0).Value
End If

End Sub

---

calling it like this

Code:

---

Private Sub Command1_Click()
    Call LoadStats(Combo1.Text, Combo2.Text, Combo3.Text)
End Sub

---

and i get this error

Run-time error '-2147217900(80040e14)':
Syntax error in FROM clause.


:(

Someone must know where it is wrong? it can't be that much wrong.

Code:

---

Public Sub LoadStats(Tourney As String, Entry As String, Entrants As String)

rs.Open "Select Count(*) from tblData Where Type = '" & Tourney & "' and Entry = '" & Entry & "'"

If Not rs.EOF Then
    Text1.Text = rs.Fields(0).Value
End If

End Sub

---

?

As the error is "Syntax error in FROM clause.", the issue seems to be with the table name.. but I can't see why that would be.

Try this:

VB Code:

rs.Open "SELECT Count(*) AS CountRows FROM [tblData] WHERE [Type] = '" & Tourney & "' and [Entry] = '" & Entry & "'"

If there are still problems, let us know what is in the Tourney and Entry variables.

You're not using tblData as your table name, are you?

yes tblData is the name of my table.

Okay - it's what I would expect as an example name.

just check spelling in the table name in your database...if you run the same code in access are you getting the results....

Run your statement in Query Analyzer. I don't understand what the syntax error is. Maybe you've spelt something wrong, but run it in Query Analyzer anywyas.