Results 1 to 3 of 3
  1. #1
    Dave D is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jun 2014
    Posts
    45

    Long Text field in an Update query

    Here is my question: can I use a Long Text field in an update query and if so, how? - thank you for any help.



    I have included my function containing the update query. It blows up on execute with the following error.

    Error: 3075
    Syntax error (missing operator) in query expression '<div>... Test Text...</div>'

    The 'str' data type (below) is a Long Text data type with Text Format set to Rich Text.

    Code:
    Public Function setReasonSelected(cRecordId As Long, _
                                      pRecordId As Long, _
                                      vBidId As Long, _
                                      str As String) As Boolean
    
    'update all vendor items with the same 'reason selected' as the passed-in str
    '
    'this update will only work if the vendor's item is selected
    '
    On Error GoTo setError
        Dim db As DAO.Database
        Dim strSQL As String
        
        Set db = CurrentDb
        strSQL = "UPDATE tblVendorBidPrices SET reasonSelected = " & str & _
                 " WHERE cRecordId = " & cRecordId & _
                 " AND pRecordId <> " & pRecordId & _
                 " AND vBidId = " & vBidId & _
                 " AND selected = True"
        db.Execute strSQL, dbFailOnError
        setReasonSelected = True                  'set return value
        
    exitHere:
        'housekeeping
        db.Close
        Set db = Nothing
        Exit Function
        
    setError:
        MsgBox "Unable to update 'Reason Selected' for record ID:  " + CStr(pRecordId) + vbCrLf + vbCrLf + _
               "Error:  " + CStr(Err.Number) + vbCrLf + Err.Description
        setReasonSelected = False                 'set return value
        Resume exitHere
    End Function

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    One can update a Long Text field in an update query - but it cannot be used for a join.

    Make the query using Access' query design view - and then switch it to SQL View in order to sanity check your syntax

  3. #3
    Dave D is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jun 2014
    Posts
    45
    The fix was rather simple and I should have thought of it sooner. All I needed to do was wrap str in single quotes.

    Code:
    strSQL = "UPDATE tblVendorBidPrices SET reasonSelected = '" & str & "'" & _
                  " WHERE cRecordId =" & cRecordId & _
                  " AND pRecordId <>" & pRecordId & _
                  " AND vBidId =" & vBidId & _
                  " AND selected = True"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-11-2013, 11:32 PM
  2. Replies: 11
    Last Post: 10-08-2012, 07:27 PM
  3. Ribbons XML code to long for one text field
    By sstrauss87 in forum Access
    Replies: 3
    Last Post: 12-22-2011, 03:46 PM
  4. Replies: 2
    Last Post: 05-05-2010, 02:52 PM
  5. Update Query too long
    By ack9f in forum Queries
    Replies: 3
    Last Post: 04-26-2010, 12:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums