Etxezarreta
Member
- Local time
- Today, 01:43
- Joined
- Apr 13, 2020
- Messages
- 175
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.
Debug print:
Etxe.
EDITED by Isladogs Added code tags
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: