By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Code to determine query

100+
P: 135
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
commodityenglish
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.
Oct 11 '07 #1
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 11 '07 #2

100+
P: 135
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
Oct 12 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 12 '07 #4

100+
P: 135
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Engineering.Commodity, 
  2. 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,
  3. 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,
  4. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  5. FROM Engineering
  6. WHERE Engineering.Variant1=-1
  7. GROUP BY Engineering.Commodity
  8. ORDER BY Engineering.Commodity
  9. UNION SELECT "Total" AS Commodity, 
  10. 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,
  11. 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,
  12. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  13. FROM Engineering
  14. WHERE Engineering.Variant1=-1;
and a modyfied query that brings ENGLISH names instead of french ones like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT [commodity name].CommodityEnglish,
  2. 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,
  3. 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,
  4. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  5. FROM Engineering INNER JOIN [COMMODITY NAME] ON (Engineering.Commodity = [COMMODITY NAME].Commodity )  
  6. WHERE Engineering.Variant1=-1
  7. GROUP BY [commodity name].CommodityEnglish
  8. ORDER BY [commodity name].CommodityEnglish
  9. UNION SELECT "Total" AS CommodityEnglish,
  10. 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,
  11. 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,
  12. nz(Sum((Engineering![Weight]/1000)* (Engineering![Level 1 Driver]+Engineering![Level 1 Passenger]+Engineering![Level 1 Rear])),0) AS MIX
  13. FROM Engineering
  14. 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
Oct 15 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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 "cmbLanguage" on the form holding "English" and "Francais".

Now use your second query from the revious statement and change the first field like:
Expand|Select|Wrap|Line Numbers
  1. SELECT IIF(Forms!frmReports!cmbLanguage="English",[commodity name].CommodityEnglish,Engineering.Commodity), .....
  2.  
Getting the idea ?

Nic;o)
Oct 15 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.