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

Etxezarreta

Member
Local time
Today, 22:49
Joined
Apr 13, 2020
Messages
96
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.

Code:
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:
Code:
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
 
Last edited by a moderator:

isladogs

CID VIP
Local time
Today, 21:49
Joined
Jan 14, 2017
Messages
14,018
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

Member
Local time
Today, 22:49
Joined
Apr 13, 2020
Messages
96
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.
 

Users who are viewing this thread

Top Bottom