Select Statement includies a reserved word or argument error...(long explanation)

[mtampa]

Hi guys,

First off, thanks for all your help. I have no real training in VBA but I'm the only one at my office who sorta find his way through it from time to time working on these older reports I inherited.

I was able to successfully modify my Access database to add a new column in my AS400 query.

I was running a get macro to find ceiling and sold and now I'm looking for options. I was able to successfully set up my database and grab the desired information correctly, now I'm trying to modify the Excel report.

Normally, I've been successful simply looking at the code and adding what I need, but this time I'm stuck. I added OPTION, but continue to get the same error. Here is my code: (my error line is bolded)

Sub ADOImportFromAccessbyCols(DBFullName As String, _
sStr As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
' open the recordset
'.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
' all records
.Open sStr, cn, , , adCmdText
' filter records

'RS2WS rs, TargetRange ' write data from the recordset to the worksheet

' ' optional approach for Excel 2000 or later (RS2WS is not necessary)
'For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
' TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
'Next
TargetRange.Offset(0, 0).CopyFromRecordset rs ' the recordset data

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


Sub GetData2(sReadDate As String)

sTable = "(select distinct ceil, option, sold, dReadDate, dDeparture,category,village_code, occ from tblAvailClean)"

'If Sheets("Main").Range("M24") <> vba.Date Then
'sTable = "tblAvailCleanHist"
'End If


Dim sStr As String
Dim n As Range
Dim dlDate As Date
Dim dsDate As Date

Dim wsName As String

Dim wkSht As Worksheet

wsName = Application.ActiveSheet.Name

sDateString = "dateserial(" & VBA.Mid(sReadDate, 7, 4) & "," & VBA.Mid(sReadDate, 1, 2) & "," & _
VBA.Mid(sReadDate, 4, 2) & ")"



'loop all cats

'If (wsName <> "CARC") Then
'If (wsName <> "SANC") Then
For Each n In ActiveSheet.Range("CAPA").Cells



sVcode = n.Parent.Name
sCat = VBA.Trim(Cells(2, n.Column))

sStr = "SELECT sum(ceil*1) AS CEIL, sum(sold*1)AS SOLD, sum(option*1) AS OPTION FROM " & sTable & _
" WHERE dReadDate=" & sDateString & " and dDeparture between dateserial(2012,10,27) and dateserial(2013,05,04) " & _
"and village_code='" & sVcode & "' and category='" & sCat & "' group by dDeparture order by dDeparture"


ADOImportFromAccessbyCols "\\cgassv0001\revenue\DSS\DBs\Inventory\Copy of NaInv_W13.mdb", sStr, n


Next n
'End If
' End If

On Error Resume Next
For Each n In ActiveSheet.Range("CAPANEW").Cells



sVcode = n.Parent.Name
sCat = VBA.Trim(Cells(2, n.Column))

sStr = "SELECT sum(ceil*1) AS CEIL, sum(sold*1) AS SOLD, sum(option*1) AS OPTION FROM " & sTable & _
" WHERE dReadDate=" & sDateString & " and dDeparture between dateserial(2012,10,27) and dateserial(2012,05,04) " & _
"and village_code='" & sVcode & "' and category='" & sCat & "' group by dDeparture order by dDeparture"


ADOImportFromAccessbyCols "\\cgassv0001\revenue\DSS\DBs\Inventory\Copy of NaInv_W13.mdb", sStr, n

Next n
On Error GoTo 0

End Sub



What exactly is it getting upset at? Everything is spelled correctly...the punctuatin is the same as it was previously. I'm totally lost.

 

[The_Barman]

OPTION is a reserved word in SQL. So you should not use this in the statement AS OPTION
It may accept sum([option]*1) AS sOPTION

 

[taklecker]

I'm no VBA expert but it seems to me the problem is that the term 'option' is a reserved word in VBA.

I see from the other terms and general gist of the script that you are looking to use the term 'option' in the sense of a stock option or other option purchase price or date as variable name.

If you were to use another variable name like "STOCKOPTION", "PURCHOPTION", "OPTIONPRICE" or "OPTIONDATE" might resolve the problem.

 

[mtampa]

OPTION is a reserved word in SQL. So you should not use this in the statement AS OPTION
It may accept sum([option]*1) AS sOPTION

Thanks man! I would have never figured that out!

 

[mtampa]

OK guys, now that I've gotten everything to populate, I'm trying to figure out how to do math within the VBA. For me, options are INCLUDED within sold, so I would want my sold formula to be the value of the SOLD column to be SOLD - OPTION. This way I would get a "true" sold value.

So Ceil would remain the same, sold would be Sold - Option and Option would remain the same.

I believe this is the code the guy who built this was using to import into Excel from Access is as follows. There used to be a functionality that would query all of the different occupancy levels by category, but I figured out how to trim this out to save time. It was a step that collected data, but was never used.

Also, is this a step I should be doing in Excel, or would it be easier to do it in the Access Table before importing to Excel?

Sub Macro1()


sTemplate = "T:\dss\xlTemplates\SubSheets\NA Inventory OccLevel W13"
sVillage = ActiveSheet.Name
sCat = ActiveCell
iyear = VBA.Year(Sheets("Main").Range("M24"))
imonth = VBA.Month(Sheets("Main").Range("M24"))
iday = VBA.Day(Sheets("Main").Range("M24"))

sTable = "tblAvailCleanHist"
If Sheets("Main").Range("M24") = VBA.Date Then
sTable = "tblAvailClean"
End If

Call CreateSheet(sTemplate, sVillage, sCat, iyear, imonth, iday, sTable)



End Sub


Sub CreateSheet(ByVal sTemplate As String, ByVal sVillage As String, _
ByVal sCat As String, ByVal iyear As Integer, _
ByVal imonth As Integer, ByVal iday As Integer, ByVal sTable As String)
'
'Create Workbook based on template
'

Dim oOcc As Workbook
Dim oDest As Range
Dim oDestLoop As Range
Dim sOccStr As String


Set oOcc = Workbooks.Add(Template:=sTemplate)
Set oDest = oOcc.Sheets(1).Range("F4")

sDateString = "dateserial(" & iyear & "," & imonth & "," & iday & ")"
sDated = DateSerial(iyear, imonth, iday)
oOcc.Sheets(1).Range("A5") = sVillage
oOcc.Sheets(1).Range("B5") = sCat
oOcc.Sheets(1).Range("C5") = sDated

' reggie note sStr was from occ_commands_unq
sStr = "SELECT max(Occ) as iMax from cmds where village="
sStr = sStr & "'" & sVillage & "' and category='" & sCat & "'"

Imax = ADO_getMinMax("\\cgassv0001\revenue\DSS\DBs\Inventory\Copy of NaInv_W13.mdb", sStr)

'this patch is to make up for the new Cats in W11
sStartDate = "dateserial(2010,10,30)"
iOffset = 0




For iOcc = 1 To Imax



Set oDestLoop = oDest.Offset(0, 3 * (iOcc - 1))

oDestLoop.Offset(0, 0) = "CAPA"
oDestLoop.Offset(0, 1) = "SOLD"
oDestLoop.Offset(0, 2) = "OPTION"
oDestLoop.Offset(0, 3) = "AVAIL"

oDestLoop.Offset(1, 0) = iOcc
Range(oDestLoop.Offset(1, 0), oDestLoop.Offset(1, 2)).Merge

Next iOcc


sOccStr = sOccStr & "SELECT "
sOccStr = sOccStr & " sum(iif(occ=1,ceil,0)) ,sum(iif(occ=1,sold,0)),sum(iif(occ=1,ceil-sold,0)) "
For iOcc = 2 To Imax

sOccStrs = sOccStrs & ", sum(iif(occ=" & iOcc & ",ceil,0)) , " & _
"sum(iif(occ=" & iOcc & ",sold,0)) , " & _
"sum(iif(occ=" & iOcc & ",ceil-sold,0)) "

Next iOcc
sOccStr = sOccStr & sOccStrs
sOccStr = sOccStr & " FROM " & sTable & _
" WHERE dReadDate=" & sDateString & " and dDeparture between " & sStartDate & " and dateserial(2013,05,04) " & _
"and village_code='" & sVillage & "' and category='" & sCat & "' group by dDeparture order by dDeparture"


'MsgBox sOccStr
ADOImportFromAccessbyCols "\\cgassv0001\revenue\DSS\DBs\Inventory\Copy of NaInv_W13.mdb", sOccStr, oDest.Offset(2 + iOffset, 0)

sOccStr = ""
sOccStr = sOccStr & "SELECT "
sOccStr = sOccStr & " sum(ceil) ,sum(sold),sum(ceil-sold) "

sOccStr = sOccStr & " FROM " & sTable & _
" WHERE dReadDate=" & sDateString & " and dDeparture between " & sStartDate & " and dateserial(2013,05,04) " & _
"and village_code='" & sVillage & "' and category='" & sCat & "' group by dDeparture order by dDeparture"


'MsgBox sOccStr




Dim oTotals As Range

Set oDestLoop = oDest.Offset(0, 3 * (Imax))

oDestLoop.Offset(0, 0) = "CAPA"
oDestLoop.Offset(0, 1) = "SOLD"
oDestLoop.Offset(0, 2) = "OPTION"
oDestLoop.Offset(0, 3) = "AVAIL"

oDestLoop.Offset(1, 0) = "Total"
Range(oDestLoop.Offset(1, 0), oDestLoop.Offset(1, 2)).Merge

Set oTotals = oDest.Offset(2 + iOffset, 3 * (Imax))



ADOImportFromAccessbyCols "\\cgassv0001\revenue\DSS\DBs\Inventory\Copy of NaInv_W13.mdb", sOccStr, oTotals




End Sub

Function ADO_getMinMax(DBFullName As String, ByVal sStr As String) As Integer

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer


' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset


With rs
.Open sStr, cn, , , adCmdText
End With

Imax = rs.Fields("iMax")

' the recordset data

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing


ADO_getMinMax = Imax

End Function


Sub treaddate()

iyear = VBA.Year(Date)
imonth = VBA.Month(Date)
iday = VBA.Day(Date)

MsgBox DateSerial(iyear, imonth, iday)


End Sub


Sub hide()

Application.ScreenUpdating = False

For Each cell In Range("HIDDEN")
If (VBA.UCase(cell.Value) = "CAPA" Or VBA.UCase(cell.Value) = "SOLD" Or VBA.UCase(cell.Value) = "OPTION") And VBA.UCase(cell.Offset(-1, 0)) <> "TOTAL" Then
cell.EntireColumn.Hidden = Not (cell.EntireColumn.Hidden)
End If
Next cell

Rows("3:6").EntireRow.Hidden = Not (Rows("3:6").EntireRow.Hidden)

Application.ScreenUpdating = True
End Sub


Sub hide2()

Range("hidden").EntireColumn.Hidden = Not (Range("hidden").EntireColumn.Hidden)


End Sub