CODEWITHSUNDEEP
ms-access
Ziko Otmani
Ziko Otmani

Reputation: 25

SQL query VBA Access issue

I get an error when I try to execute this code, I think I have a problem in the sql query and the line "Service = Dlookup ..." can you help please ! thank you very much

    Private Sub btnConnexion_Click()

    Dim Categ As Integer
    Dim Service As String
    Dim IdProf As Integer

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb


    'vérification que l'utilisater a bien entrer e login et le mot de passe
    Me.txtlogin.SetFocus
    If IsNull(Me.txtlogin) Then
    MsgBox "svp entrer votre login ", vbInformation, "login required "
    Me.txtlogin.SetFocus
    ElseIf IsNull(Me.txtmdp) Then
    MsgBox "svp entrer votre mots de passe ", vbInformation, "mdp required "
    Me.txtmdp.SetFocus
    Else
    'vérification que le login et le mdp sont corrects
    If (IsNull(DLookup("login", "dbo_Authentification", "login='" & Me.txtlogin.Value &       "'"))) Or _
        (IsNull(DLookup("mdp", "dbo_Authentification", "mdp='" & Me.txtmdp.Value & "'")))    Then
        MsgBox "login ou mdp incorrect"
    Else
    'récupération de l'IdCatégorie dans Categ, pour préciser les sessions des acteurs selon   leurs catégories professionneles
        Categ = DLookup("IdCategorie", "dbo_Professionnel", "IdProfessionnel = " &   DLookup("IdCompte", "dbo_Authentification", "login='" & Me.txtlogin.Value & "'"))
        'DoCmd.Close
        If Categ = 3 Then
            DoCmd.OpenForm "role"
            Else
            DoCmd.OpenForm "ListingPatients"

            'Service récupère le service du professionnel authentifié pour l'afficher à   l'entete du formulaire "ListingPatients"
            Service = DLookup("IntituleServ", "dbo_Service", "IdService = " &   DLookup("IdProfessionnel", "dbo_Professionnel", "IdProfessionnel = " & DLookup("IdCompte",   "dbo_Authentification", "login='" & Me.txtlogin.Value & "'")))
            Forms![ListingPatients]![txtIntituleServ] = Service



            strSQL = "SELECT dbo_Patient.*, dbo_Service.IntituleServ, dbo_HospitalisatAcuelle.lit, dbo_Professionnel.IdProfessionnel, dbo_HospitalisatAcuelle.DateEntree, dbo_HospitalisatAcuelle.DateSortie FROM dbo_Service INNER JOIN ((dbo_Professionnel INNER JOIN dbo_Authentification ON dbo_Professionnel.IdProfessionnel = dbo_Authentification.IdCompte) INNER JOIN (dbo_Patient INNER JOIN (dbo_HospitalisatAcuelle INNER JOIN dbo_DonneePatientActuelles ON dbo_HospitalisatAcuelle.IdHosp = dbo_DonneePatientActuelles.IdHosp) ON dbo_Patient.IdPatient = dbo_DonneePatientActuelles.IdPatient) ON dbo_Professionnel.IdProfessionnel = dbo_HospitalisatAcuelle.IdProfessionnel) ON dbo_Service.IdService = dbo_Professionnel.Idservice WHERE (((dbo_HospitalisatAcuelle.DateEntree)<=Now()) AND ((dbo_HospitalisatAcuelle.DateSortie)>Now())) OR (((dbo_HospitalisatAcuelle.DateSortie) Is Null) AND dbo_Service.IntituleServ = '" & Service & "') ;"

       End If
    End If
   End If


   End Sub

So when I execute this code I get the error "Invalid use of null" in the line "Service = ..." and the sql query dosen't return the condition when ! thank you very very much

So, after adding the Nz, I think that the problem of Null is solved

           Service = Nz(DLookup("IntituleServ", "dbo_Service", "IdService = " & DLookup("IdService", "dbo_Professionnel", "IdProfessionnel = " & DLookup("IdCompte", "dbo_Authentification", "login='" & Me.txtlogin.Value & "'"))), "inconnu")
        Forms![ListingPatients]![txtIntituleServ] = Service

But I still have an issue with the SQL query, I think I didn't put the correct syntax of SQL query integrating in the VBA code, can you take a look to this line : 

        strSQL = "SELECT dbo_Patient.*, dbo_Service.IntituleServ, dbo_HospitalisatAcuelle.lit, dbo_Professionnel.IdProfessionnel, dbo_HospitalisatAcuelle.DateEntree, dbo_HospitalisatAcuelle.DateSortie FROM dbo_Service INNER JOIN ((dbo_Professionnel INNER JOIN dbo_Authentification ON dbo_Professionnel.IdProfessionnel = dbo_Authentification.IdCompte) INNER JOIN (dbo_Patient INNER JOIN (dbo_HospitalisatAcuelle INNER JOIN dbo_DonneePatientActuelles ON dbo_HospitalisatAcuelle.IdHosp = dbo_DonneePatientActuelles.IdHosp) ON dbo_Patient.IdPatient = dbo_DonneePatientActuelles.IdPatient) ON dbo_Professionnel.IdProfessionnel = dbo_HospitalisatAcuelle.IdProfessionnel) ON dbo_Service.IdService = dbo_Professionnel.Idservice WHERE (((dbo_HospitalisatAcuelle.DateEntree)<=Now()) AND ((dbo_HospitalisatAcuelle.DateSortie)>Now())) OR (((dbo_HospitalisatAcuelle.DateSortie) Is Null)) ;"
        strSQL = strSQL & "WHERE [dbo_Service]![IntituleServ] = ' " & Service & " ' "
DoCmd.OpenQuery(strSQL)

Thank you,

Upvotes: 0

Views: 127

Answers (1)

grahamj42
grahamj42

Reputation: 2762

You have declared Service as a string variable. DLookup returns Null if no matching record is found. A Null cannot be stored in a String, but can be stored in a Variant.

So either use Dim Service As Variant and test with IsNull()

or use Service = Nz(DLookup(...), "Inconnu") to return "Inconnu" if Dlookup() fails.

Upvotes: 2

Related Questions