SQL query VBA Access issue

Question

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,

I recommend that you don't attempt to nest three DLOOKUP functions like this. Split across three lines (at least) and it will be easier for you to debug. At the very least, you will be able to discover which one of the three calls is causing a problem. — Andy G
– Andy G, Commented May 31, 2014 at 22:04
In response to your edit, you have put a semicolon at the end of the first strSQL = instead of at the end of the query. (In fact, the semicolon is not essential for Access.) — grahamj42
– grahamj42, Commented Jun 6, 2014 at 10:06

grahamj42 · Accepted Answer · 2014-05-31 23:07:29Z

2

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.

answered May 31, 2014 at 23:07

grahamj42

2,7623 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query VBA Access issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related