Erreur 94 : Run time error : invalid use of null

Bonjour a tous !
Jai un probleme lorsque je compile mon code pour calculer le seuil d'un marche
La partie en gras est la ligne contenant l'erreur.
Merci de m'aider

Option Compare Database
Option Explicit

Public Sub Fonction_verification_seuil_marche()

Dim requeteSQL As String
Dim requeteSST As String
Dim requetenbRow As String
Dim rs As Recordset
Dim rsSST As Recordset
Dim rsNbRow As Recordset
Dim montant_LC_modif_sup As Variant
Dim montant_AVP_sup As Variant
Dim montant_a_garder As Variant
Dim mon_marche As String

mon_marche = InputBox("Quel marché souhaitez-vous ?", "Choix du marché")

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM Verification_seuil_marche"

requeteSQL = "SELECT num_affaire, num_marche, Chantier.service, Lettre_commande.id_LC, Chantier.id_chantier, Chantier.RAT, NZ(max(num_AVP),1) as num_AVP_sup, PRO.montant_estimatif_TTC, montant_travaux_LC_TTC, NZ(max(num_modification),1) as LC_modif_sup, montant_travaux_TTC, (SUM(montant_situation_chantier_TTC)/(LC_modif_sup*num_AVP_sup)) AS total_situation_TTC"
requeteSQL = requeteSQL & " FROM ((((((Chantier)"
requeteSQL = requeteSQL & " LEFT JOIN PRO ON Chantier.id_chantier = PRO.id_chantier)"
requeteSQL = requeteSQL & " LEFT JOIN AVP ON AVP.id_chantier = PRO.id_chantier)"
requeteSQL = requeteSQL & " LEFT JOIN Lettre_commande ON Lettre_commande.id_chantier = Chantier.id_chantier)"
requeteSQL = requeteSQL & " LEFT JOIN LC_modificatives ON Lettre_commande.id_LC = LC_modificatives.id_LC)"
requeteSQL = requeteSQL & " LEFT JOIN Situation_chantier ON Situation_chantier.id_chantier = Chantier.id_chantier)"
requeteSQL = requeteSQL & " WHERE num_affaire Is Not Null AND num_marche = '" & mon_marche & "'"
requeteSQL = requeteSQL & " GROUP BY num_affaire, num_marche, Chantier.service, Lettre_commande.id_LC, Chantier.id_chantier, Chantier.RAT, PRO.montant_estimatif_TTC, montant_travaux_LC_TTC, montant_travaux_TTC"

Set rs = CurrentDb.OpenRecordset(requeteSQL)

