Variable in SQL Statement

[Ricky1]

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.

[mendhak]

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

[Ricky1]

gave me invalid syntax error

[si_the_geek]

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

[Ricky1]

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

stil getting an error, to many qoutas?

[mendhak]

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

[Ricky1]

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

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.

[Ricky1]

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

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

?

[si_the_geek]

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:

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.

[Al42]

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

[Ricky1]

yes tblData is the name of my table.

[Al42]

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

[ganeshmoorthy]

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

[mendhak]

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.