ACCESS FEED: RE: [MS_AccessPros] Syntax Error in TRANSFORM

Senin, 14 Juli 2014

RE: [MS_AccessPros] Syntax Error in TRANSFORM

That is not what I was referring to, rather that NZ is not valid in T-SQL,
which is what I usually use, so I avoid it.

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 14 July 2014 15:29
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Syntax Error in TRANSFORM

Bob-

NZ should map to ISNULL when passed to SQL Server by ODBC from Access. Does
it not?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 14, 2014, at 9:42 AM, 'Bob Phillips' bob.phillips@dsl.pipex.com
<mailto:bob.phillips@dsl.pipex.com> [MS_Access_Professionals]
<MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com> > wrote:

Thanks John. Such a silly mistake, my only excuse is that it occurred with
my copy-paste form my joins query, and I wasn't looking closely enough as it
said the error was in the TRANSFORM.

Area does need to be joined, I missed it when I created a simple query to
setup my joins for me, easily rectified.

I will decline the NZ function, SQL Server doesn't support it and so I avoid
it.

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 14 July 2014 13:15
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Syntax Error in TRANSFORM

Bob-

First of all, I would replace:

TRANSFORM 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 CrossValue

With:

NZ(Round(Sum(UpsellRevenue * Switch(param.report.ccy="EUR", xr.EURRate,
param.report.ccy="USD", xr.USDRate,
param.report.ccy="GBP",xr.GBPRate) ), 2), "") AS CrossValue

And it this part:

FROM Area AS area, ((Region AS reg INNER JOIN (Contract AS con INNER JOIN
(Country AS cty INNER JOIN (Brand AS brnd INNER JOIN Property AS prop ON
brnd.BrandID = prop.BrandID) ON cty.CountryID = prop.CountryID) ON
con.ContractID = prop.ContractID) ON reg.RegionID = cty.RegionID) INNER JOIN
(ExchangeRates AS xr INNER JOIN Tracking AS trk ON xr.Currency =
trk.CurrencyCode) ON prop.PropertyID = trk.PropertyID) INNER JOIN Division
AS div ON prop.DivisionID = div.DivisionID;
WHERE .

Get rid of the semi-colon just before WHERE. A semi-colon tells SQL that's
the end of the query.

Are you SURE you want the Cartesian Product with Area? I see no JOIN
criteria for the Area table.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 14, 2014, at 5:29 AM, 'Bob Phillips' bob.phillips@dsl.pipex.com
<mailto:bob.phillips@dsl.pipex.com> [MS_Access_Professionals]
<MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals@yahoogroups.com> > wrote:

Can anyone see why the following code is giving me a syntax error in the
TRANSFORM statement, as it looks fine to me?

A bit of background. The query was running perfectly well, with that same
TRANSFORM statement. I then needed to add a field from another table,
Division. I couldn't go into design view and just add the table and field
there as that screws up my SQL completely (cast your minds back to my
previous post about this, caused by Access 'optimising' my code), I have to
do it in SQL view. As I am totally confused by how Access setsup the joins,
where it decides to add brackets and where it decides to include the ONs, I
created a simple query to just include all of my tables and bring back the
data. I then copy-pasted the FROM statement in that query to my existing
query, added the extra parameter and the test for that parameter value. When
I try to save the query I get the syntax error in part of the code I haven't
touched.

The query is below, followed by the original from statement, the one in the
query that works.

PARAMETERS param.[year] Long, param.report.ccy Text ( 3 ), param.property.id
Long, param.country.code Text ( 3 ), param.division.id Long, param.region.id
Text ( 5 ), param.area.id Long, param.brand.id Long, param.contract.id Long,
param.start.[date] DateTime, param.[end].[date] DateTime;

TRANSFORM 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 CrossValue

SELECT prop.PropertyID, prop.PropertyName, '', cty.Country, reg.Region,
area.Area, brnd.Brand, con.Contract

FROM Area AS area, ((Region AS reg INNER JOIN (Contract AS con INNER JOIN
(Country AS cty INNER JOIN (Brand AS brnd INNER JOIN Property AS prop ON
brnd.BrandID = prop.BrandID) ON cty.CountryID = prop.CountryID) ON
con.ContractID = prop.ContractID) ON reg.RegionID = cty.RegionID) INNER JOIN
(ExchangeRates AS xr INNER JOIN Tracking AS trk ON xr.Currency =
trk.CurrencyCode) ON prop.PropertyID = trk.PropertyID) INNER JOIN Division
AS div ON prop.DivisionID = div.DivisionID;

WHERE trk.TrackingYear=param.year And

(prop.PropertyId=param.property.id OR
param.property.id=99) AND

(cty.CountryID=param.country.code Or
param.country.code="zzz") And

(div.DivisionID = param.division.id Or param.division.id = 99) 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 (trk.DepartureDate>=param.start.date AND
trk.DepartureDate<=param.end.date) AND

xr.DateOfRate=DateSerial(Year(trk.DepartureDate)

,Month(trk.DepartureDate)

,1)

GROUP BY prop.PropertyID, prop.PropertyName, cty.Country, div.Division,
reg.Region, area.Area, brnd.Brand, con.Contract

PIVOT Format([trk.DepartureDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

FROM ExchangeRates AS xr INNER JOIN (Region AS reg INNER JOIN ((Country AS
cty INNER JOIN (Brand AS brnd INNER JOIN (Area INNER JOIN (Contract AS con
INNER JOIN Property AS prop ON con.ContractID = prop.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) ON xr.Currency =
trk.CurrencyCode

[Non-text portions of this message have been removed]