rs.MoveFirst
While Not rs.EOF
montant_LC_modif_sup = 0
montant_AVP_sup = 0
montant_a_garder = 0
' on teste ensuite si le montant des travaux dans Chantier est rempli
If (IsNull(rs!montant_travaux_TTC) = True) Then
'on teste d'abord si le montant si ils existent des factures attribuées à ce chantier et si le RAT est coché'
If ((IsNull(rs!total_situation_TTC) = True) Or (rs!RAT = False)) Then
' on teste ensuite si le montant des travaux est rempli dans une lettre de commande modificative : si oui, on prend la dernière LC modif
If (IsNull(rs!LC_modif_sup) = True) Then
' on teste ensuite si le montant des travaux est rempli dans la lettre de commande initiale
If (IsNull(rs!montant_travaux_LC_TTC) = True) Then
' on teste ensuite si le montant des travaux est rempli pour le PRO
If (IsNull(rs!montant_estimatif_TTC) = True) Then
' on teste ensuite si le montant des travaux est rempli pour un AVP : on prend le dernier AVP
If (IsNull(rs!num_AVP_sup) = False) Then
If (IsNull(montant_AVP_sup = DLookup("montant_estimatif_TTC", "AVP", "id_chantier = " & rs!id_chantier & " AND num_AVP = " & rs!num_AVP_sup)) = True) Then
montant_a_garder = 0
Else
montant_AVP_sup = DLookup("montant_estimatif_TTC", "AVP", "id_chantier = " & rs!id_chantier & " AND num_AVP = " & rs!num_AVP_sup)
montant_a_garder = montant_AVP_sup
DoCmd.RunSQL "INSERT INTO Verification_seuil_marche VALUES ('" & rs!num_affaire & "','" & rs!num_marche & "',null," & rs!id_chantier & "," & rs!num_AVP_sup & "," & montant_AVP_sup & ",null,null,null,null,null,null," & montant_a_garder & ")"
End If
Else
montant_a_garder = 0
End If
Else
montant_a_garder = rs!montant_estimatif_TTC
DoCmd.RunSQL "INSERT INTO Verification_seuil_marche VALUES ('" & rs!num_affaire & "','" & rs!num_marche & "',null," & rs!id_chantier & "," & rs!num_AVP_sup & ",null," & rs!montant_estimatif_TTC & ",null,null,null,null,null," & montant_a_garder & ")"
End If
Else
montant_a_garder = rs!montant_travaux_LC_TTC
DoCmd.RunSQL "INSERT INTO Verification_seuil_marche VALUES ('" & rs!num_affaire & "','" & rs!num_marche & "'," & rs!id_LC & "," & rs!id_chantier & ",null,null,null," & Val(rs!montant_travaux_LC_TTC) & ",null,null,null,null," & Val(montant_a_garder) & ")"
End If
Else
montant_LC_modif_sup = DLookup("nouveau_montant_TTC", "LC_modificatives", "id_LC = " & rs!id_LC & " AND num_modification = " & rs!LC_modif_sup)
If (IsNull(montant_LC_modif_sup) = True) Then
montant_LC_modif_sup = DLookup("ancien_montant_TTC", "LC_modificatives", "id_LC = " & rs!id_LC & " AND num_modification = " & rs!LC_modif_sup)
End If
montant_a_garder = montant_LC_modif_sup
DoCmd.RunSQL "INSERT INTO Verification_seuil_marche VALUES ('" & rs!num_affaire & "','" & rs!num_marche & "'," & rs!id_LC & "," & rs!id_chantier & ",null,null,null,null," & rs!LC_modif_sup & "," & Val(montant_LC_modif_sup) & ", null,null," & Val(montant_a_garder) & ")"
End If
Else
requetenbRow = "SELECT Count(*) AS nb_chantier FROM Chantier WHERE ((Chantier.num_affaire) Is Not Null) AND (Chantier.num_marche = '" & mon_marche & "')"
Set rsNbRow = CurrentDb.OpenRecordset(requetenbRow)
rsNbRow.MoveFirst

requeteSST = "SELECT NZ(SUM(montant_situation_TTC),0) as total_sous_traitance_TTC"
requeteSST = requeteSST & " FROM ((Chantier)"
requeteSST = requeteSST & " LEFT JOIN Situation_chantier ON Situation_chantier.id_chantier = Chantier.id_chantier)"
requeteSST = requeteSST & " LEFT JOIN Situation_sous_traitant ON Situation_chantier.id_situation_chantier = Situation_sous_traitant.id_situation_chantier"
requeteSST = requeteSST & " WHERE num_marche = '" & mon_marche & "'"
Set rsSST = CurrentDb.OpenRecordset(requeteSST)
rsSST.MoveFirst

montant_a_garder = rs!total_situation_TTC + (rsSST!total_sous_traitance_TTC) / rsNbRow!nb_chantier
DoCmd.RunSQL "INSERT INTO Verification_seuil_marche VALUES ('" & rs!num_affaire & "','" & rs!num_marche & "'," & rs!id_LC & "," & rs!id_chantier & ",null,null,null,null,null,null,null," & Val(montant_a_garder) & "," & Val(montant_a_garder) & ")"
End If
Else
montant_a_garder = rs!montant_travaux_TTC
DoCmd.RunSQL "INSERT INTO Verification_seuil_marche VALUES ('" & rs!num_affaire & "','" & rs!num_marche & "'," & rs!id_LC & "," & rs!id_chantier & ",null,null,null,null,null,null," & Val(rs!montant_travaux_TTC) & ",null," & Val(montant_a_garder) & ")"
End If
rs.MoveNext
Wend

DoCmd.OpenQuery ("requete_verification_seuil_marche")

DoCmd.SetWarnings True

End Sub