Results 1 to 4 of 4
  1. #1
    lookingforK is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    31

    Question How to solve error 3828 caused by using a multivalued field & parameters in query?

    Hi,



    I am using VBA with MS Access 2007 to export data from a query to MS Excel 2007.

    I use {DoCmd.TransferSpreadsheet acExport, , ...} to export data.

    But I get "Run-time error 3828", that is: Cannot reference a table with a multi-valued field using an IN clause that refers to another database.

    I test the VBA code many rounds, and find the cause is:
    * Can't use the 2 parameters (that come from 2 combo boxes on a form and are used as a selection criterion) and the multi-valued field in the query at the same time.

    In other words, when I just use the 2 parameters after removing the multi-valued field (or just use the multi-valued field after removing the 2 parameters), the VBA running is gonna be fine.

    What's wrong with this?
    How can I use the parameters and the multi-valued field at the same time when running the VBA?

    Thank you in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ultimate solution is to not use multi-value fields (I REFUSE to use them).

    The 2 parameters are from two multi-select comboboxes?

    You want to export the data in the multi-value field?

    Show the query SQL statement.
    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
    lookingforK is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2012
    Posts
    31
    Thank you.

    The 2 para.s are from two single-valued comboboxes.

    I want to export the data in the multi-valued field.

    SQL:
    PARAMETERS [Forms]![Export Reports]![ComboWKE] Short, [Forms]![Export Reports]![ComboWIS] Short;
    SELECT [Week View DS 2013].ID, [Week View DS 2013].AVP, [Week View DS 2013].[Business Unit], [Week View DS 2013].Initiative, [Week View DS 2013].[Initiative Type], IIf([Start Year]="2012",1,[Adjust Week In-Store]) AS [Adjusted Week In-Store 2013], IIf([End Year]>"2013",52,[Adjust Week Ending]) AS [Adjusted Week Ending 2013]

    FROM [Week View DS 2013]
    WHERE (((IIf([Start Year]="2012",1,[Adjust Week In-Store]))<=[Forms]![Export Reports]![ComboWKE]) AND ((IIf([End Year]>"2013",52,[Adjust Week Ending]))>=[Forms]![Export Reports]![ComboWIS]));


    [Week View DS 2013].[Initiative Type] is a multivalued field.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have never used the PARAMETERS clause for any query. But then I don't use parameterized queries and very seldom do exports.

    The only purpose I can see for the clause is to try and validate the input. Instead, control validation with the form features and eliminate the PARAMETERS clause.

    AFAIK, to export data from multi-value field, the query must expand the values to separate rows. Review http://office.microsoft.com/en-us/ac...33722.aspx#BM7
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-10-2012, 02:15 PM
  2. How to solve syntex Error In INSERT INTO Statement?
    By kingsoh in forum Programming
    Replies: 0
    Last Post: 06-07-2012, 12:00 PM
  3. Replies: 4
    Last Post: 03-13-2012, 12:50 PM
  4. Multivalued Look-Up Field Problem
    By Mike Wood in forum Access
    Replies: 4
    Last Post: 02-27-2012, 01:26 PM
  5. Replies: 2
    Last Post: 12-02-2010, 02:35 AM

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