Code trop long

**Chris 81** · 02/06/2008, 11h21

Bonjour a tous,

J'ai recopie du code SQL derriere un bouton sur VBA...
Pas de soucis les boutons repondait correctement jusqu'a ce que je rajoute du code toujours en copie de SQL...

L'insertion est trop longue et me renvoie la copie du code sur la prochaine ligne...
Separee les deux lignes marchent bien!

Je pense donc avoir un probleme de syntaxe en fin de la 1ere ou en debut de la 2eme ligne...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
    If Me![Selection1] = 1 Then
        Forms![CompteExploitation].RecordSource = "SELECT DISTINCTROW Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, Sum(DatabaseCustomerDiscountOutDetail.DiscountOutPercent) AS SumOfDiscountOutPercent, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]) AS [Month], InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier, " _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
                                                    & "FROM ((((((((Style INNER JOIN [Order] ON Style.StyleSystem = Order.StyleSystem) INNER JOIN InvoiceOwn ON Order.OrderRef = InvoiceOwn.OrderRef) INNER JOIN InvoicePartner ON Order.OrderRef = InvoicePartner.OrderRef) INNER JOIN InvoiceSupplier ON Order.OrderRef = InvoiceSupplier.OrderRef) INNER JOIN InvoiceTransit ON Order.OrderRef = InvoiceTransit.OrderRef) INNER JOIN InvoiceForwarder ON Order.OrderRef = InvoiceForwarder.OrderRef) INNER JOIN CreditNote ON Order.OrderRef = CreditNote.OrderRef) INNER JOIN DatabaseCustomerDiscountOutDetail ON Style.Customer = DatabaseCustomerDiscountOutDetail.Customer) INNER JOIN DebitNote ON Order.OrderRef = DebitNote.OrderRef " _
                                                    & "GROUP BY Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]), InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier," _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
                                                    & "HAVING (((Style.StyleCustomer) Not Like ""dusc*"") AND ((Order.OrderNrCustomerFull) Is Not Null) AND ((Order.CancellationDate) Is Null) AND ((InvoiceOwn.OwnInvoiceDate) Is Not Null) AND ((InvoiceOwn.OwnFiscalYear)=""08"") AND ((InvoiceOwn.InvoiceRankOwn)=1)) " _
                                                    & "ORDER BY Style.Customer;"

Merci d'avance pour celui ou celle qui se donnera la peine d'eclaircir ma lanterne...

**azertix** · 02/06/2008, 11h27

Essaye de mettre ton code dans une balise [CODE] ou [CODE=SQL] si c'est du SQL pur, pour qu'on voie plus clairement la syntaxe

Parce que là, c'est un peu ... indigeste

**Chris 81** · 02/06/2008, 11h48

le code en soit n'est pas mon soucis.
Mon probleme se situe au niveau des sauts de ligne...
La syntaxe exacte recherchee se situe au niveau des characteres mis en rouge...

If Me![Selection1] = 1 Then
Forms![CompteExploitation].RecordSource = "SELECT DISTINCTROW Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, Sum(DatabaseCustomerDiscountOutDetail.DiscountOutPercent) AS SumOfDiscountOutPercent, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]) AS [Month], InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier, " _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
& "FROM ((((((((Style INNER JOIN [Order] ON Style.StyleSystem = Order.StyleSystem) INNER JOIN InvoiceOwn ON Order.OrderRef = InvoiceOwn.OrderRef) INNER JOIN InvoicePartner ON Order.OrderRef = InvoicePartner.OrderRef) INNER JOIN InvoiceSupplier ON Order.OrderRef = InvoiceSupplier.OrderRef) INNER JOIN InvoiceTransit ON Order.OrderRef = InvoiceTransit.OrderRef) INNER JOIN InvoiceForwarder ON Order.OrderRef = InvoiceForwarder.OrderRef) INNER JOIN CreditNote ON Order.OrderRef = CreditNote.OrderRef) INNER JOIN DatabaseCustomerDiscountOutDetail ON Style.Customer = DatabaseCustomerDiscountOutDetail.Customer) INNER JOIN DebitNote ON Order.OrderRef = DebitNote.OrderRef " _
& "GROUP BY Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]), InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier," _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
& "HAVING (((Style.StyleCustomer) Not Like ""dusc*"") AND ((Order.OrderNrCustomerFull) Is Not Null) AND ((Order.CancellationDate) Is Null) AND ((InvoiceOwn.OwnInvoiceDate) Is Not Null) AND ((InvoiceOwn.OwnFiscalYear)=""08"") AND ((InvoiceOwn.InvoiceRankOwn)=1)) " _
& "ORDER BY Style.Customer;"

