Ms Query Parameters in Excel 2007

[Salut39]

Hi Guys,

I have Query in Ms Access 2003 which has parameters set up for Exchange Rates to convert Currency amounts to GBP. I anayse it in Excel 2007 via Ms Query and have a bit of a problem.

Query run fine in Access, when I link to Excel it comes with Error: "Too few parameter. Expected 2" When I remove parameters I can't put them back with Ms Query it says I have Syntax Error.

IIf([Currency 2]="EUR",[EUR Rate],IIf([Currency 2]="USD",[USD Rate],1))

Any idea how to make it work?

Cheers
Yuri

 

[SkipVought]

hi,

Please post the entire SQL that you are using in MS Query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

SELECT `Billing Cube Query 1`.ICPN, `Billing Cube Query 1`.`Customer No`, `Billing Cube Query 1`.`Customer Name`, `Billing Cube Query 1`.Country, `Billing Cube Query 1`.Region, `Billing Cube Query 1`.`Bill date`, `Billing Cube Query 1`.`Display Artist Name`, `Billing Cube Query 1`.Title, `Billing Cube Query 1`.`MEA Company Name`, `Billing Cube Query 1`.`International Release Date`, `Billing Cube Query 1`.`Item Type`, `Billing Cube Query 1`.`Currency 2`, `Billing Cube Query 1`.`Exchange Rates`, `Billing Cube Query 1`.Quantity, `Billing Cube Query 1`.`Gross Sales`, `Billing Cube Query 1`.`Net Sales`, `Billing Cube Query 1`.`Royalty / MEA held in TP`, `Billing Cube Query 1`.`MTP Exclude Royalty`, `Billing Cube Query 1`.`PPS Fee`, `Billing Cube Query 1`.`Acetate Cost`, `Billing Cube Query 1`.`Gross Sales £`, `Billing Cube Query 1`.`Net Sales £`
FROM `Billing Cube Query 1` `Billing Cube Query 1`
ORDER BY `Billing Cube Query 1`.`Display Artist Name`

 

[SkipVought]

???

You mention "When I remove parameters I can't put them back with Ms Query " WHAT PARAMETERS?

Where is your IIF()???

I would say filter the query for each rate plug it in. HOWEVER, in the MS Query QBE grid interface, you are very limited in what you can do with parameters. You are limited to a simple Where x=?

I'd use VBA, which I often do.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

It's in my Ms Access Query:

SELECT [02 SAP Billing Report].ICPN, [02 Customer list].[Customer No], [02 Customer list].[Customer Name], [02 Customer list].Country, [02 Customer list].Region, [02 SAP Billing Report].[Bill date], [02 Merch Output].[Display Artist Name], [02 Merch Output].Title, [02 Merch Output].[MEA Company Name], [02 Merch Output].[International Release Date], [02 MTP details].[Item Type], [02 SAP Billing Report].[Currency 2], IIf([Currency 2]="EUR",[EUR Rate],IIf([Currency 2]="USD",[USD Rate],1)) AS [Exchange Rates], [02 SAP Billing Report].Quantity, [02 SAP Billing Report].[Gross Sales], Round([Gross Sales]/[Exchange Rates],2) AS [Gross Sales £], [02 SAP Billing Report].[Net Sales], Round([Net Sales]/[Exchange Rates],2) AS [Net Sales £], [02 MTP details].[Royalty / MEA held in TP], [02 MTP details].[MTP Exclude Royalty], [02 MTP details].[Acetate Cost], [02 MTP details].[PPS Fee]
FROM (([02 SAP Billing Report] INNER JOIN [02 MTP details] ON [02 SAP Billing Report].ICPN = [02 MTP details].ICPN) INNER JOIN [02 Merch Output] ON [02 MTP details].ICPN = [02 Merch Output].ICPN) INNER JOIN [02 Customer list] ON [02 SAP Billing Report].Payer = [02 Customer list].[Customer No];

 

[SkipVought]

So are you saying that your Access Query is named `Billing Cube Query 1`?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

Yes

 

[SkipVought]

Well then you cannot run the FIRST query that you posted, from Excel. You must run the SECOND query and supply the two parameter values. As I stated previously, you may have to use VBA to supply the parameters rather than MS Query user interface.

Try MS Query and see if the parameters will work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

BTW is there the way to go directly to Query in Access and skip Ms Query. Pardon the pun.

 

[SkipVought]

MS Query IS query!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

I can always do final calculations in Excel.
Thanks Have a good weekend!

 

[SkipVought]

In VBA forum707, via ADO you can do this.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}