Too few parameters exporting to text

  • Thread starter Thread starter Phil Smith
  • Start date Start date
P

Phil Smith

I have a pretty straightforward query, which is NOT a parameter query.

Open the query, it works fine. Export to an Excel spreadsheet, works great.

Export to Text, "Too few parameters. Expected 2"

My goal is to automatically export this query to TAB delimited with a macro.

Phil
 
Hi Phil,

What is the SQL of the query? How are you exporting when you get the
error? Manually? Which version of Access?

Clifford Bass
 
Clifford said:
Hi Phil,

What is the SQL of the query? How are you exporting when you get the
error? Manually? Which version of Access?

Clifford Bass

:
Click to expand...
Access 2003.

Right now I am simply going to
File|Export|, Save as type: Textfiles, hit export, get error.
If I choose Excel instead, it works fine.


Query Follows;


SELECT RoyaltyReportBase.[Vendor ID] AS [Supplier#],
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand AS [Invoice ID], 8205 AS Account,
IIf(RoyaltyReportBase!Division="Apparel","Skate",RoyaltyReportBase!Division)
AS Division, "MKTG" AS Department, RoyaltyReportBase.Category,
IIf(RoyaltyReportBase!Brand="Santa Cruz","Santa Cruz
Skate",RoyaltyReportBase!Brand) AS Brand,
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand AS [Memo], "" AS Project,
Sum(RoyaltyReportBase.Royalty) AS Amount
FROM RoyaltyReportBase
GROUP BY RoyaltyReportBase.[Vendor ID],
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand, 8205,
IIf(RoyaltyReportBase!Division="Apparel","Skate",RoyaltyReportBase!Division),
"MKTG", RoyaltyReportBase.Category, IIf(RoyaltyReportBase!Brand="Santa
Cruz","Santa Cruz Skate",RoyaltyReportBase!Brand),
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand, ""
ORDER BY RoyaltyReportBase.[Vendor ID],
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand, IIf(RoyaltyReportBase!Brand="Santa Cruz","Santa
Cruz Skate",RoyaltyReportBase!Brand), RoyaltyReportBase.Category;
 
Hi Phil,

I think you have encountered a bug. I can replicate the problem in
Access 2007. It is the use of the reference to the ENDDATE on the
Sales_Reports_Form form. Oddly, if you use the TransferText macro command or
the VBA DoCmd.TransferText, it works fine! If you need to create an export
specification, use a copy of the query with an actual date instead of the
reference to the ENDDATE on the form. It should then let you do it manually.

Hope that helps,

Clifford Bass
 
Nope. Follows is the modified query. I do indeed ned to make the
export specification, but I get the same error with the following:

SELECT RoyaltyReportBase.[Vendor ID] AS [Supplier#],
DatePart("m",#1/1/2009#) & "-" & Year(#1/1/2009#) & " Royalty-" &
[RoyaltyReportBase]![Brand] AS [Invoice ID], 8205 AS Account,
IIf(RoyaltyReportBase!Division="Apparel","Skate",RoyaltyReportBase!Division)
AS Division, "MKTG" AS Department, RoyaltyReportBase.Category,
IIf(RoyaltyReportBase!Brand="Santa Cruz","Santa Cruz
Skate",RoyaltyReportBase!Brand) AS Brand, DatePart("m",#1/1/2009#) & "-"
& Year(#1/1/2009#) & " Royalty-" & [RoyaltyReportBase]![Brand] AS
[Memo], "" AS Project, Sum(RoyaltyReportBase.Royalty) AS Amount
FROM RoyaltyReportBase
GROUP BY RoyaltyReportBase.[Vendor ID], DatePart("m",#1/1/2009#) & "-" &
Year(#1/1/2009#) & " Royalty-" & [RoyaltyReportBase]![Brand], 8205,
IIf(RoyaltyReportBase!Division="Apparel","Skate",RoyaltyReportBase!Division),
"MKTG", RoyaltyReportBase.Category, IIf(RoyaltyReportBase!Brand="Santa
Cruz","Santa Cruz Skate",RoyaltyReportBase!Brand),
DatePart("m",#1/1/2009#) & "-" & Year(#1/1/2009#) & " Royalty-" &
[RoyaltyReportBase]![Brand], ""
ORDER BY RoyaltyReportBase.[Vendor ID], DatePart("m",#1/1/2009#) & "-" &
Year(#1/1/2009#) & " Royalty-" & [RoyaltyReportBase]![Brand],
IIf(RoyaltyReportBase!Brand="Santa Cruz","Santa Cruz
Skate",RoyaltyReportBase!Brand), RoyaltyReportBase.Category;






Clifford said:
Hi Phil,

I think you have encountered a bug. I can replicate the problem in
Access 2007. It is the use of the reference to the ENDDATE on the
Sales_Reports_Form form. Oddly, if you use the TransferText macro command or
the VBA DoCmd.TransferText, it works fine! If you need to create an export
specification, use a copy of the query with an actual date instead of the
reference to the ENDDATE on the form. It should then let you do it manually.

Hope that helps,

Clifford Bass

:

Access 2003.

Right now I am simply going to
File|Export|, Save as type: Textfiles, hit export, get error.
If I choose Excel instead, it works fine.


Query Follows;


SELECT RoyaltyReportBase.[Vendor ID] AS [Supplier#],
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand AS [Invoice ID], 8205 AS Account,
IIf(RoyaltyReportBase!Division="Apparel","Skate",RoyaltyReportBase!Division)
AS Division, "MKTG" AS Department, RoyaltyReportBase.Category,
IIf(RoyaltyReportBase!Brand="Santa Cruz","Santa Cruz
Skate",RoyaltyReportBase!Brand) AS Brand,
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand AS [Memo], "" AS Project,
Sum(RoyaltyReportBase.Royalty) AS Amount
FROM RoyaltyReportBase
GROUP BY RoyaltyReportBase.[Vendor ID],
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand, 8205,
IIf(RoyaltyReportBase!Division="Apparel","Skate",RoyaltyReportBase!Division),
"MKTG", RoyaltyReportBase.Category, IIf(RoyaltyReportBase!Brand="Santa
Cruz","Santa Cruz Skate",RoyaltyReportBase!Brand),
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand, ""
ORDER BY RoyaltyReportBase.[Vendor ID],
DatePart("m",Forms!Sales_Reports_Form!ENDDATE) & "-" &
Year(Forms!Sales_Reports_Form!ENDDATE) & " Royalty-" &
RoyaltyReportBase!Brand, IIf(RoyaltyReportBase!Brand="Santa Cruz","Santa
Cruz Skate",RoyaltyReportBase!Brand), RoyaltyReportBase.Category;
Click to expand...
Click to expand...
 
Hi Phil,

There still must be something in there that it is viewing as a
parameter. Is RoyaltyReportBase a table or a query? If a query, what is its
SQL? While I don't think it will make a difference, try changing all of your
exclamation points to periods. Meanwhile I can putter with it see if
anything becomes obvious.

Clifford Bass
 
Hi Phil,

That exports manually just fine for me with RoyaltyReportBase set up as
a table from both 2003 and 2007.

Clifford Bass
 
I know - unacceptable workaround, but if it's more important to have
this work than anything else, you could write the query results to a
table and export that... then just delete the contents of the temp
table before you run the append.

Requires a bit of McGuyvering, but that's life sometimes...
 
It too is a query, and follows. The problem is, why wouldn't it need
those parameters to export as an Excel file?


SELECT SalesCombinedLicenseeSum.labels, RoyaltyMap.[Vendor Name],
brand.name AS Brand, category.name AS Category, division.name AS
Division, item.short_desc, item.old_sku, item.item_id,
SalesCombinedLicenseeSum.Ext_price, SalesCombinedLicenseeSum.ship_qty,
IIf(RoyaltyMap![Primary Percentage]=0,"",RoyaltyMap![Primary
Percentage]/RoyaltyMap![Split BY Number]) AS Percentage,
RoyaltyMap.minimum, RoyaltyMap.[Split BY Number],
IIf(RoyaltyMap!Domestic<=0 Or RoyaltyMap!Domestic Is
Null,"",SalesCombinedLicenseeSum!DomesticUnits) AS [Domestic Units],
IIf(RoyaltyMap!Foreign<=0 Or RoyaltyMap!Foreign Is
Null,"",SalesCombinedLicenseeSum!ForeignUnits) AS [Foreign Units],
IIf(SalesCombinedLicenseeSum!Ext_price>RoyaltyMap!minimum,(SalesCombinedLicenseeSum!Ext_price-RoyaltyMap!minimum)*(RoyaltyMap![Primary
Percentage]/100/RoyaltyMap![Split BY
Number]),0)+(SalesCombinedLicenseeSum!ForeignUnits*RoyaltyMap!Foreign)+(RoyaltyMap!Domestic*SalesCombinedLicenseeSum!DomesticUnits)
AS Royalty, RoyaltyMap.[Vendor ID]
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (item
INNER JOIN (RoyaltyMap INNER JOIN SalesCombinedLicenseeSum ON
(RoyaltyMap.[LICENSEE CODE] = SalesCombinedLicenseeSum.labels) AND
(RoyaltyMap.[Item Type] = SalesCombinedLicenseeSum.name)) ON
item.item_id = SalesCombinedLicenseeSum.item_id) ON division.division_id
= item.division_id) ON brand.brand_id = item.brand_id) ON
category.category_id = item.master_category_id
WHERE (((RoyaltyMap.[Pool?])=No));
 
Hi Phil,

If you have not tried it yet, can you export this query as a text file?
If not, are any of its source tables queries? If so, do any of those use
conditions from your form? If so, temporarily replace those references with
actual data and try again.

As to why it works fine exporting to an Excel file; my guess is that
the code to do the one is completely different from the code to do the other.
Maybe even written by different programmmers. But that is only a guess. It
could be considered a bug. It is also odd that it does work when done
through code!

Let us know if that helps,

Clifford Bass
 
OK. It gets kind of ugly. Below that query is a union query. a step
or two below that is another union query. I think I can see what the
problem might be. I took a single piece of the lowest Union query, and
received the same error, so it is definitely part of the problem.

That query has "name" from two different tables. In design view I see
name and name, when I run it it converts these to type.name and brand.name.

I will go through the pain of fixing this if it is reasonable that this
is the problem. There are about 18 different queries that revolve
around this base query, and all will have to be modified.

You think that this is the problem?





Clifford said:
Hi Phil,

If you have not tried it yet, can you export this query as a text file?
If not, are any of its source tables queries? If so, do any of those use
conditions from your form? If so, temporarily replace those references with
actual data and try again.

As to why it works fine exporting to an Excel file; my guess is that
the code to do the one is completely different from the code to do the other.
Maybe even written by different programmmers. But that is only a guess. It
could be considered a bug. It is also odd that it does work when done
through code!

Let us know if that helps,

Clifford Bass

:

It too is a query, and follows. The problem is, why wouldn't it need
those parameters to export as an Excel file?


SELECT SalesCombinedLicenseeSum.labels, RoyaltyMap.[Vendor Name],
brand.name AS Brand, category.name AS Category, division.name AS
Division, item.short_desc, item.old_sku, item.item_id,
SalesCombinedLicenseeSum.Ext_price, SalesCombinedLicenseeSum.ship_qty,
IIf(RoyaltyMap![Primary Percentage]=0,"",RoyaltyMap![Primary
Percentage]/RoyaltyMap![Split BY Number]) AS Percentage,
RoyaltyMap.minimum, RoyaltyMap.[Split BY Number],
IIf(RoyaltyMap!Domestic<=0 Or RoyaltyMap!Domestic Is
Null,"",SalesCombinedLicenseeSum!DomesticUnits) AS [Domestic Units],
IIf(RoyaltyMap!Foreign<=0 Or RoyaltyMap!Foreign Is
Null,"",SalesCombinedLicenseeSum!ForeignUnits) AS [Foreign Units],
IIf(SalesCombinedLicenseeSum!Ext_price>RoyaltyMap!minimum,(SalesCombinedLicenseeSum!Ext_price-RoyaltyMap!minimum)*(RoyaltyMap![Primary
Percentage]/100/RoyaltyMap![Split BY
Number]),0)+(SalesCombinedLicenseeSum!ForeignUnits*RoyaltyMap!Foreign)+(RoyaltyMap!Domestic*SalesCombinedLicenseeSum!DomesticUnits)
AS Royalty, RoyaltyMap.[Vendor ID]
FROM category INNER JOIN (brand INNER JOIN (division INNER JOIN (item
INNER JOIN (RoyaltyMap INNER JOIN SalesCombinedLicenseeSum ON
(RoyaltyMap.[LICENSEE CODE] = SalesCombinedLicenseeSum.labels) AND
(RoyaltyMap.[Item Type] = SalesCombinedLicenseeSum.name)) ON
item.item_id = SalesCombinedLicenseeSum.item_id) ON division.division_id
= item.division_id) ON brand.brand_id = item.brand_id) ON
category.category_id = item.master_category_id
WHERE (((RoyaltyMap.[Pool?])=No));
Click to expand...
Click to expand...
 
Hi Phil,

Sorry for the delay. Microsoft's Discussion Group message notification
is or was not working. Have you solved the issue? If not, let me know.

One thought, if I understand your information correctly, is that there
are a couple of places that use "name" as a column name. This can be the
problem; best to not use that if possible as it is a reserved word and as
such can cause Access confusion and you unexpected results. If you are stuck
with "name" as the column from a table that cannot be changed, try aliasing
it in any initial queries that use it: select Name as Brand_Name, ....
Also, don't use periods in the alias.

Clifford Bass
 
I have not solved it per say, it has been back burnered. Your "name"
comment is well taken. It seems I will have to spend some time rewriting
queries and renaming fields. Oh Well.

Thanx
 
Hi Phil,

You are welcome.

Do a search in Access's online help for "reserved words" to get
information on other word to avoid.

Good luck on it when you get back to it!

Clifford Bass
 
Back
Top