Problem passing variables SQL query

[pjammer1]

USING VB6 AND ACCESS 2002 (Sorry Old Stuff)

I am trying to pass 3 variables into an SQL query. I get error: "No value give for one or more required parameters."
There seems to be a problem with the SQL syntax although it compiles perfectly. The SQL statement executes with this:


strSQL = "SELECT * FROM SCHEDULE Where WEEK =" & intWeek & " AND YEAR =" & intYear

But when adding the third variable with AND I get the error message as stated :

strSQL = "SELECT * FROM SCHEDULE Where WEEK =" & intWeek & " AND YEAR =" & intYear & " AND SEASON =" & strSeason

The variable name SEASON matches the correct field name.

Here is the VB code:

Private Sub cmdSubmit_Click()
Dim intYear As Integer
Dim intWeek As Integer
Dim strSeason As String
Dim strSQL As String


If optRegSeason.Value = True Then
strSeason = "R"
Else
strSeason = "P"
End If

intYear = Combo1.Text
intWeek = Combo2.Text

Dim db As Database, rs As Recordset
Set db = OpenDatabase("C:\Documents and Settings\G'Day Mate\My Documents\WASUP.mdb")
Set rs = db.OpenRecordset("SCHEDULE", dbOpenDynaset)
On Error Resume Next

strSQL = "SELECT * FROM SCHEDULE Where WEEK =" & intWeek & " AND YEAR =" & intYear & " AND SEASON =" & strSeason
Adodc1.RecordSource = strSQL
Adodc1.Refresh

Any help would be appreciated.

Gary

 

[TheAceMan1]

How are ya pjammer1 . . .

Strings need to go inside quotes:

[blue]strSQL = "SELECT * FROM SCHEDULE Where [red][b]([/b][/red]WEEK =" & intWeek & " AND YEAR =" & intYear & " AND SEASON = [red][b]'[/b][/red]" & strSeason & "[red][b]'[/b][/red]"[red][b])[/b][/red][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[danvlas]

And keep yourself on the safe side by changing

YEAR =

to

[YEAR] =

Even better, if possible, change the name of the field. "Year" is a reserved word (a function)

HTH


Daniel Vlas
Systems Consultant

 

[pjammer1]

Sorry for the belated reply. Forgot to bookmark this site and could not remember where I had posted. The string variable in quotes worked! Thanks so much for the help.