SQL Problem: reserved word

F

faberk

I am trying to programically insert this sql into a report's record source,
but I the "enter parameter value: currency" dialogue appears. The sql works
fine in a query. Currency is a reserved SQL word. Is this my problem? If
it is, how do i overcome it when the field name is "Currency". If it isnt,
what is the problem? Thanks in advance.
 
F

faberk

Al,

Thanks for your response. I have aliased the currency field, but am still
getting the dialogue. SQL below:

strSQL = "TRANSFORM Sum(tblNormData.AmountCAD) AS SumOfAmountCAD " _
& "SELECT tblSubRegions.SubRegionDescr,
tblRegions.RegionDescr, tblGrpMain.GrpMainDescr, tblMarketAreas.MktArea,
tblMarketAreas.MktAreaDescr, tblProductGroup.Description,
tblGrpSub.GrpSubDescr, tblGrpSub.GrpSubSort,
IIf([grpsubdescr]=""Units"","""",tblNormData.Currency) AS Curr " _
& "FROM (tblRegions INNER JOIN tblSubRegions ON
tblRegions.RegionId = tblSubRegions.RegionId) INNER JOIN ((tblGrpMain INNER
JOIN tblGrpSub ON tblGrpMain.GrpMain = tblGrpSub.GrpMainAssoc) INNER JOIN
(((tblGrpChart INNER JOIN tblNormData ON tblGrpChart.GrpAll =
tblNormData.TypeValue) INNER JOIN tblMarketAreas ON tblNormData.MktId =
tblMarketAreas.MktId) INNER JOIN tblProductGroup ON tblNormData.ProdId =
tblProductGroup.ProdId) ON tblGrpSub.GrpSub = tblGrpChart.GrpSubAssoc) ON
tblSubRegions.SubRegionId = tblMarketAreas.SubRegionId " _
& "WHERE (((tblRegions.RegionId) =" & Me.cboRegions & ") And
((tblSubRegions.SubRegionId) =" & Me.lstSubregions & ") And
((tblGrpSub.GrpSub) <> 3) And ((tblNormData.AmountCAD) <> 0)) " _
& "GROUP BY tblSubRegions.SubRegionDescr,
tblRegions.RegionDescr, tblGrpMain.GrpMainDescr, tblMarketAreas.MktArea,
tblMarketAreas.MktAreaDescr, tblProductGroup.Description,
tblGrpSub.GrpSubDescr, tblGrpSub.GrpSubSort,
IIf([grpsubdescr]=""Units"","""",tblNormData.Currency) " _
& "ORDER BY tblRegions.RegionDescr, tblGrpSub.GrpSubSort " _
& "PIVOT tblNormData.Month In
(""01"",""02"",""03"",""04"",""05"",""06"",""07"",""08"",""09"",""10"",""11"",""12"");"
 
O

Ofer

If you copy this SQl to a query and you run it, and you get no error message
then the problem is not with the SQl, its with the report

Check if you have a field bounded to currency.
Check the grouping and sorting if you have currency there.



faberk said:
Al,

Thanks for your response. I have aliased the currency field, but am still
getting the dialogue. SQL below:

strSQL = "TRANSFORM Sum(tblNormData.AmountCAD) AS SumOfAmountCAD " _
& "SELECT tblSubRegions.SubRegionDescr,
tblRegions.RegionDescr, tblGrpMain.GrpMainDescr, tblMarketAreas.MktArea,
tblMarketAreas.MktAreaDescr, tblProductGroup.Description,
tblGrpSub.GrpSubDescr, tblGrpSub.GrpSubSort,
IIf([grpsubdescr]=""Units"","""",tblNormData.Currency) AS Curr " _
& "FROM (tblRegions INNER JOIN tblSubRegions ON
tblRegions.RegionId = tblSubRegions.RegionId) INNER JOIN ((tblGrpMain INNER
JOIN tblGrpSub ON tblGrpMain.GrpMain = tblGrpSub.GrpMainAssoc) INNER JOIN
(((tblGrpChart INNER JOIN tblNormData ON tblGrpChart.GrpAll =
tblNormData.TypeValue) INNER JOIN tblMarketAreas ON tblNormData.MktId =
tblMarketAreas.MktId) INNER JOIN tblProductGroup ON tblNormData.ProdId =
tblProductGroup.ProdId) ON tblGrpSub.GrpSub = tblGrpChart.GrpSubAssoc) ON
tblSubRegions.SubRegionId = tblMarketAreas.SubRegionId " _
& "WHERE (((tblRegions.RegionId) =" & Me.cboRegions & ") And
((tblSubRegions.SubRegionId) =" & Me.lstSubregions & ") And
((tblGrpSub.GrpSub) <> 3) And ((tblNormData.AmountCAD) <> 0)) " _
& "GROUP BY tblSubRegions.SubRegionDescr,
tblRegions.RegionDescr, tblGrpMain.GrpMainDescr, tblMarketAreas.MktArea,
tblMarketAreas.MktAreaDescr, tblProductGroup.Description,
tblGrpSub.GrpSubDescr, tblGrpSub.GrpSubSort,
IIf([grpsubdescr]=""Units"","""",tblNormData.Currency) " _
& "ORDER BY tblRegions.RegionDescr, tblGrpSub.GrpSubSort " _
& "PIVOT tblNormData.Month In
(""01"",""02"",""03"",""04"",""05"",""06"",""07"",""08"",""09"",""10"",""11"",""12"");"



Al Camp said:
Access is telling you what the problem is...
Rename the Currency field to something like CurrencyAmt, and change it
wherever it's called out in any queries, form, and reports.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Click to expand...
Click to expand...
 
D

Douglas J Steele

If you can't rename the field, try surrounding its name in square brackets:
[currency]
 
F

faberk

Thanks Ofer.

Looking right at it. Are brain cramps hereditary???



Ofer said:
If you copy this SQl to a query and you run it, and you get no error message
then the problem is not with the SQl, its with the report

Check if you have a field bounded to currency.
Check the grouping and sorting if you have currency there.



faberk said:
Al,

Thanks for your response. I have aliased the currency field, but am still
getting the dialogue. SQL below:

strSQL = "TRANSFORM Sum(tblNormData.AmountCAD) AS SumOfAmountCAD " _
& "SELECT tblSubRegions.SubRegionDescr,
tblRegions.RegionDescr, tblGrpMain.GrpMainDescr, tblMarketAreas.MktArea,
tblMarketAreas.MktAreaDescr, tblProductGroup.Description,
tblGrpSub.GrpSubDescr, tblGrpSub.GrpSubSort,
IIf([grpsubdescr]=""Units"","""",tblNormData.Currency) AS Curr " _
& "FROM (tblRegions INNER JOIN tblSubRegions ON
tblRegions.RegionId = tblSubRegions.RegionId) INNER JOIN ((tblGrpMain INNER
JOIN tblGrpSub ON tblGrpMain.GrpMain = tblGrpSub.GrpMainAssoc) INNER JOIN
(((tblGrpChart INNER JOIN tblNormData ON tblGrpChart.GrpAll =
tblNormData.TypeValue) INNER JOIN tblMarketAreas ON tblNormData.MktId =
tblMarketAreas.MktId) INNER JOIN tblProductGroup ON tblNormData.ProdId =
tblProductGroup.ProdId) ON tblGrpSub.GrpSub = tblGrpChart.GrpSubAssoc) ON
tblSubRegions.SubRegionId = tblMarketAreas.SubRegionId " _
& "WHERE (((tblRegions.RegionId) =" & Me.cboRegions & ") And
((tblSubRegions.SubRegionId) =" & Me.lstSubregions & ") And
((tblGrpSub.GrpSub) <> 3) And ((tblNormData.AmountCAD) <> 0)) " _
& "GROUP BY tblSubRegions.SubRegionDescr,
tblRegions.RegionDescr, tblGrpMain.GrpMainDescr, tblMarketAreas.MktArea,
tblMarketAreas.MktAreaDescr, tblProductGroup.Description,
tblGrpSub.GrpSubDescr, tblGrpSub.GrpSubSort,
IIf([grpsubdescr]=""Units"","""",tblNormData.Currency) " _
& "ORDER BY tblRegions.RegionDescr, tblGrpSub.GrpSubSort " _
& "PIVOT tblNormData.Month In
(""01"",""02"",""03"",""04"",""05"",""06"",""07"",""08"",""09"",""10"",""11"",""12"");"



Al Camp said:
Access is telling you what the problem is...
Rename the Currency field to something like CurrencyAmt, and change it
wherever it's called out in any queries, form, and reports.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am trying to programically insert this sql into a report's record source,
but I the "enter parameter value: currency" dialogue appears. The sql
works
fine in a query. Currency is a reserved SQL word. Is this my problem?
If
it is, how do i overcome it when the field name is "Currency". If it
isnt,
what is the problem? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Top