Code to determine query

[Gilberto]

Hello,

I have two forms (ENGLISH AND FRENCH) each with buttons that open reports in the language of the form. The only difference in the reports is that their query returns data from different fields.

In this sense i have.

TblCommodity Names with
commodityID
commodityenglis h
commodityfrench

What code do i need to redirect the query taking in consideration the name of the form where the user opens the reports???

Thnks.

[nico5038]

Better to create a French and English query and use that for the forms.
Creating a query depending on the name of a form is "tricky" as a change of name ruins the query...

Nic;o)

[Gilberto]

Better to create a French and English query and use that for the forms.
Creating a query depending on the name of a form is "tricky" as a change of name ruins the query...

Nic;o)

Thanks Nico, that sounds better, can you elaborate a bit more. I thought that queries were just for reports.

Gilberto

[nico5038]

Queries and tables are more or less "the same" as both extract data from the database. Tables are "elementary " and queries add the possibility to extract data (SELECT) from multiple tables, but also allow the selection of a subset of fields from one table.
Both reports and forms can be linked ("bound") to any table or query to extract their data.

Nic;o)

[Gilberto]

Thnks nico. I just still dont understand how to define the query so that on user "response" it opens the adequate report (ENGLISH report or FRENCH report).

This is my exact situation. I have a FRENCH report whose query reads:

SELECT Engineering.Commodity, 
nz(Sum(IIf([Engineering].[Seating]="front",(Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
nz(Sum(IIf([Engineering].[Seating]="rear",( Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
FROM Engineering
WHERE Engineering.Variant1=-1
GROUP BY Engineering.Commodity
ORDER BY Engineering.Commodity
UNION SELECT "Total" AS Commodity, 
nz(Sum(IIf([Engineering].[Seating]="front", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
nz(Sum(IIf([Engineering].[Seating]="rear", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
FROM Engineering
WHERE Engineering.Variant1=-1;

and a modyfied query that brings ENGLISH names instead of french ones like this:

SELECT [commodity name].CommodityEnglish,
nz(Sum(IIf([Engineering].[Seating]="front",(Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
nz(Sum(IIf([Engineering].[Seating]="rear",( Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
FROM Engineering INNER JOIN [COMMODITY NAME] ON (Engineering.Commodity = [COMMODITY NAME].Commodity )  
WHERE Engineering.Variant1=-1
GROUP BY [commodity name].CommodityEnglish
ORDER BY [commodity name].CommodityEnglish
UNION SELECT "Total" AS CommodityEnglish,
nz(Sum(IIf([Engineering].[Seating]="front", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS FRONT,
nz(Sum(IIf([Engineering].[Seating]="rear", (Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+ Engineering![Level 1 Rear]),0)),0) AS REAR,
nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
FROM Engineering
WHERE Engineering.Variant1=-1;

All i need is that without having to create duplicate REPORTS, someway the user can indicate if he wants the report in ENGLISH (which will follow the second query) or in FRENCH (which will follow the first query).

How can i do this???

Thanks again,
Gilberto

[nico5038]

You'll need to store the needed language somewhere.
Let's assume you've created a form to start the report named "frmReports ".
Now place a combobox named "cmbLanguag e" on the form holding "English" and "Francais".

Now use your second query from the revious statement and change the first field like:

SELECT IIF(Forms!frmReports!cmbLanguage="English",[commodity name].CommodityEnglish,Engineering.Commodity), .....

Getting the idea ?

Nic;o)