Problem getting WHERE sql statement to execute. 2

[Dom606]

I am having a problem with getting the WHERE statement to work. I currently get an error saying characters found after sql statement (3142).
I think it has to do with having two sql statements in the query. However, I do not know how else to write the where part of sql to include the Where and sWhere criteris. If I put them together I get a syntax error.
Any Ideas?
Thanks

    Dim ParamStart As String
    Dim ParamEnd As String
    Dim Num1 As Integer
    Dim Num2 As Integer
    Dim sWhere As String
    Dim strSQL As String
    
    ParamStart$ = UCase(left(InputBox$("Start With Letter", "Start First Name Letter"), 1))
    ParamEnd$ = UCase(left(InputBox$("End With Letter", "End First Name Letter"), 1))

   '  Get the Asc number for the letter
    Num1 = Asc(ParamStart$)
    Num2 = Asc(ParamEnd$)
    sWhere$ = " Asc(Left([First],1) BETWEEN " & Num1 & " AND " & Num2
    
strSQL = "SELECT [First] & "" "" & [Mid] & "" "" & [Last] AS Name, tblMaster.ADDRESS, [CITY] & "", "" & [ST] & ""  "" & [Zip] AS Address2, " & _
        "IIf(IsNull([Phone]),"" "",[Phone]) AS PHONE1, IIf(IsNull([Tour]),"" "",[Tour]) AS TOUR1, tblMaster.SIGN, " & _
        "IIf(IsNull([FltStatus]),"" "",[FltStatus]) AS FltStatus1, IIf(IsNull([Platoon]),"" "",[Platoon]) AS Platoon1, " & _
        "IIf([tblmaster].[PLATOONID]=20 Or [tblmaster].[ID]=485,""FREE"",[tblDuesYearsLKU].[DuesYears] & """") AS SWITCH, tblMaster.[E-Mail] AS Email " & _
        "FROM tblFltStatusLKU RIGHT JOIN (tblStateLKU RIGHT JOIN (tblPlatoonLKU RIGHT JOIN ((tblMaster LEFT JOIN qryMaster_Label_PD1 ON " & _
        "tblMaster.ID = qryMaster_Label_PD1.ID) LEFT JOIN tblDuesYearsLKU ON qryMaster_Label_PD1.MaxOfDuesID = tblDuesYearsLKU.DuesID) ON " & _
        "tblPlatoonLKU.PlatoonID = tblMaster.PLATOONID) ON tblStateLKU.STCODEID = tblMaster.STCODEID) ON tblFltStatusLKU.StatusID = tblMaster.STATUSID " & _
        "Where ((tblMaster.[E-Mail]) Is Not Null) AND ((tblMaster.STATUSTYPEID)=1);"

strSQL = strSQL & sWhere

Set MailList = db.OpenRecordset(strSQL)

 

[jrbarnett]

Take the semi colon off the end of the first strSQL definition and add " AND " to the start of sWhere.

Additionally, your code declares the sWhere variable but assigns the value to sWhere$ - the same for ParamStart and ParamEnd.

I would therefore also sort this out as well, so the code looks something like:

    Dim ParamStart As String
    Dim ParamEnd As String
    Dim Num1 As Integer
    Dim Num2 As Integer
    Dim sWhere As String
    Dim strSQL As String
    
    ParamStart = UCase(left(InputBox$("Start With Letter", "Start First Name Letter"), 1))
    ParamEnd = UCase(left(InputBox$("End With Letter", "End First Name Letter"), 1))

   '  Get the Asc number for the letter
    Num1 = Asc(ParamStart$)
    Num2 = Asc(ParamEnd$)
    sWhere = " AND Asc(Left([First],1) BETWEEN " & Num1 & " AND " & Num2
    
strSQL = "SELECT [First] & "" "" & [Mid] & "" "" & [Last] AS Name, tblMaster.ADDRESS, [CITY] & "", "" & [ST] & ""  "" & [Zip] AS Address2, " & _
        "IIf(IsNull([Phone]),"" "",[Phone]) AS PHONE1, IIf(IsNull([Tour]),"" "",[Tour]) AS TOUR1, tblMaster.SIGN, " & _
        "IIf(IsNull([FltStatus]),"" "",[FltStatus]) AS FltStatus1, IIf(IsNull([Platoon]),"" "",[Platoon]) AS Platoon1, " & _
        "IIf([tblmaster].[PLATOONID]=20 Or [tblmaster].[ID]=485,""FREE"",[tblDuesYearsLKU].[DuesYears] & """") AS SWITCH, tblMaster.[E-Mail] AS Email " & _
        "FROM tblFltStatusLKU RIGHT JOIN (tblStateLKU RIGHT JOIN (tblPlatoonLKU RIGHT JOIN ((tblMaster LEFT JOIN qryMaster_Label_PD1 ON " & _
        "tblMaster.ID = qryMaster_Label_PD1.ID) LEFT JOIN tblDuesYearsLKU ON qryMaster_Label_PD1.MaxOfDuesID = tblDuesYearsLKU.DuesID) ON " & _
        "tblPlatoonLKU.PlatoonID = tblMaster.PLATOONID) ON tblStateLKU.STCODEID = tblMaster.STCODEID) ON tblFltStatusLKU.StatusID = tblMaster.STATUSID " & _
        "Where ((tblMaster.[E-Mail]) Is Not Null) AND ((tblMaster.STATUSTYPEID)=1) "

