ORA-00936: missing expression

[eyesonthebal]

Hi, Everyone:

I'm currently trying to write VBA code for a macro in Excel that makes use of some SQL, but for some reason, I'm getting the message, "ORA-00936: missing expression", when I execute the macro. Please see below for the portion of the code involving my SQL code. In particular, the very last line of code generates the error. IDField, IDFieldAlias, MonthIndex_p, YearIndex_p, Group_p, Charge, NextMonthIndex and NextYearIndex are all defined variables (either strings or integers) in my code. This SQL code works in Access, but not in VBA, which I've noticed happens somewhat often in practice. Any help with identifying the problem with the syntax in the below code would be greatly appreciated. If there are any questions, please feel free to ask.
___________________________________________________________________________ ___________________________________________________________________________ ___________________________________
SQLCode = "SELECT ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR, ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR, RIGHT([ELIG.PERSON_1.SSN],4) AS SSN, ELIG.ENROLL_IN_FAMILY." & IDField & " AS " & IDFieldAlias & ", " & _
"ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR AS [ALTUS INTERNAL ID], ELIG.PERSON.FNAME, ELIG.PERSON.LNAME, ELIG.ENROLL_IN_FAMILY.REL_CODE, '" & Charge & "' AS CHARGE, " & _
"ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE, ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE FROM ((ELIG.ENROLLMENT_EPISODE_DEF_V INNER JOIN ELIG.ENROLL_IN_FAMILY ON " & _
"(ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR = ELIG.ENROLL_IN_FAMILY.ENROLL_PERS_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR = ELIG.ENROLL_IN_FAMILY.GRP_NBR) AND " & _
"(ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR = ELIG.ENROLL_IN_FAMILY.DIV_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.SUB_PERS_NBR = ELIG.ENROLL_IN_FAMILY.SUB_PERS_NBR)) INNER JOIN " & _
"ELIG.PERSON ON ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR = ELIG.PERSON.PERS_NBR) INNER JOIN ELIG.PERSON AS ELIG.PERSON_1 ON ELIG.ENROLLMENT_EPISODE_DEF_V.SUB_PERS_NBR = " & _
"ELIG.PERSON_1.PERS_NBR WHERE (((DateDiff('yyyy',[ELIG.PERSON.DOB],'" & MonthIndex_p & "/1/" & YearIndex_p & "') + Int(Format('" & MonthIndex_p & "/1/" & YearIndex_p & "','mmdd')<" & _
"Format([ELIG.PERSON.DOB],'mmdd'))) < 19)) GROUP BY ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR, ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR, RIGHT([ELIG.PERSON_1.SSN],4), " & _
"ELIG.ENROLL_IN_FAMILY." & IDField & ", ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR, ELIG.PERSON.FNAME, ELIG.PERSON.LNAME, ELIG.ENROLL_IN_FAMILY.REL_CODE, '" & Charge & "', " & _
"ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE, ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE HAVING (((ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR) In (" & Group_p & ")) AND " & _
"((ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE)< TO_DATE('" & NextMonthIndex & "/1/" & NextYearIndex & "','MM-DD-YYYY')) AND ((ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE)>" & _
"TO_DATE('" & MonthIndex_p & "/1/" & YearIndex_p & "','MM-DD-YYYY')))"

Set Records = New ADODB.Recordset
Records.Open SQLCode, Conn_p, adOpenStatic, adLockReadOnly

___________________________________________________________________________ ___________________________________________________________________________ ___________________________________

Thank you all, and happy holidays!

Best,
David

[SamT]

For analysis purposes I broke down the entire "String" into lines to be concatenated. Paragraph breaks replaced Line Continuation characters (" _")

The VBAExpress Post editor auto-wordwraps long lines of text. Lines not preceded by an Ampersand are wordwrapped.

Note that all Concatenation lines that are not enclosed in Quotes must be valid VBA variables
Some other possible errors. And it may be that certain space characters are missing or dupicated.

"SELECT ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR, ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR, RIGHT([ELIG.PERSON_1.SSN],4) AS SSN, ELIG.ENROLL_IN_FAMILY."
& IDField
& " AS "
& IDFieldAlias
& ", "


& "ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR AS [ALTUS INTERNAL ID], ELIG.PERSON.FNAME, ELIG.PERSON.LNAME, ELIG.ENROLL_IN_FAMILY.REL_CODE, '"
& Charge
& "' AS CHARGE, "


& "ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE, ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE FROM ((ELIG.ENROLLMENT_EPISODE_DEF_V INNER JOIN ELIG.ENROLL_IN_FAMILY ON "


