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

  • Passing a variable to SQL

    From Ray Pearson@21:1/5 to All on Tue Nov 16 12:28:30 2021
    I have the following code -

    Dim varx As String

    varx = DLookup("[SubYear]", "[tblConfiguration]")

    Set db = CurrentDb()

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

    What am I doing wrong? varx is actually a Year eg 2022.

    TIAA - Ray

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ron Weiner@21:1/5 to All on Tue Nov 16 17:17:38 2021
    It happens that Ray Pearson formulated :
    I have the following code -

    Dim varx As String

    varx = DLookup("[SubYear]", "[tblConfiguration]")

    Set db = CurrentDb()

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

    What am I doing wrong? varx is actually a Year eg 2022.

    TIAA - Ray

    If sub2.SubYear is text try changing :

    AND ((Sub2.SubYear)= varx)

    To :

    AND ((Sub2.SubYear)= ‘“ & varx & “’)

    If it's a number try :

    AND ((Sub2.SubYear)= “ & varx & “)

    The easy way to debug these things is to put a breakpoint on the first instruction after the strSql = assignment. Then debug.print strSql.
    Finaly copy the the full sql statement from the immediate windopw into
    the Sql view of a new query and let access show you the problem.

    Ron W

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ron Paii@21:1/5 to Ray Pearson on Thu Nov 18 09:22:02 2021
    On Tuesday, November 16, 2021 at 2:28:33 PM UTC-6, Ray Pearson wrote:
    I have the following code -

    Dim varx As String

    varx = DLookup("[SubYear]", "[tblConfiguration]")

    Set db = CurrentDb()

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

    What am I doing wrong? varx is actually a Year eg 2022.

    TIAA - Ray

    ((Sub2.SubYear)=" & varx & ")

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