1. Forum
  2. USENET
  3. comp.databases.ms-access

  • Passing a variable to SQL

    From Ray Pearson@rwpearson123@gmail.com to comp.databases.ms-access on Tue Nov 16 12:28:30 2021
    From Newsgroup: comp.databases.ms-access

    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
    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Ron Weiner@rw@domain.com to comp.databases.ms-access on Tue Nov 16 17:17:38 2021
    From Newsgroup: comp.databases.ms-access

    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)= rCyrCL & varx & rCLrCO)

    If it's a number try :

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

    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
    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Ron Paii@ron81pai@gmail.com to comp.databases.ms-access on Thu Nov 18 09:22:02 2021
    From Newsgroup: comp.databases.ms-access

    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 & ")

    --- Synchronet 3.21b-Linux NewsLink 1.2