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

[lookingforK]

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?:banghead:

Thank you.

 

[CJ_London]

Re: How to solve error 3828 caused by using a multivalued field & parameters in query

Quite frankly I think multivalue fields are more trouble than they are worth - I've yet to find a situation where they are a benefit.

One or more of your tables referred to in your query you are exporting has these multivalue fields - either don't export them or convert them to text (or numeric as appropriate) fields and use a more advanced database design - basically a separate table

 

[boblarson]

Re: How to solve error 3828 caused by using a multivalued field & parameters in query

What is the SQL of the query you are trying to export?

 

[lookingforK]

Re: How to solve error 3828 caused by using a multivalued field & parameters in query

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.:banghead:

 

[boblarson]

Re: How to solve error 3828 caused by using a multivalued field & parameters in query

Change your query so that

[Week View DS 2013].[Initiative Type],

Shows

[Week View DS 2013].[Initiative Type].VALUE

The .VALUE part breaks it out of Multi-Select mode and lists each record out repeated with the Multi-Select Value each on a single line for that record.

 

[CJ_London]

Re: How to solve error 3828 caused by using a multivalued field & parameters in query

Doesn't that then give you say 5 rows if there are 5 items selected in the multivalue field?

 

[lookingforK]

Re: How to solve error 3828 caused by using a multivalued field & parameters in query

Thank you,

I tried, but got another error Run-time error '3125'.

I am just wondering what kind of function (e.g. DLOOKUP???) I can use to to covert the values in the MVF to strings in query? :banghead:

Change your query so that

[Week View DS 2013].[Initiative Type],

Shows

[Week View DS 2013].[Initiative Type].VALUE

The .VALUE part breaks it out of Multi-Select mode and lists each record out repeated with the Multi-Select Value each on a single line for that record.

 

[boblarson]

Re: How to solve error 3828 caused by using a multivalued field & parameters in query

Taking a look at your SQL, what is this SHORT that you have defined? The declaration should be DATETIME.

 

[lookingforK]

Re: How to solve error 3828 caused by using a multivalued field & parameters in query

I think SHORT should be assigned automatically by MS Access 2007 because I set up the 2 parameters in "Query Parameters" dialog box as Integer.

[Forms]![Export Reports]![ComboWKE] & [Forms]![Export Reports]![ComboWIS] are both week numbers from 1 ~ 52, not for Datetime.