Run-time error 2147217900

[Alexander Willey]

Hello,
I have a concatenate query that is run using a module given further below, which on its own works OK
When I relate the concatenate query to a related table in a new query or add additional fields to it, it runs OK for a short while, but when I scroll up and down through the data, I then get a Run-time error and it highlights the code below starting RS.open..... in yellow.

The dialogue box error says....
Run-time error '2147217900 (80040e)':
Syntax error (missing operator) in query expression '[Fieldname]='.


here is the module code...

Option Compare Database
Option Explicit
Public Function Conc
(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & " , " & rs!Fld
End If
rs.MoveNext
Loop



I'd be grateful for advice on how to fix this please
Many thanks in advance for any help
Alex

 

[jdraw]

I agree with Uncle Gizmo -- it could very well be related to reserved words.
Also, you don't set a return value to Conc. There is no End Function statement??

It appears that VFld will have a Null followed by a comma and then rs!Fld values separated with commas.

 

[Alexander Willey]

Thank you for your help
Stupid me did not paste whole of module code in, here it is again

Option Compare Database
Option Explicit
Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & " , " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 4)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function