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.
Re: Variable in SQL Statement
rs.Open "Select Count(*) from tblData Where Type = '" & Tourney & "' and Entry = '" & Entry & "';"
Re: Variable in SQL Statement
gave me invalid syntax error
Re: Variable in SQL Statement
Remove the ; from just before the end (some databases want it, others dont).
Re: Variable in SQL Statement
rs.Open "Select Count(*) from tblData Where Type = '" & Tourney & "' and Entry = '" & Entry & "'"
stil getting an error, to many qoutas?
Re: Variable in SQL Statement
rs.Open "Select Count(*) AS CountRows from tblData Where [Type] = '" & Tourney & "' and [Entry] = '" & Entry & "'"
Re: 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
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.
:(
Re: Variable in SQL Statement
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
?
Re: Variable in SQL Statement
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.
Re: Variable in SQL Statement
You're not using tblData as your table name, are you?
Re: Variable in SQL Statement
yes tblData is the name of my table.
Re: Variable in SQL Statement
Okay - it's what I would expect as an example name.
Re: Variable in SQL Statement
just check spelling in the table name in your database...if you run the same code in access are you getting the results....
Re: Variable in SQL Statement
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.