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

 

[Uncle Gizmo]

Possibly the field contents you are feeding into the query contain quotation marks or something?

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

Also it would be sensible to change the words from real words like source and identity into programming words like ... fldSource and fldIdentity... if you always use your words like this you will avoid any possibility of clashing with reserved words which might be something that's happening here....

Sent from my SM-G925F using Tapatalk

 

[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