1. Forum
  2. Usenet
  3. COMP.DATABASES.MS-ACCESS

  • SQL

    From Ray Pearson@21:1/5 to All on Fri May 21 16:59:13 2021
    I have used the sql statement in Access 97 but now I am using 2016 and I keep getting the old "not enough parameters". I have check everything and all seems well. can anyone throw any light on the problem. I'm new to SQL

    TIA - Ray

    strSql = "SELECT FarNorth.JPID, FarNorth.FirstName, FarNorth.Surname, FarNorth.Email, FarNorth.Status, Sub2.SubID, Sub2.Year, Sub2.AmountPaid" & _
    "FROM FarNorth INNER JOIN Sub2 ON FarNorth.JPID = Sub2.SubID " & _
    "WHERE (((FarNorth.Status)='Active') AND ((Sub2.Year)=2022) AND ((Sub2.AmountPaid) Is Null) AND ((Len([FarNorth].[Email]))>0));"

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ron Weiner@21:1/5 to All on Fri May 21 21:33:37 2021
    Ray Pearson presented the following explanation :
    I have used the sql statement in Access 97 but now I am using 2016 and I keep getting the old "not enough parameters". I have check everything and all seems well. can anyone throw any light on the problem. I'm new to SQL

    TIA - Ray

    strSql = "SELECT FarNorth.JPID, FarNorth.FirstName, FarNorth.Surname, FarNorth.Email, FarNorth.Status, Sub2.SubID, Sub2.Year, Sub2.AmountPaid" & _
    "FROM FarNorth INNER JOIN Sub2 ON FarNorth.JPID = Sub2.SubID " & _
    "WHERE (((FarNorth.Status)='Active') AND ((Sub2.Year)=2022) AND ((Sub2.AmountPaid) Is Null) AND ((Len([FarNorth].[Email]))>0));"

    I do not see anything obviously wrong with the Sql you have listed, but
    we cannot see what columns are in the tables FarNorth and Sub2.
    Presumbly :
    FarNotrh.Status is a Text type
    Sub2.Year is a number
    Sub2AmountPaid does not have a Default Value and
    FarNorth.Email is also a text type.

    I'd reccomend you taking the Sql and Paste it into the Sql view of a
    new query, and see if Access gives you a hint as to where the problem
    might be.

    Ron W

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ray Pearson@21:1/5 to Ron Weiner on Sat May 29 16:26:16 2021
    On Saturday, May 22, 2021 at 1:33:44 PM UTC+12, Ron Weiner wrote:
    Ray Pearson presented the following explanation :
    I have used the sql statement in Access 97 but now I am using 2016 and I keep
    getting the old "not enough parameters". I have check everything and all seems well. can anyone throw any light on the problem. I'm new to SQL

    TIA - Ray

    strSql = "SELECT FarNorth.JPID, FarNorth.FirstName, FarNorth.Surname, FarNorth.Email, FarNorth.Status, Sub2.SubID, Sub2.Year, Sub2.AmountPaid" & _
    "FROM FarNorth INNER JOIN Sub2 ON FarNorth.JPID = Sub2.SubID " & _
    "WHERE (((FarNorth.Status)='Active') AND ((Sub2.Year)=2022) AND ((Sub2.AmountPaid) Is Null) AND ((Len([FarNorth].[Email]))>0));"
    I do not see anything obviously wrong with the Sql you have listed, but
    we cannot see what columns are in the tables FarNorth and Sub2.
    Presumbly :
    FarNotrh.Status is a Text type
    Sub2.Year is a number
    Sub2AmountPaid does not have a Default Value and
    FarNorth.Email is also a text type.

    I'd reccomend you taking the Sql and Paste it into the Sql view of a
    new query, and see if Access gives you a hint as to where the problem
    might be.

    Ron W
    Thank you Ron.....I followed-up on your advice to enter the sql in to a query and presto the error was staring me in the face. Thanks again - Ray

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)