Results 1 to 3 of 3
  1. #1
    Shamli is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Location
    California
    Posts
    142

    Run Time Error - 3828 - Multivalued Fields


    Hello Everyone,
    I am facing a problem when I try to export a query to Excel.

    This is the Export Query:
    Code:
    Private Sub Export_To_Excel_Click()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "lstEmployee", "C:\Users\b66sws\Documents\Users\Desktop\TestTOREPORTS.xlsx", True
    End Sub
    This is the error Message:
    Click image for larger version. 

Name:	3828.PNG 
Views:	12 
Size:	18.8 KB 
ID:	36064

    This is my query:
    Code:
    [SELECT Employee_T.UserLogID AS [b66#], Employee_T.EmployeeID, Employee_T.FullName, TaskOrderItems.TaskOderItemStartdate AS [TOI Start Date], TaskOrderItems.TaskOrderItemEndDate AS [TOI End Date], TaskOrderItems.PrimeText AS Prime, TaskOrderItems.SubText AS Sub, TaskOrderItems.TaskOrderText AS TaskOrder, TaskOrderItems.PositionTitleText, TaskOrderItems.ElementText AS Element, TaskOrderItems.Created, TaskOrderItems.IsActive, TaskOrderItems.ContractText
    FROM Employee_T LEFT JOIN TaskOrderItems ON Employee_T.UserLogID = TaskOrderItems.TaskOrderStaffID
    WHERE (((Employee_T.FullName) Is Not Null And (Employee_T.FullName) Like "*" & IIf([forms]![TaskOrder]![txtSearch]<>"",[forms]![taskOrder]![txtSearch],"") & "*") AND ((TaskOrderItems.TaskOrderItemEndDate)>#10/1/2018#) AND ((Employee_T.Deployed)=True))
    ORDER BY Employee_T.FullName, TaskOrderItems.TaskOrderItemEndDate DESC , TaskOrderItems.PrimeText, TaskOrderItems.SubText, TaskOrderItems.PositionTitleText, TaskOrderItems.ElementText, TaskOrderItems.Created DESC;

    Can anyone help me with this.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Which is the multi-value field?

    Must first expand the multiple values to separate rows, like:

    SELECT ID, fieldname.Value AS Data FROM tablename;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure what the purpose of this is

    IIf([forms]![TaskOrder]![txtSearch]<>"",[forms]![taskOrder]![txtSearch],"")
    you are saying if it doesn't="" use the value, otherwise use ""

    the value will either ="", or it won't. SO what is the point?

    also this

    (Employee_T.FullName) Is Not Null And (Employee_T.FullName) Like "*" &.....
    FullName cannot be null and equal something at the same time. As above, either it's null or its not, there's no might be

    I'm pointing these out because they may be creating the situation for your error

    if it is a multivalue field, I'm betting it is TaskOrderItems.TaskOrderStaffID

    and in respect of the error message - just to check - both these tables are in the same database?

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

Similar Threads

  1. Replies: 6
    Last Post: 07-03-2014, 02:32 PM
  2. Replies: 3
    Last Post: 03-13-2013, 04:00 PM
  3. Replies: 4
    Last Post: 03-13-2012, 12:50 PM
  4. MultiValued Fields
    By Lorlai in forum Access
    Replies: 0
    Last Post: 06-10-2011, 10:25 AM
  5. Multivalued fields
    By ydt in forum Forms
    Replies: 1
    Last Post: 08-25-2010, 01:52 PM

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