Error Message with Query in Code

[Kharonne]

Hey Guys,

I keep getting this wierd error message with a query I wrote inside some VBA code. Here is the code:

Function GetChiefSeid() As String
Dim cnn As ADODB.Connection, rst As New ADODB.Recordset
Set cnn = currentProject.Connection

Dim Y As String
Y = "6-11-06-A-012"
SQLstr = "SELECT [Authorized Approvers Table].SEID " & _
"FROM [Projects Table], [Authorized Approvers Table]" & _
"WHERE (([Projects Table].Group = [Authorized Approvers Table].Group)" & _
"AND ([Authorized Approvers Table].CurrentApprover =True)" & _
"AND ([Authorized Approvers Table].Email=True)" & _
"AND ([Projects Table].ProjectNumber=Y));"

rst.Open SQLstr, cnn, adOpenStatic, adLockReadOnly

If Not rst.EOF Then
GetChiefSeid = rst!SEID
rst.MoveNext
Do While Not rst.EOF
GetChiefSeid = GetChiefSeid & ";" & rst!SEID
rst.MoveNext
Loop
End If
rst.Close
End Function

Everytime I try to run the code I recieve this error message: 'Run-time error' -2147217904 (80040e10)': No value given for one or more required parameters.

I tested the query out before hand and it works, returning the value I am checking for. The code works fine when I remove that last AND ""AND ([Projects Table].ProjectNumber=Y));" any help to get me on the right track is appreciated.

 

[JANR]

"AND ([Projects Table].ProjectNumber=" & Y & "));"

Put the variabel outside the stringexpression, also check your SPACES around Keywords like:

Select, From, Where, And etc

JR

 

[JANR]

it possible you must wrap singleqoutes around the variable Y

"AND ([Projects Table].ProjectNumber= '" & Y & "' & _
" ));"

JR

 

[Kharonne]

Thanks Friend,

The quotes worked! Huge help!

 

[JANR]

No worries

JR