Bonjour,

J'ai un petit (gros ) souci avec une requête d'ajout et la fonction SWITCH.

Cette partie de la requête suivante fonctionne:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
Private Sub Commande0_Click()
 
Dim sql As String
sql = " INSERT INTO tblReceptionfichierXLCausedesretards ( Client, [v AR-ligne], [v ref# Client : ligne], [Code article],"
sql = sql & " Article, [v representant], [v planificateur], [Qté ordre], [V fin fab prevue], [Date livraison planifiee],"
sql = sql & " [v Date modifiée accusé depart], [v Date derniere modification des dates accuses], [OF], CAP, Ligne )"
sql = sql & " SELECT Sheet1.[Def#Client], [OV] & ' - ' & [ligne] AS [V AR-ligne],"
sql = sql & " [No commande client] & ' - ' & [Numero comm# client] AS [V réf client ligne],"
sql = sql & " Sheet1.[Code Article], Sheet1.Designation, Sheet1.[Adv client],"
sql = sql & " Sheet1.Planner, Sheet1.[Qté backlog], Sheet1.[Ddée EXW],"
sql = sql & " Sheet1.[reel ddée], Sheet1.[Acc# EXW],"
sql = sql & " Sheet1.[Modif# EXW],"
sql = sql & " Sheet1.[Production order],"
 
' change le contenu dans le champ CAP
sql = sql & "  Switch("
sql = sql & "[Family Newsletter]='Accessories for A Contactors','AU5',"
sql = sql & " [Family Newsletter]='Contactor Size 1 A9 to A16','A9',"
sql = sql & " [Family Newsletter]='Contactor Size 1 AL9 to AL16','AL9',"
sql = sql & " [Family Newsletter]='Accessories for Bar Contactors' and 'Bar Contactors < 800A' and 'Bar Contactors >= 800A','OFR',"
sql = sql & " [Family Newsletter]='Contactor Size 2 A26','A26',"
sql = sql & " [Family Newsletter]='Contactor Size 2 A30 to A40','A30-40',"
sql = sql & " [Family Newsletter]='Contactor Size 2 AL26','AL26',"
sql = sql & " [Family Newsletter]='Contactor Size 2 AL30 to AL40','AL30-AL40',"
sql = sql & " [Family Newsletter]='Contactor Size 3 A50 to A75','A50-A75',"
sql = sql & " [Family Newsletter]='New AF Contactors','AF',"
sql = sql & " [Family Newsletter]='New SNK Terminal Blocks','SNK',"
sql = sql & " [Family Newsletter]='Other contactors (UA-RA, AE, GAE, TAE…) Size 1' AND 'Other contactors (UA-RA, AE, GAE, TAE…) Size 2' AND 'Other contactors (UA-RA, AE, GAE, TAE…) Size 3','AE',"
sql = sql & " [Family Newsletter]='Sensors for Industrial Market','ANR',"
sql = sql & " [Family Newsletter]='Sensors for Railway Market','ANS' "
sql = sql & " ) AS CAP,"
 
' Change le contenu dans le champ Ligne
sql = sql & " Switch("
sql = sql & "[Ligne1]='L1-Terminal Blocks','L1',"
sql = sql & "[Ligne1]='L2-PCB Connection','L2',"
sql = sql & "[Ligne1]='L3-Control Signaling','L3',"
sql = sql & "[Ligne1]='L4 -PLC','L4',"
sql = sql & "[Ligne1]='L5-Contactors','L5',"
sql = sql & "[Ligne1]='L7-Current Sensors','L7'"
sql = sql & " ) AS NumLigne"
sql = sql & " FROM Sheet1;"
Debug.Print sql
DoCmd.RunSQL (sql)
End Sub
Je rajoute ce code suivant à la suite du 1er SWITCH, et là ma requête se plante. Soit c'est trop complexe, soit un pb de syntaxe!!!
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
 
'suite après le 1er switch à la ligne 31
sql = sql & " [Family Newsletter]='Sensors for Railway Market','ANS',"
sql = sql & " [Designation]='BAM 2' AND 'BAM 2 GRIS VO' AND 'BAM 2 IVOIRE V0' AND 'BAM2' AND 'BAM3',"
sql = sql & " AND 'D2.5/5.2L' AND 'D2.5/5.3L' AND 'D2.5/5.4L' AND 'D2.5/5.N.2L' AND 'D2.5/5.N.3L' AND 'D2.5/5.N.4L',"
sql = sql & " AND 'D4/6' AND 'DA2.5/5' AND 'M4 6 N' AND 'M4/6' AND 'M4/6 TERMINAL GREY' AND 'M4/6.1' AND 'M4/6.D2.1',"
sql = sql & " AND 'M4/6.N' AND 'M4/6.V0' AND 'M6/8' AND 'M6/8 TERMINAL BLOCK 24-8AWG' AND 'M6/8.1' AND 'M6/8.4 V0',"
sql = sql & " AND 'M6/8.N' AND 'M6/8.V0' AND 'MA2 5 5' AND 'MA2,5/5.N BLUE TERMINAL BLOCK 22-12AWG' AND 'MA2.5/5',"
sql = sql & " AND 'MA2.5/5.1' AND 'MA2.5/5.N' AND 'MA2.5/5.V0' AND 'TERMINAL 4mm2 32A GREY' AND 'Terminal block ZS4',"
sql = sql & " AND 'TERMINAL ENTERLEC 4MM Type:ZS4 1SNK50501' AND 'ZS4' AND 'ZS4-BK' AND 'ZS4-BL' AND 'ZS4-PE' AND 'ZS4-PR','MAB'"
sql = sql & " ) AS CAP,"
En SQL, ce n'est pas possible trop de carractère...

J'aimerai pouvoir comprendre, et y a t'il une autre solution.
J'ai pensé de passer par une table en définissant les SWITCHs, ou par un module.....
Mais, je ne vois pas comment faire.

Merci d'avance de votre aide