& "(ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR = ELIG.ENROLL_IN_FAMILY.ENROLL_PERS_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR = ELIG.ENROLL_IN_FAMILY.GRP_NBR) AND "


& "(ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR = ELIG.ENROLL_IN_FAMILY.DIV_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.SUB_PERS_NBR = ELIG.ENROLL_IN_FAMILY.SUB_PERS_NBR)) INNER JOIN "


& "ELIG.PERSON ON ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR = ELIG.PERSON.PERS_NBR) INNER JOIN ELIG.PERSON AS ELIG.PERSON_1 ON ELIG.ENROLLMENT_EPISODE_DEF_V.SUB_PERS_NBR = "


& "ELIG.PERSON_1.PERS_NBR WHERE (((DateDiff('yyyy',[ELIG.PERSON.DOB],'"
& MonthIndex_p
& "/1/"
& YearIndex_p
& "') + Int(Format('"
& MonthIndex_p
& "/1/"
& YearIndex_p
& "','mmdd')<"


& "Format([ELIG.PERSON.DOB],'mmdd'))) < 19)) GROUP BY ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR, ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR, RIGHT([ELIG.PERSON_1.SSN],4), "


&"ELIG.ENROLL_IN_FAMILY."
& IDField
& ", ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR, ELIG.PERSON.FNAME, ELIG.PERSON.LNAME, ELIG.ENROLL_IN_FAMILY.REL_CODE, '"
& Charge
& "', "


& "ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE, ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE HAVING (((ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR) In ("
& Group_p
& ")) AND "


& "((ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE)< TO_DATE('"
& NextMonthIndex
& "/1/"
& NextYearIndex
& "','MM-DD-YYYY')) AND ((ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE)>"


& "TO_DATE('"
& MonthIndex_p
& "/1/"
& YearIndex_p
& "','MM-DD-YYYY')))"

I would suggest that you use certain SQL terms as indicators of where to start Concatenation strings
& ", AND..."
& ", Where...
& ", >....
& ", format...Use your imagination. Also VBA ignores empty white space so you can
& "' And..." _


& ", Where..."

[eyesonthebal]

I apologize for the messy post; I was rushing to send it out so I could leave work and run some last minute X-mas errands. The SQL portion of my VBA code is equivalent to the following string after removing continuation characters and separating the different segments of the code:

SELECT ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR, ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR, RIGHT([ELIG.PERSON_1.SSN],4) AS SSN, ELIG.ENROLL_IN_FAMILY." & IDField & " AS " & IDFieldAlias & ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR AS [ALTUS INTERNAL ID], ELIG.PERSON.FNAME, ELIG.PERSON.LNAME, ELIG.ENROLL_IN_FAMILY.REL_CODE, '" & Charge & "' AS CHARGE, ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE, ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE

FROM ((ELIG.ENROLLMENT_EPISODE_DEF_V INNER JOIN ELIG.ENROLL_IN_FAMILY ON (ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR = ELIG.ENROLL_IN_FAMILY.ENROLL_PERS_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR = ELIG.ENROLL_IN_FAMILY.GRP_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR = ELIG.ENROLL_IN_FAMILY.DIV_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.SUB_PERS_NBR = ELIG.ENROLL_IN_FAMILY.SUB_PERS_NBR)) INNER JOIN ELIG.PERSON ON ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR = ELIG.PERSON.PERS_NBR) INNER JOIN ELIG.PERSON AS ELIG.PERSON_1 ON ELIG.ENROLLMENT_EPISODE_DEF_V.SUB_PERS_NBR = ELIG.PERSON_1.PERS_NBR

WHERE (((DateDiff('yyyy',[ELIG.PERSON.DOB],'" & MonthIndex_p & "/1/" & YearIndex_p & "') + Int(Format('" & MonthIndex_p & "/1/" & YearIndex_p & "','mmdd')< Format([ELIG.PERSON.DOB],'mmdd'))) < 19))

GROUP BY ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR, ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR, RIGHT([ELIG.PERSON_1.SSN],4), ELIG.ENROLL_IN_FAMILY." & IDField & ", ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR, ELIG.PERSON.FNAME, ELIG.PERSON.LNAME, ELIG.ENROLL_IN_FAMILY.REL_CODE, '" & Charge & "', ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE, ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE

HAVING (((ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR) In (" & Group_p & ")) AND ((ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE)< TO_DATE('" & NextMonthIndex & "/1/" & NextYearIndex & "','MM-DD-YYYY')) AND ((ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE)>TO_DATE('" & MonthIndex_p & "/1/" & YearIndex_p & "','MM-DD-YYYY')))"

Does anyone see anything wrong with the syntax here? I simply copied and pasted the code generated by Access after I created the query in design view. The only changes I made were replacing some of the expressions with variables and replacing underscores with periods between the schemas and tables, as I've noticed that this is necessary if the code is to be executed correctly in VBA. I'm not at work at the moment so I can't mess around with the code, but I'm guessing it might be possible that, for example, the fact that "AS [ALTUS INTERNAL ID]" and "AS CHARGE" are both present in the code could be a cause of the error, considering the discrepancy in the code's syntax. Other than that, I'm not sure what else could be causing the problem. Additional/missing parentheses? If anyone sees something wrong with this code, please let me know!! Thanks!

[SamT]

WHERE (((DateDiff('yyyy',[ELIG.PERSON.DOB],'" & MonthIndex_p & "/1/" & YearIndex_p & "') + Int(Format('" & MonthIndex_p & "/1/" & YearIndex_p & "','mmdd')< Format([ELIG.PERSON.DOB],'mmdd'))) < 19))

Where Year(DOB - RefDate) + Int(MonthDay string of Ref Date) is less than (MonthDay String of DOB) is less than 19 :dunno

(Excel VBA DateDiff subtracts second from first)

Excel VBA for IF <19yo on Date

If DateAdd("yyyy", 19, DOB) < DateHTH

[eyesonthebal]

I replaced that formula with a new formula and somewhat simplified the code, but I'm still having problems :(. Now, the message, "ORA-00905: missing keyword", comes up. My new SQL code is as follows, after removing continuation characters and separating the different segments of code:

"SELECT ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR, ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR, RIGHT(ELIG.PERSON_1.SSN, 4) AS SSN, ELIG.ENROLL_IN_FAMILY." & IDField & " AS " & IDFieldAlias & ", ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR AS ALTUS_ID, ELIG.PERSON.FNAME, ELIG.PERSON.LNAME, ELIG.ENROLL_IN_FAMILY.REL_CODE, ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE, ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE

FROM ((ELIG.ENROLLMENT_EPISODE_DEF_V INNER JOIN ELIG.ENROLL_IN_FAMILY ON (ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR = ELIG.ENROLL_IN_FAMILY.ENROLL_PERS_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR = ELIG.ENROLL_IN_FAMILY.GRP_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR = ELIG.ENROLL_IN_FAMILY.DIV_NBR) AND (ELIG.ENROLLMENT_EPISODE_DEF_V.SUB_PERS_NBR = ELIG.ENROLL_IN_FAMILY.SUB_PERS_NBR)) INNER JOIN ELIG.PERSON ON ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR = ELIG.PERSON.PERS_NBR) INNER JOIN ELIG.PERSON AS ELIG.PERSON_1 ON ELIG.ENROLLMENT_EPISODE_DEF_V.SUB_PERS_NBR = ELIG.PERSON_1.PERS_NBR

WHERE (INT((TO_DATE('" & MonthIndex_p & "/1/" & YearIndex_p & "','MM-DD-YYYY') - ELIG.PERSON.DOB)/365.25) < 19)

GROUP BY ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR, ELIG.ENROLLMENT_EPISODE_DEF_V.DIV_NBR, RIGHT(ELIG.PERSON_1.SSN, 4), ELIG.ENROLL_IN_FAMILY." & IDField & ", ELIG.ENROLLMENT_EPISODE_DEF_V.DEP_PERS_NBR, ELIG.PERSON.FNAME, ELIG.PERSON.LNAME, ELIG.ENROLL_IN_FAMILY.REL_CODE, ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE, ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE

HAVING (((ELIG.ENROLLMENT_EPISODE_DEF_V.GRP_NBR) In (" & Group_p & ")) AND ((ELIG.ENROLLMENT_EPISODE_DEF_V.EFF_DATE) < TO_DATE('" & NextMonthIndex & "/1/" & NextYearIndex & "','MM-DD-YYYY')) AND ((ELIG.ENROLLMENT_EPISODE_DEF_V.LIM_DATE) > TO_DATE('" & MonthIndex_p & "/1/" & YearIndex_p & "','MM-DD-YYYY')))"

Someoooooooone pleaaaaaaase helpppppppppppppp!!

[SamT]

In order to keep the white space required for proper analysis, I saved to a text file and attached it. Note that we cannot upload *.txt files, so I renamed it to a *.zip file.

I notice that the string is 1632 characters (+ spaces) long. This might be a problem.

I suggest that you use several String variables then concatenate then into the final Query.


Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strGroupBy As String
Dim strFinal As String

strFinal = strSelect & strFrom & strWhere & strGroupBy


This can also make it easier to design and TS the String(s)