Bob
For the life of me, I've never seen that happen. Just for kicks I did a count on [param].[property].[id] and found 64 instances! And I see everything got bracketed.
Maybe if you declare all your parameters that won't happen. But I really don't have an explanation for you.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com
---In MS_Access_Professionals@yahoogroups.com, <bob.phillips@dsl.pipex.com> wrote:
This is a bit of an odd one, but here goes.
I have this Access query.
SELECT reg.RegionID, reg.Region, IIF(ISNULL(Round(Sum(UpsellRevenue
*Switch(param.report.ccy="EUR",xr.EURRate
,param.report.ccy="USD",xr.USDRate
,param.report.ccy="GBP",xr.GBPRate)
)
,2)),""
,Round(Sum(UpsellRevenue
*Switch(param.report.ccy="EUR",xr.EURRate
,param.report.ccy="USD",xr.USDRate
,param.report.ccy="GBP",xr.GBPRate)
)
,2)) AS TotalRevenue
FROM ExchangeRates AS xr, (((Region AS reg INNER JOIN ((Country AS cty INNER JOIN Property AS prop ON cty.CountryID = prop.CountryID) INNER JOIN Tracking AS trk ON prop.PropertyID = trk.PropertyID) ON reg.RegionID = cty.RegionID) INNER JOIN Area AS area ON prop.AreaID = area.AreaID) INNER JOIN Brand AS brnd ON prop.BrandID = brnd.BrandID) INNER JOIN Contract AS con ON prop.ContractID = con.ContractID
WHERE (((trk.TrackingYear)=[param].[year])) AND
(trk.DepartureDate >= param.start.date AND trk.DepartureDate <= param.end.date) AND
(prop.PropertyID=param.property.id OR param.property.id=99) AND
(cty.CountryID=param.country.code OR param.country.code="zzz") AND
(reg.RegionID=param.region.id OR param.region.id="zzz") AND
(area.AreaID=param.area.id OR param.area.id=99) AND
(brnd.BrandID=param.brand.id OR param.brand.id=99) AND
(con.ContractID=param.contract.id OR param.contract.id=99) AND
xr.DateOfRate=DateSerial(Year(trk.DepartureDate),Month(trk.DepartureDate),1)
GROUP BY reg.RegionID, reg.Region;
If I go to design view, I find that one of the tables is hidden behind another, so I move the top one, nothing more than just moving it in the relationship poane, go back to the SQL view, and the code is changed to the below. What the heck is going on here?
SELECT reg.RegionID, reg.Region, IIf(IsNull(Round(Sum(UpsellRevenue*Switch(param.report.ccy="EUR",xr.EURRate,param.report.ccy="USD",xr.USDRate,param.report.ccy="GBP",xr.GBPRate)),2)),"",Round(Sum(UpsellRevenue*Switch(param.report.ccy="EUR",xr.EURRate,param.report.ccy="USD",xr.USDRate,param.report.ccy="GBP",xr.GBPRate)),2)) AS TotalRevenue
FROM ExchangeRates AS xr, Region AS reg INNER JOIN ((Country AS cty INNER JOIN (Brand AS brnd INNER JOIN (Area INNER JOIN (Property AS prop INNER JOIN Contract AS con ON prop.ContractID = con.ContractID) ON area.AreaID = prop.AreaID) ON brnd.BrandID = prop.BrandID) ON cty.CountryID = prop.CountryID) INNER JOIN Tracking AS trk ON prop.PropertyID = trk.PropertyID) ON reg.RegionID = cty.RegionID
WHERE (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1))) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz")) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz")) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((con.ContractID)=[param].[contract].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((brnd.BrandID)=[param].[brand].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((area.AreaID)=[param].[area].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((reg.RegionID)=[param].[region].[id]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((cty.CountryID)=[param].[country].[code]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((prop.PropertyID)=[param].[property].[id]) AND ((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99)) OR (((trk.TrackingYear)=[param].[year]) AND ((trk.DepartureDate)>=[param].[start].[date] And (trk.DepartureDate)<=[param].[end].[date]) AND ((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)) AND (([param].[property].[id])=99) AND (([param].[country].[code])="zzz") AND (([param].[region].[id])="zzz") AND (([param].[area].[id])=99) AND (([param].[brand].[id])=99) AND (([param].[contract].[id])=99))
GROUP BY reg.RegionID, reg.Region;
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar