Until SQL Server 2012, IIf() wasn't valid. I was happily surprised to see it now! I don't think Switch() is supported but the CASE statement would suffice.
> To:
MS_Access_Professionals@yahoogroups.com> From:
MS_Access_Professionals@yahoogroups.com> Date: Mon, 14 Jul 2014 16:54:00 +0100
> Subject: 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]
>
>
>
> ------------------------------------
> Posted by: "Bob Phillips" <
bob.phillips@dsl.pipex.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
> <*> To visit your group on the web, go to:
>
http://groups.yahoo.com/group/MS_Access_Professionals/>
> <*> Your email settings:
> Individual Email | Traditional
>
> <*> To change settings online go to:
>
http://groups.yahoo.com/group/MS_Access_Professionals/join> (Yahoo! ID required)
>
> <*> To change settings via email:
>
MS_Access_Professionals-digest@yahoogroups.com >
MS_Access_Professionals-fullfeatured@yahoogroups.com>
> <*> To unsubscribe from this group, send an email to:
>
MS_Access_Professionals-unsubscribe@yahoogroups.com>
> <*> Your use of Yahoo Groups is subject to:
>
https://info.yahoo.com/legal/us/yahoo/utos/terms/>
Tidak ada komentar:
Posting Komentar