Alexander Willey
Registered User.
- Local time
- Today, 21:46
- Joined
- Mar 1, 2013
- Messages
- 31
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
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