[AC-2007] erreur de code dans une requete [Résolu]

[Chris 81]

Bonjour à tous,

Cela fait maintenant plusieurs heures que j'essaie de trouver mon erreur malgré plusieurs cafés et autant de cigarettes...
Si quelqu'un avait le courage d'y jeter un œil... mais le bon!

Code sql :

1
2
3
4
5
 ActualCourierDesign = "SELECT Design.DesignCreationDate, Design.DesignName, Design.DesignType, Design.DesignExpectedDate, Design.DesignCustomerName, Design.DesignDescription, Design.DesignInstruction, Design.DesignRequested, Design.DesignRequestDate, Design.DesignSupplierName, PictureDesign.PictureRank, PictureDesign.PictureAddress, Design.DesignSystem, Design.Designer, & DLookUp("NameShort","DatabasePeople","FirstName = '" & [Designer] & "'") & " AS DesignerShort, Design.DesignNrSupplier, Design.DesignStatus, Design.DesignRequestRank, AnimationDesign.AnimationRank, AnimationDesign.DesignAnimationRef, AnimationDesign.Animation, AnimationDesign.AnimationCodeBar, AnimationDesign.AnimationColour, AnimationDesign.AnimationDesignation, AnimationDesign.AnimationRef, AnimationDesign.AnimationType, FabricDesign.FabricRank, FabricDesign.DesignFabricref, FabricDesign.Fabric, FabricDesign.FabricCodeBar, FabricDesign.FabricColor, FabricDesign.FabricComposition, FabricDesign.FabricConstruction, " _
& "FabricDesign.FabricReference, FabricDesign.FabricTreatment, FabricDesign.FabricWeight " _
& "FROM ((Design INNER JOIN PictureDesign ON Design.DesignSystem = PictureDesign.DesignSystem) INNER JOIN AnimationDesign ON Design.DesignSystem = AnimationDesign.DesignSystem) INNER JOIN FabricDesign ON Design.DesignSystem = FabricDesign.DesignSystem " _
& "WHERE (((Design.DesignCreationDate) IS NOT NULL) AND ((Design.DesignRequestDate) IS NULL) AND ((PictureDesign.PictureRank) = 1) AND ((AnimationDesign.AnimationRank) = 1) AND ((FabricDesign.FabricRank) = 1) AND ((Design.DesignCancelDate) IS NULL) AND ((Design.DesignApprovedDate) IS NULL)) " _
& "ORDER BY Design.DesignCreationDate, Design.DesignName;"

[vodiem]

salut Chris 81,

à première vu comme ca, je vois que:

ActualDesigner = "SELECT Design.DesignCreationDate, Design.DesignName, Design.DesignType, Design.DesignExpectedDate, Design.DesignCustomerName, Design.DesignDescription, Design.DesignInstruction, Design.DesignRequested, Design.DesignRequestDate, Design.DesignSupplierName, PictureDesign.PictureRank, PictureDesign.PictureAddress, Design.DesignSystem, Design.Designer, & DLookUp("NameShort","DatabasePeople","FirstName = '" & [Designer] & "'") & " AS DesignerShort, Design.DesignNrSupplier, Design.DesignStatus, Design.DesignRequestRank, AnimationDesign.AnimationRank, AnimationDesign.DesignAnimationRef, AnimationDesign.Animation, AnimationDesign.AnimationCodeBar, AnimationDesign.AnimationColour, AnimationDesign.AnimationDesignation, AnimationDesign.AnimationRef, AnimationDesign.AnimationType, FabricDesign.FabricRank, FabricDesign.DesignFabricref, FabricDesign.Fabric, FabricDesign.FabricCodeBar, FabricDesign.FabricColor, FabricDesign.FabricComposition, FabricDesign.FabricConstruction, " _
& "FabricDesign.FabricReference, FabricDesign.FabricTreatment, FabricDesign.FabricWeight " _
& "FROM ((Design INNER JOIN PictureDesign ON Design.DesignSystem = PictureDesign.DesignSystem) INNER JOIN AnimationDesign ON Design.DesignSystem = AnimationDesign.DesignSystem) INNER JOIN FabricDesign ON Design.DesignSystem = FabricDesign.DesignSystem " _
& "WHERE (((Design.DesignCreationDate) Is Not Null) AND ((Design.DesignRequestDate) Is Null) AND ((PictureDesign.PictureRank) = 1) AND ((AnimationDesign.AnimationRank) = 1) AND ((FabricDesign.FabricRank) = 1) AND ((Design.DesignCancelDate) Is Null) AND ((Design.DesignApprovedDate) Is Null)) " _
& "ORDER BY Design.DesignCreationDate, Design.DesignName;"

(attention à ce que "& [Designer] &" ne contient pas d'apostrophe)
et doubler les guillemets dans le dlookup()...

[Chris 81]

Merci pour la promptitude de ta réponse

Entre temps j'avais partiellement trouve l'erreur mais malheureusement ce n'est toujours pas la solution...

[vodiem]

tu as bien un truc comme ca:

Code :

ActualDesigner = "SELECT ..., DLookUp(""NameShort"",""DatabasePeople"",""FirstName = '" & [Designer] & "'"") AS DesignerShort, ..."

?

[Chris 81]

voila ce que j'ai ...

Code :

1
2
 
, & DLookUp("NameShort","DatabasePeople","FirstName = '" & [Designer] & "'") & " AS DesignerShort

[Chris 81]

je viens de relire a nouveau ton message...
Tu mets des doubles guillemets ?

[Chris 81]

Pendant que je t'écrivais mon dernier message m'est venue l'idée de supprimer ce bout de code pour vérifier si c'est bien à ce niveau la que j'ai mon soucis...
Je confirme que c'est bien dans ce bout de code qu'il y a un problème puisque VBA l'a accepté sans ce code...

[vodiem]

Envoyé par Chris 81

Tu mets des doubles guillemets ?

ben oui, faut bien qu'il sache que c'est un guillemet et pas la fin du string.
on double le guillemet ou l'apostrophe à l'intérieur du string si on l'ouvre avec un de ces caractères et qu'on veut le faire reconnaitre comme contenu.
revoit l'exemple que j'ai donné.
par contre j'ai suposé que [Designer] était une variable dans le code VB et non un champ de la requete, ce qui s'écrirait autrement.

[Chris 81]

Bien le bonjour,

[Designer] est effectivement un champ de la requête
Ceci montre une fois de plus que l’on n’est jamais assez précis.
Toutes mes excuses et merci pour ton aide

[vodiem]

dans ce cas tu devrais avoir un code comme ceci:

Code :

ActualDesigner = "SELECT ..., DLookUp(""NameShort"",""DatabasePeople"",""FirstName = '"" & [Designer] & ""'"") AS DesignerShort, ..."

[Chris 81]

Merci beaucoup ca marche ...