Solved SQL "step by step" dynamic expression in VBA : error on "AND" (1 Viewer)

[Etxezarreta]

Hello everyone, I have been struggling to build a dynamic SQL chain:
the first step works just fine (str_SQL = str_SQL & " Where t_ListeContacts.Departement In (" & str_CritereDepartement & ")" used to feed a recordset)
the second one returns an error: str_SQL = str_SQL & " AND t_ListeContacts.Profession IN (" & str_CritereProfession & ")", even though str_CritereProfession is not empty the debug print shows an expression that looks alright to me (I put it at the very bottom of this message), but here is one problem: I don't know SQl very well. Do you have any suggestion please? Many thanks in advance.

str_SQL = "SELECT t_ListeContacts.[Nom_contact], t_ListeContacts.[Prenon_contact]," & _
                "t_ListeContacts.[Nom_structure], t_ListeContacts.[Fonction], t_StatutJuridiqueSoignants.StatutJuridiqueSoignant, " & _
                "t_IntitulesProfession.Profession, t_ListeContacts.[Departement], t_ListeContacts.[Ville] FROM t_StatutJuridiqueSoignants " & _
                "INNER JOIN (t_IntitulesProfession INNER JOIN t_ListeContacts ON t_IntitulesProfession.ID_Profession = t_ListeContacts.FK_Profession) " & _
                "ON t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant = t_ListeContacts.FK_Statut_juridique"
    If (Len(str_CritereDepartement) <> 0) Then
        str_SQL = str_SQL & " Where t_ListeContacts.Departement In (" & str_CritereDepartement & ")"
    End If
        Debug.Print str_SQL
    If (Len(str_CritereProfession) <> 0) Then
        str_SQL = str_SQL & " AND t_ListeContacts.Profession IN (" & str_CritereProfession & ")"
    End If

Debug print:

SELECT t_ListeContacts.[Nom_contact], t_ListeContacts.[Prenon_contact],t_ListeContacts.[Nom_structure], t_ListeContacts.[Fonction], t_StatutJuridiqueSoignants.StatutJuridiqueSoignant, t_IntitulesProfession.Profession, t_ListeContacts.[Departement], t_ListeContacts.[Ville] FROM t_StatutJuridiqueSoignants INNER JOIN (t_IntitulesProfession INNER JOIN t_ListeContacts ON t_IntitulesProfession.ID_Profession = t_ListeContacts.FK_Profession) ON t_StatutJuridiqueSoignants.ID_StatutJuridiqueSoignant = t_ListeContacts.FK_Statut_juridique Where t_ListeContacts.Departement In ("64","87") Where (t_ListeContacts.Departement In ("64","87") AND t_ListeContacts.Profession IN ("Médecin"))

Etxe.

EDITED by Isladogs Added code tags

 

[arnelgp]

you have 2 "Where" clause there.

 

[isladogs]

As you can see I added code tags to improve readability
If you look at the debug version you will see you have two WHERE statements

 

[Etxezarreta]

Oh! I have just sorted it out : I give a different name every time I add one step: str_SQL, str_SQL1 etc...
thanks a lot!
Etxe.