I keep getting a syntax error 3075 missing operator

[atrium]

Private Sub Form_Current()
' ----- I need to find the latest Contact Log entry for this matter
Me.CLogIdFld = DMax("MatterContactsMadeId", "[MatterContactsMade]", "[MatterId] = " & Me.MatterIdFld)
Me.DescriptionFld = DLookup("Description", "MatterContactsMade", "[MatterContactMadeId] = " & CLogIdFld)

End Sub

I get it on the '[MatterId] =' on the bold line above. Me.ClogIdFld = line
If I comment that one out I get it on the one that's not bold. Same error

Any help would be appreciated

 

[June7]

Are MatterID and MatterContactMadeID number type fields?

 

[atrium]

Yes they are

 

[arnelgp]

Private Sub Form_Current()
Dim strReturn as string
strReturn = DMax("MatterContactsMadeId & '|' & Description", "[MatterContactsMade]", "[MatterId] = " & Me.MatterIdFld)
Me.CLogIdFld = Split(strReturn,"|")(0)
Me.DescriptionFld = Split(strReturn, "|")(1)

' ----- I need to find the latest Contact Log entry for this matter
'Me.CLogIdFld = DMax("MatterContactsMadeId", "[MatterContactsMade]", "[MatterId] = " & Me.MatterIdFld)
'Me.DescriptionFld = DLookup("Description", "MatterContactsMade", "[MatterContactMadeId] = " & CLogIdFld)

End Sub

 

[atrium]

I now get the above error. It's because the field Description is a Long Text field. How can I get around it

 

[Gasman]

Private Sub Form_Current()
' ----- I need to find the latest Contact Log entry for this matter
Me.CLogIdFld = DMax("MatterContactsMadeId", "[MatterContactsMade]", "[MatterId] = " & Me.MatterIdFld)
Me.DescriptionFld = DLookup("Description", "MatterContactsMade", "[MatterContactMadeId] = " & CLogIdFld)

End Sub

I get it on the '[MatterId] =' on the bold line above. Me.ClogIdFld = line
If I comment that one out I get it on the one that's not bold. Same error

Any help would be appreciated

Try debugging and see what the criteria actually is?

 

[June7]

I tested original code structure with Memo field (I have Access 2010) and works just fine. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[arnelgp]

Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("select MatterContactsMadeId, Description from MatterContactsMade where MatterID = " & Nz(Me!MatterIdFld, 0))
With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
        Me!CLogIdFld = !MatterContactsMadeId
        Me!DescriptionFld = !Description
    Else
        Me!CLogIdFld = Null
        Me!DescriptionFld = Null
    End If
    .Close
End With
Set rs = Nothing
Set db = Nothing
    
' ----- I need to find the latest Contact Log entry for this matter
'Me.CLogIdFld = DMax("MatterContactsMadeId", "[MatterContactsMade]", "[MatterId] = " & Me.MatterIdFld)
'Me.DescriptionFld = DLookup("Description", "MatterContactsMade", "[MatterContactMadeId] = " & CLogIdFld)

End Sub

 

[Gasman]

Private Sub Form_Current()
Dim strReturn as string
strReturn = DMax("MatterContactsMadeId & '|' & Description", "[MatterContactsMade]", "[MatterId] = " & Me.MatterIdFld)
Me.CLogIdFld = Split(strReturn,"|")(0)
Me.DescriptionFld = Split(strReturn, "|")(1)

' ----- I need to find the latest Contact Log entry for this matter
'Me.CLogIdFld = DMax("MatterContactsMadeId", "[MatterContactsMade]", "[MatterId] = " & Me.MatterIdFld)
'Me.DescriptionFld = DLookup("Description", "MatterContactsMade", "[MatterContactMadeId] = " & CLogIdFld)

End Sub

@arnelgp
That is a really neat trick, bringing back more than one data item.

 

[arnelgp]

That is a really neat trick,

unfortunately it fails on the Memo

 

[Gasman]

unfortunately it fails on the Memo

Perhaps, but o/p never said anything about a memo field?

 

[June7]

Perhaps, but o/p never said anything about a memo field?

In post #5 they indicated Description is Long Text (formerly known as Memo field).

 

[Gasman]

In post #5 they indicated Description is Long Text (formerly known as Memo field).

Yes, I know June7, but not initially? The error message gave that away as well.

 

[jdraw]

Not sure how exactly you have used your Long text field to act as a table, but you might find this function useful. It uses vbCrLf as line terminator.

 

[atrium]

I found your code jdraw and don't think it suits my needs