Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With SQL Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The following works OK

    <pre>strSQL = "SELECT Count(curVBen) AS NumberOfRecords, " & _
    "Sum(curVBen) AS TotalBenefit, " & _
    "Sum(curVFee) AS TotalFee, " & _
    "Sum(curAmtAllowed) AS TotalAllowed, " & _
    "Sum(curAmtApplToDed) AS ApplToDed, " & _
    "Sum(curAmtNotCovered) AS NotCovered " _
    & "FROM " & strTable & " " _
    & "WHERE (((" & strTable & ".dteVDATE) " _
    & "BETWEEN #" & gloGetValue("StartDate") & "# " _
    & "AND #" & gloGetValue("EndDate") & "#) " _
    & "AND ((" & strTable & "." & strCode & ") " _
    & "BETWEEN '" & gloGetValue("StartProcedureGroupCode") & "' " _
    & "AND '" & gloGetValue("EndProcedureGroupCode") & "') " _
    & "AND ((" & strTable & ".idsKeyOfPersonal)=" _
    & PersonalID & "))"
    </pre>


    I’m trying to streamline the following code in a form and I’m getting and I am getting “3265 Item not found in collection” error

    <pre>strSQL = "SELECT Count(curVBen) AS NumberOfRecords, " & _
    "Sum(curVBen) AS TotalBenefit, " & _
    "Sum(curAmtAllowed) AS TotalAllowed " _
    & "FROM " & strTable & " " _
    & "WHERE (((" & strTable & "." & strCode & ") " _
    & "= '" & gloGetValue("StartProcedureGroupCode") & "' " _
    & "AND ((" & strTable & ".idsKeyOfPersonal)=" _
    & PersonalID & ")))"

    Debug.Print strSQL
    </pre>


    However, I can run the the contents of strSQL in query grid OK

    <pre>SELECT Count(curVBen) AS NumberOfRecords,
    Sum(curVBen) AS TotalBenefit,
    Sum(curAmtAllowed) AS TotalAllowed
    FROM qrytblSumServetblVouch2_NoVoids
    WHERE (((qrytblSumServetblVouch2_NoVoids.strProcedureGro up) = 'O'
    AND ((qrytblSumServetblVouch2_NoVoids.idsKeyOfPersonal )=1632)))
    </pre>


    Where is my problem?

    Thanks, John

  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With SQL Syntax? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I retyped it and it works

    <pre>strSQL = "SELECT Count(curVBen) AS NumberOfRecords, " & _
    "Sum(curVFee) AS TotalFee, " & _
    "Sum(curAmtAllowed) AS TotalAllowed " _
    & "FROM " & strTable & " " _
    & "WHERE (((" & strTable & "." & strCode & ") " _
    & "= '" & gloGetValue("StartProcedureGroupCode") & "' " _
    & "AND ((" & strTable & ".idsKeyOfPersonal)=" _
    & PersonalID & ")))"
    </pre>


Posting Permissions

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