strSQL = strSQL & sWhere

Set MailList = db.OpenRecordset(strSQL)

 

[Dom606]

Thank you for your response. I did as you suggested and it is much closer to what it should be. I am now receiving and error regarding syntax as follows.

Run-Time error 3075
Missing),], or item in query expression '((tblMaster.[E-Mail])Is Not Null) AND ((tblMaster.STATUSTYPEID)=1) AND Asc(Left([First],1) BETWEEN 65 AND 67'.

    Dim ParamStart As String
    Dim ParamEnd As String
    Dim Num1 As Integer
    Dim Num2 As Integer
    Dim sWhere As String
    Dim strSQL As String
    
    ParamStart = UCase(left(InputBox$("Start With Letter", "Start First Name Letter"), 1))
    ParamEnd = UCase(left(InputBox$("End With Letter", "End First Name Letter"), 1))

   '  Get the Asc number for the letter
    Num1 = Asc(ParamStart$)
    Num2 = Asc(ParamEnd$)
    sWhere = " AND Asc(Left([First],1) BETWEEN " & Num1 & " AND " & Num2
    
strSQL = "SELECT [First] & "" "" & [Mid] & "" "" & [Last] AS Name, tblMaster.ADDRESS, [CITY] & "", "" & [ST] & ""  "" & [Zip] AS Address2, " & _
        "IIf(IsNull([Phone]),"" "",[Phone]) AS PHONE1, IIf(IsNull([Tour]),"" "",[Tour]) AS TOUR1, tblMaster.SIGN, " & _
        "IIf(IsNull([FltStatus]),"" "",[FltStatus]) AS FltStatus1, IIf(IsNull([Platoon]),"" "",[Platoon]) AS Platoon1, " & _
        "IIf([tblmaster].[PLATOONID]=20 Or [tblmaster].[ID]=485,""FREE"",[tblDuesYearsLKU].[DuesYears] & """") AS SWITCH, tblMaster.[E-Mail] AS Email " & _
        "FROM tblFltStatusLKU RIGHT JOIN (tblStateLKU RIGHT JOIN (tblPlatoonLKU RIGHT JOIN ((tblMaster LEFT JOIN qryMaster_Label_PD1 ON " & _
        "tblMaster.ID = qryMaster_Label_PD1.ID) LEFT JOIN tblDuesYearsLKU ON qryMaster_Label_PD1.MaxOfDuesID = tblDuesYearsLKU.DuesID) ON " & _
        "tblPlatoonLKU.PlatoonID = tblMaster.PLATOONID) ON tblStateLKU.STCODEID = tblMaster.STCODEID) ON tblFltStatusLKU.StatusID = tblMaster.STATUSID " & _
        "Where ((tblMaster.[E-Mail]) Is Not Null) AND ((tblMaster.STATUSTYPEID)=1) "

strSQL = strSQL & sWhere

Set MailList = db.OpenRecordset(strSQL)

 

[PHV]

sWhere = " AND Asc(Left([First],1)[!])[/!] BETWEEN " & Num1 & " AND " & Num2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dom606]

Thank you again. That was it. It now works perfectly. You both get a star.