Je ne sais pas si je reponds a ta demande...

en racourci quelle est la syntaxe en fin de ligne (et debut de ligne suivante) lorsque le code est trop long...

**azertix** · 02/06/2008, 11h49

Bon, je le fais pour toi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
If Me![Selection1] = 1 Then
Forms![CompteExploitation].RecordSource = "SELECT DISTINCTROW Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, Sum(DatabaseCustomerDiscountOutDetail.DiscountOutPercent) AS SumOfDiscountOutPercent, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]) AS [Month], InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier, " _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
& "FROM ((((((((Style INNER JOIN [Order] ON Style.StyleSystem = Order.StyleSystem) INNER JOIN InvoiceOwn ON Order.OrderRef = InvoiceOwn.OrderRef) INNER JOIN InvoicePartner ON Order.OrderRef = InvoicePartner.OrderRef) INNER JOIN InvoiceSupplier ON Order.OrderRef = InvoiceSupplier.OrderRef) INNER JOIN InvoiceTransit ON Order.OrderRef = InvoiceTransit.OrderRef) INNER JOIN InvoiceForwarder ON Order.OrderRef = InvoiceForwarder.OrderRef) INNER JOIN CreditNote ON Order.OrderRef = CreditNote.OrderRef) INNER JOIN DatabaseCustomerDiscountOutDetail ON Style.Customer = DatabaseCustomerDiscountOutDetail.Customer) INNER JOIN DebitNote ON Order.OrderRef = DebitNote.OrderRef " _
& "GROUP BY Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]), InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier," _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
& "HAVING (((Style.StyleCustomer) Not Like ""dusc*"") AND ((Order.OrderNrCustomerFull) Is Not Null) AND ((Order.CancellationDate) Is Null) AND ((InvoiceOwn.OwnInvoiceDate) Is Not Null) AND ((InvoiceOwn.OwnFiscalYear)=""08"") AND ((InvoiceOwn.InvoiceRankOwn)=1)) " _
& "ORDER BY Style.Customer;"

**Chris 81** · 02/06/2008, 11h51

je remets en gras rouge ou se situe mon soucis

If Me![Selection1] = 1 Then
Forms![CompteExploitation].RecordSource = "SELECT DISTINCTROW Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, Sum(DatabaseCustomerDiscountOutDetail.DiscountOutPercent) AS SumOfDiscountOutPercent, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]) AS [Month], InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier, " _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
& "FROM ((((((((Style INNER JOIN [Order] ON Style.StyleSystem = Order.StyleSystem) INNER JOIN InvoiceOwn ON Order.OrderRef = InvoiceOwn.OrderRef) INNER JOIN InvoicePartner ON Order.OrderRef = InvoicePartner.OrderRef) INNER JOIN InvoiceSupplier ON Order.OrderRef = InvoiceSupplier.OrderRef) INNER JOIN InvoiceTransit ON Order.OrderRef = InvoiceTransit.OrderRef) INNER JOIN InvoiceForwarder ON Order.OrderRef = InvoiceForwarder.OrderRef) INNER JOIN CreditNote ON Order.OrderRef = CreditNote.OrderRef) INNER JOIN DatabaseCustomerDiscountOutDetail ON Style.Customer = DatabaseCustomerDiscountOutDetail.Customer) INNER JOIN DebitNote ON Order.OrderRef = DebitNote.OrderRef " _
& "GROUP BY Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]), InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier," _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
& "HAVING (((Style.StyleCustomer) Not Like ""dusc*"") AND ((Order.OrderNrCustomerFull) Is Not Null) AND ((Order.CancellationDate) Is Null) AND ((InvoiceOwn.OwnInvoiceDate) Is Not Null) AND ((InvoiceOwn.OwnFiscalYear)=""08"") AND ((InvoiceOwn.InvoiceRankOwn)=1)) " _
& "ORDER BY Style.Customer;"

**Jean-Philippe André** · 02/06/2008, 11h57

Salut, et en décomposant en 2 parties

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
If Me![Selection1] = 1 Then
strSQL = "SELECT blablabla..."
Forms![CompteExploitation].RecordSource = strSQL

?

**ullgom** · 02/06/2008, 12h00

je dirais qu'il faut inverser ton & et ton _

exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Public Sub test()
 
    MsgBox "toto" & _
    "titi"
 
End Sub

**Chris 81** · 02/06/2008, 12h02

Je ne comprends pas tres bien...
En creant une variable comme tu le suggeres je ne resolverais pas mon probleme de nombre de charactere dans une seule ligne...

J'essaie,autant que faire ce peut d'etre un peu plus clair...

comment je dis en VBA que ma ligne de code continue sur la ligne suivante...
Concatenation ????

Autant que je m'en souvienne ce doit etre une histoire de _ en fin de ligne...

**Jean-Philippe André** · 02/06/2008, 12h03

cette solution ne te convient-elle pas ?

Envoyé par azertix

Bon, je le fais pour toi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
If Me![Selection1] = 1 Then
Forms![CompteExploitation].RecordSource = "SELECT DISTINCTROW Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, Sum(DatabaseCustomerDiscountOutDetail.DiscountOutPercent) AS SumOfDiscountOutPercent, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]) AS [Month], InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier, " _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
& "FROM ((((((((Style INNER JOIN [Order] ON Style.StyleSystem = Order.StyleSystem) INNER JOIN InvoiceOwn ON Order.OrderRef = InvoiceOwn.OrderRef) INNER JOIN InvoicePartner ON Order.OrderRef = InvoicePartner.OrderRef) INNER JOIN InvoiceSupplier ON Order.OrderRef = InvoiceSupplier.OrderRef) INNER JOIN InvoiceTransit ON Order.OrderRef = InvoiceTransit.OrderRef) INNER JOIN InvoiceForwarder ON Order.OrderRef = InvoiceForwarder.OrderRef) INNER JOIN CreditNote ON Order.OrderRef = CreditNote.OrderRef) INNER JOIN DatabaseCustomerDiscountOutDetail ON Style.Customer = DatabaseCustomerDiscountOutDetail.Customer) INNER JOIN DebitNote ON Order.OrderRef = DebitNote.OrderRef " _
& "GROUP BY Style.StyleCustomer, Order.OrderNrCustomerFull, Order.CancellationDate, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnFiscalYear, InvoiceOwn.InvoiceRankOwn, InvoiceOwn.OwnFiscalYear, InvoiceOwn.OwnInvoiceDate, InvoiceOwn.OwnInvoiceAmountDelivered, Style.Customer, Style.Department, Style.Buyer, Style.Commercial, DatePart(""m"",[OwnInvoiceDate]), InvoiceOwn.OwnInvoiceExclu, CreditNote.CreditNoteAmountEx, InvoiceSupplier.SupplierInvoiceAmountExc, InvoiceForwarder.ForwarderInvoiceAmountExc, InvoiceTransit.TransitInvoiceAmountExc, InvoicePartner.PartnerInvoiceAmountExc, DebitNote.DebitNoteAmountEx, Order.QuantityModified, Order.Supplier," _
& "[OwnInvoiceExclu]-([OwnInvoiceAmountDelivered]*[SumOfDiscountOutPercent]/100)-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc] AS [Amount], ([OwnInvoiceExclu]-[DiscountOutAmount]-[CreditNoteAmountEx]+[DebitNoteAmountEx]-[SupplierInvoiceAmountExc]-[ForwarderInvoiceAmountExc]-[TransitInvoiceAmountExc]-[PartnerInvoiceAmountExc])/[OwnInvoiceExclu]*100 AS [Percent] " _
& "HAVING (((Style.StyleCustomer) Not Like ""dusc*"") AND ((Order.OrderNrCustomerFull) Is Not Null) AND ((Order.CancellationDate) Is Null) AND ((InvoiceOwn.OwnInvoiceDate) Is Not Null) AND ((InvoiceOwn.OwnFiscalYear)=""08"") AND ((InvoiceOwn.InvoiceRankOwn)=1)) " _
& "ORDER BY Style.Customer;"

**Chris 81** · 02/06/2008, 12h09

Pour JP

Ta solution me convient parfaitement ... mais VBA me dit "Syntax error" a partir du debut de la 2 eme ligne...

La requete par elle-meme fonctionne...
Avant le rajou cela fonctionne...
Une fois que la ligne se scinde.... "syntax error"...

**ullgom** · 02/06/2008, 12h17

chris regarde ma réponse ...
Sinon tu peux aussi faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ma_Var= "blabla"
ma_Var = ma_Var & "blibli"
...

**Chris 81** · 02/06/2008, 12h19

pour essayer d'etre un peu plus explicite...

Lorsque je mets ce meme code directement dans le record source cela fonctionne... mais pas lorsqu'il est en VBA

**Chris 81** · 02/06/2008, 12h29

J'ai suivi tes conseils en creant une variable string
[QUOTE]Order.Supplier, & ActualAmountPercent " _[/QUOTE]

malheureusement j'obtiens le meme message d'erreur...
Alors que lorsque je mets ce code directement dans le recordsource cela fonctionne...

Joies et mysteres du code VBA... C'est ce qui fait son charme...