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

    Unanswered: How do you run a custom query..

    that has a criteria in it?

    Here is my code:

    Dim conndb as Adodb.Connection
    Dim rsApplyPay as Adodb.Recordset

    Set rsApplyPay = New Adodb.Recordset

    rsApplypay.Open "qryApplyPay", conndb, adOpenKeyset, adLockPessimistic

    The Error:
    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    I have run a custom query before with vba but never with a query that has a criteria in it. What is my problem?

    Thank you in advance..

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    what is "qryApplyPay"?

    I assume that's an access object?

    I also notice that you haven't intialized your connection in that code... what are you hitting through ado?

    Anyways, I don't believe you can use access objects as criteria for an external ado recordset. You will most likely have to explicitly define a static sql statement when making the call.

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    Yes, it is an access object. I have tried hard coding in the query into VBA but not luck with it for some reason. I have done harding coding query before but I have no luck with this one for some reason..arghh. I'm getting rusty..

  4. #4
    Join Date
    Apr 2003
    Posts
    280
    qryApplyPay:

    SELECT mwtrn.[Chart Number], mwtrn.[Case Number], mwcas.[Insurance Carrier #1], DatePart('m',[Date From]) AS MonthFrom, DatePart('m',[Date To]) AS MonthTo, DatePart('yyyy',[Date From]) AS YearFrom, DatePart('yyyy',[Date To]) AS YearTo, mwtrn.Amount, mwtrn.[Date From], mwtrn.[Date To], mwtrn.[Insurance 1 Paid], mwtrn.[Insurance 1 Amount Paid]

    FROM mwtrn INNER JOIN mwcas ON mwtrn.[Case Number] = mwcas.[Case Number]

    GROUP BY mwtrn.[Chart Number], mwtrn.[Case Number], mwcas.[Insurance Carrier #1], DatePart('m',[Date From]), DatePart('m',[Date To]), DatePart('yyyy',[Date From]), DatePart('yyyy',[Date To]), mwtrn.Amount, mwtrn.[Date From], mwtrn.[Date To], mwtrn.[Insurance 1 Paid], mwtrn.[Insurance 1 Amount Paid]

    HAVING (((mwcas.[Insurance Carrier #1]) Like [Forms]![frmApplyPayments]![lstFacilityID]) AND ((DatePart('m',[Date From])) Like [Forms]![frmApplyPayments]![cboMonth]) AND ((DatePart('m',[Date To])) Like [Forms]![frmApplyPayments]![cboMonth]) AND ((DatePart('yyyy',[Date From])) Like [Forms]![frmApplyPayments]![cboYear]) AND ((DatePart('yyyy',[Date To])) Like [Forms]![frmApplyPayments]![cboYear]));

Posting Permissions

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