Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    280

    Unanswered: Invalid SQL statement?

    I'm trying to link to an Access query by using ADO.

    Here is my code:

    Dim conndb as ADODB.Connection
    Dim rsqryCAPAmount as ADODB.Recordset

    Set conndb = CurrentProject.Connection
    Set rsqryCAPAmount = New ADODB.Recordset

    rsqryCAPAmount.Open "qryCAPAmount", conndb <-------causing the error

    the error is:
    Run-time error '-2147217900(80040e14)
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    Here is my Access query:
    SELECT IIf(IsNull([Middle Initial]),Left([Last Name],1)+LCase(Mid(RTrim([Last Name]),2))+","+Left([First Name],1)+LCase(Mid(RTrim([First Name]),2)),(Left([Last Name],1)+LCase(Mid(RTrim([Last Name]),2))+","+Left([First Name],1)+LCase(Mid(RTrim([First Name]),2))+" "+[Middle Initial])) AS FullName, DatePart("m",[Date From]) AS MonthFrom, DatePart("m",[Date To]) AS MonthTo, DatePart("yyyy",[Date From]) AS YearFrom, DatePart("yyyy",[Date To]) AS YearTo, Sum(IIf(([Modifier 1]="OT"),[Charge A]*[Units],0)) AS OTAmount, Sum(IIf(([Modifier 1]="PT"),[Charge A]*[Units],0)) AS PTAmount, Sum(IIf(([Modifier 1]="ST"),[Charge A]*[Units],0)) AS STAmount, Month.MonthText, (Nz([PTAmount])+Nz([STAmount])) AS PTSTAmount, MWINS.Name, MWADD.Name AS FacilityName, MWADD.Code

    FROM [Month], MWADD INNER JOIN (MWINS INNER JOIN ((mwcas INNER JOIN (mwtrn INNER JOIN LAProCode ON mwtrn.[Procedure Code] = LAProCode.[Code 1]) ON mwcas.[Case Number] = mwtrn.[Case Number]) INNER JOIN mwpat ON mwtrn.[Chart Number] = mwpat.[Chart Number]) ON MWINS.Code = mwcas.[Insurance Carrier #1]) ON MWADD.Code = mwcas.Facility

    GROUP BY IIf(IsNull([Middle Initial]),Left([Last Name],1)+LCase(Mid(RTrim([Last Name]),2))+","+Left([First Name],1)+LCase(Mid(RTrim([First Name]),2)),(Left([Last Name],1)+LCase(Mid(RTrim([Last Name]),2))+","+Left([First Name],1)+LCase(Mid(RTrim([First Name]),2))+" "+[Middle Initial])), DatePart("m",[Date From]), DatePart("m",[Date To]), DatePart("yyyy",[Date From]), DatePart("yyyy",[Date To]), Month.MonthText, MWINS.Name, MWADD.Name, MWADD.Code, MWINS.Code, Month.MonthValue

    HAVING (((DatePart("m",[Date From]))=[Forms]![frmCapAmount]![cboMonth]) AND ((DatePart("m",[Date To]))=[Forms]![frmCapAmount]![cboMonth]) AND ((DatePart("yyyy",[Date From]))=[Forms]![frmCapAmount]![cboYear]) AND ((DatePart("yyyy",[Date To]))=[Forms]![frmCapAmount]![cboYear]) AND ((MWADD.Code) Like [Forms]![frmCAPAmount]![lstFacilityCode]) AND ((MWINS.Code)="MCARE") AND ((Month.MonthValue)=[Forms]![frmCAPAmount]![cboMonth]));

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Invalid SQL statement?

    Originally posted by lansing
    I'm trying to link to an Access query by using ADO.

    Here is my code:

    Dim conndb as ADODB.Connection
    Dim rsqryCAPAmount as ADODB.Recordset

    Set conndb = CurrentProject.Connection
    Set rsqryCAPAmount = New ADODB.Recordset

    rsqryCAPAmount.Open "qryCAPAmount", conndb <-------causing the error

    the error is:
    Run-time error '-2147217900(80040e14)
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    Here is my Access query:
    SELECT IIf(IsNull([Middle Initial]),Left([Last Name],1)+LCase(Mid(RTrim([Last Name]),2))+","+Left([First Name],1)+LCase(Mid(RTrim([First Name]),2)),(Left([Last Name],1)+LCase(Mid(RTrim([Last Name]),2))+","+Left([First Name],1)+LCase(Mid(RTrim([First Name]),2))+" "+[Middle Initial])) AS FullName, DatePart("m",[Date From]) AS MonthFrom, DatePart("m",[Date To]) AS MonthTo, DatePart("yyyy",[Date From]) AS YearFrom, DatePart("yyyy",[Date To]) AS YearTo, Sum(IIf(([Modifier 1]="OT"),[Charge A]*[Units],0)) AS OTAmount, Sum(IIf(([Modifier 1]="PT"),[Charge A]*[Units],0)) AS PTAmount, Sum(IIf(([Modifier 1]="ST"),[Charge A]*[Units],0)) AS STAmount, Month.MonthText, (Nz([PTAmount])+Nz([STAmount])) AS PTSTAmount, MWINS.Name, MWADD.Name AS FacilityName, MWADD.Code

    FROM [Month], MWADD INNER JOIN (MWINS INNER JOIN ((mwcas INNER JOIN (mwtrn INNER JOIN LAProCode ON mwtrn.[Procedure Code] = LAProCode.[Code 1]) ON mwcas.[Case Number] = mwtrn.[Case Number]) INNER JOIN mwpat ON mwtrn.[Chart Number] = mwpat.[Chart Number]) ON MWINS.Code = mwcas.[Insurance Carrier #1]) ON MWADD.Code = mwcas.Facility

    GROUP BY IIf(IsNull([Middle Initial]),Left([Last Name],1)+LCase(Mid(RTrim([Last Name]),2))+","+Left([First Name],1)+LCase(Mid(RTrim([First Name]),2)),(Left([Last Name],1)+LCase(Mid(RTrim([Last Name]),2))+","+Left([First Name],1)+LCase(Mid(RTrim([First Name]),2))+" "+[Middle Initial])), DatePart("m",[Date From]), DatePart("m",[Date To]), DatePart("yyyy",[Date From]), DatePart("yyyy",[Date To]), Month.MonthText, MWINS.Name, MWADD.Name, MWADD.Code, MWINS.Code, Month.MonthValue

    HAVING (((DatePart("m",[Date From]))=[Forms]![frmCapAmount]![cboMonth]) AND ((DatePart("m",[Date To]))=[Forms]![frmCapAmount]![cboMonth]) AND ((DatePart("yyyy",[Date From]))=[Forms]![frmCapAmount]![cboYear]) AND ((DatePart("yyyy",[Date To]))=[Forms]![frmCapAmount]![cboYear]) AND ((MWADD.Code) Like [Forms]![frmCAPAmount]![lstFacilityCode]) AND ((MWINS.Code)="MCARE") AND ((Month.MonthValue)=[Forms]![frmCAPAmount]![cboMonth]));
    I just tried it using your code with my own query and it worked fine although my query was not quite as intense as yours.

    Have you opened the query itself instead of a recordset and does this work?

    Gregg

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    Yeah, i have try to a different query also and it work. Maybe my query is to complicated or something but it work when just click on it manually.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •