Discussion :

[acheo]

Lorsque la grosseur d'une requete (en caracteres) demeure sous une certaine valeur, je n'ai aucun probleme. J'ai determine que si la chaine de caractere demeure aux environ de 6468 caracteres incluant les espaces, la requete s'effectue normalement. Cependant, si je soumet la chaine de caractere SQL suivante, j'obtiens le message d'erreur "Expression too complex". Existe-t-il des limites concernant les requetes en VBA sous Access?

Voici un exemple ou j'obtiens le message d'erreur. Notez que si ma requete contient un peu moins de personnes (aircraftCommander & pax), tout se deroule normalement.

Code sql :

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

INSERT INTO z_TripReport ( tripNumber, aircraftCommander, surname, pax, dateDep, status, flightProChecked, flight, paxSurname, rank ) SELECT Trips.tripNumber, Trips.aircraftCommander, Personnel.surname, Trips.pax, Trips.dateDep, Trips.status, Trips.flightProChecked, Flights.flight, Personnel_1.surname AS paxSurname, Ranks.rank FROM Ranks INNER JOIN ((Flights INNER JOIN (Personnel INNER JOIN Trips ON Personnel.SN = Trips.aircraftCommander) ON Flights.idFlight = Personnel.location) LEFT JOIN Personnel AS Personnel_1 ON Trips.pax = Personnel_1.SN) ON Ranks.idRank = Personnel.Rank WHERE dateDep >= #1/1/2007# AND dateDep <= #12/31/2007# AND ((aircraftCommander = 'A10355250' OR pax = 'A10355250') OR (aircraftCommander = 'A14270257' OR pax = 'A14270257') OR (aircraftCommander = 'A48770475' OR pax = 'A48770475') OR (aircraftCommander = 'A61311680' OR pax = 'A61311680') OR (aircraftCommander = 'A65113065' OR pax = 'A65113065') OR (aircraftCommander = 'A65303766' OR pax = 'A65303766') OR (aircraftCommander = 'A89168089' OR pax = 'A89168089') OR (aircraftCommander = 'B10155314' OR pax = 'B10155314') OR (aircraftCommander = 'B12261064' OR pax = 'B12261064') OR (aircraftCommander = 'B22892802' OR pax = 'B22892802') OR (aircraftCommander = 'B32212328' OR pax = 'B32212328') OR (aircraftCommander = 'B33788793' OR pax = 'B33788793') OR (aircraftCommander = 'B50165754' OR pax = 'B50165754') OR (aircraftCommander = 'B50409119' OR pax = 'B50409119') OR (aircraftCommander = 'B56134456' OR pax = 'B56134456') OR (aircraftCommander = 'B68760855' OR pax = 'B68760855') OR (aircraftCommander = 'B70942110' OR pax = 'B70942110') OR (aircraftCommander = 'B80940929' OR pax = 'B80940929') OR (aircraftCommander = 'C10608680' OR pax = 'C10608680') OR (aircraftCommander = 'C21928193' OR pax = 'C21928193') OR (aircraftCommander = 'C43476790' OR pax = 'C43476790') OR (aircraftCommander = 'C53117400' OR pax = 'C53117400') OR (aircraftCommander = 'C62873852' OR pax = 'C62873852') OR (aircraftCommander = 'C79443056' OR pax = 'C79443056') OR (aircraftCommander = 'C84264397' OR pax = 'C84264397') OR (aircraftCommander = 'D18407875' OR pax = 'D18407875') OR (aircraftCommander = 'D25484628' OR pax = 'D25484628') OR (aircraftCommander = 'D29133297' OR pax = 'D29133297') OR (aircraftCommander = 'D30575514' OR pax = 'D30575514') OR (aircraftCommander = 'D63017392' OR pax = 'D63017392') OR (aircraftCommander = 'D63344615' OR pax = 'D63344615') OR (aircraftCommander = 'D66403590' OR pax = 'D66403590') OR (aircraftCommander = 'D72660439' OR pax = 'D72660439') OR (aircraftCommander = 'D74851340' OR pax = 'D74851340') OR (aircraftCommander = 'D75387845' OR pax = 'D75387845') OR (aircraftCommander = 'D75834191' OR pax = 'D75834191') OR (aircraftCommander = 'D82298884' OR pax = 'D82298884') OR (aircraftCommander = 'F21509971' OR pax = 'F21509971') OR (aircraftCommander = 'F28151581' OR pax = 'F28151581') OR (aircraftCommander = 'F29702500' OR pax = 'F29702500') OR (aircraftCommander = 'F29912143' OR pax = 'F29912143') OR (aircraftCommander = 'F52236485' OR pax = 'F52236485') OR (aircraftCommander = 'F56234019' OR pax = 'F56234019') OR (aircraftCommander = 'F66993433' OR pax = 'F66993433') OR (aircraftCommander = 'F68403317' OR pax = 'F68403317') OR (aircraftCommander = 'F71806791' OR pax = 'F71806791') OR (aircraftCommander = 'F79826799' OR pax = 'F79826799') OR (aircraftCommander = 'F80155521' OR pax = 'F80155521') OR (aircraftCommander = 'H12659704' OR pax = 'H12659704') OR (aircraftCommander = 'H19667194' OR pax = 'H19667194') OR (aircraftCommander = 'H20301914' OR pax = 'H20301914') OR (aircraftCommander = 'H21403960' OR pax = 'H21403960') OR (aircraftCommander = 'H24260980' OR pax = 'H24260980') OR (aircraftCommander = 'H26006298' OR pax = 'H26006298') OR (aircraftCommander = 'H26740460' OR pax = 'H26740460') OR (aircraftCommander = 'H38816803' OR pax = 'H38816803') OR (aircraftCommander = 'H39242006' OR pax = 'H39242006') OR (aircraftCommander = 'H40120464' OR pax = 'H40120464') OR (aircraftCommander = 'H44530189' OR pax = 'H44530189') OR (aircraftCommander = 'H70942042' OR pax = 'H70942042') OR (aircraftCommander = 'H72906999' OR pax = 'H72906999') OR (aircraftCommander = 'H75934917' OR pax = 'H75934917') OR (aircraftCommander = 'H76687981' OR pax = 'H76687981') OR (aircraftCommander = 'H79843730' OR pax = 'H79843730') OR (aircraftCommander = 'K10124959' OR pax = 'K10124959') OR (aircraftCommander = 'K12710389' OR pax = 'K12710389') OR (aircraftCommander = 'K24914108' OR pax = 'K24914108') OR (aircraftCommander = 'K25484646' OR pax = 'K25484646') OR (aircraftCommander = 'K30090854' OR pax = 'K30090854') OR (aircraftCommander = 'K36867552' OR pax = 'K36867552') OR (aircraftCommander = 'K39627638' OR pax = 'K39627638') OR (aircraftCommander = 'K40254229' OR pax = 'K40254229') OR (aircraftCommander = 'K47299879' OR pax = 'K47299879') OR (aircraftCommander = 'K59409732' OR pax = 'K59409732') OR (aircraftCommander = 'K60811504' OR pax = 'K60811504') OR (aircraftCommander = 'K63916611' OR pax = 'K63916611') OR (aircraftCommander = 'K80158242' OR pax = 'K80158242') OR (aircraftCommander = 'K81027915' OR pax = 'K81027915') OR (aircraftCommander = 'N22100908' OR pax = 'N22100908') OR (aircraftCommander = 'N23841875' OR pax = 'N23841875') OR (aircraftCommander = 'N47515387' OR pax = 'N47515387') OR (aircraftCommander = 'N51904712' OR pax = 'N51904712') OR (aircraftCommander = 'N52004173' OR pax = 'N52004173') OR (aircraftCommander = 'N53287410' OR pax = 'N53287410') OR (aircraftCommander = 'N70563471' OR pax = 'N70563471') OR (aircraftCommander = 'N71153657' OR pax = 'N71153657') OR (aircraftCommander = 'N79292995' OR pax = 'N79292995') OR (aircraftCommander = 'N80358594' OR pax = 'N80358594') OR (aircraftCommander = 'R34109383' OR pax = 'R34109383') OR (aircraftCommander = 'R52560206' OR pax = 'R52560206') OR (aircraftCommander = 'R62055141' OR pax = 'R62055141') OR (aircraftCommander = 'R66645789' OR pax = 'R66645789') OR (aircraftCommander = 'R79017007' OR pax = 'R79017007') OR (aircraftCommander = 'R79209817' OR pax = 'R79209817') OR (aircraftCommander = 'T10224870' OR pax = 'T10224870') OR (aircraftCommander = 'T15361803' OR pax = 'T15361803') OR (aircraftCommander = 'T16823267' OR pax = 'T16823267') OR (aircraftCommander = 'T21778634' OR pax = 'T21778634') OR (aircraftCommander = 'T22354000' OR pax = 'T22354000') OR (aircraftCommander = 'T33807140' OR pax = 'T33807140') OR (aircraftCommander = 'T39223789' OR pax = 'T39223789') OR (aircraftCommander = 'T42738701' OR pax = 'T42738701') OR (aircraftCommander = 'T48519599' OR pax = 'T48519599') OR (aircraftCommander = 'T49096783' OR pax = 'T49096783') OR (aircraftCommander = 'T54910816' OR pax = 'T54910816') OR (aircraftCommander = 'T62088203' OR pax = 'T62088203') OR (aircraftCommander = 'T65303573' OR pax = 'T65303573') OR (aircraftCommander = 'T69632322' OR pax = 'T69632322') OR (aircraftCommander = 'T71806662' OR pax = 'T71806662') OR (aircraftCommander = 'T89347743' OR pax = 'T89347743') OR (aircraftCommander = 'V11255088' OR pax = 'V11255088') OR (aircraftCommander = 'V12261418' OR pax = 'V12261418') OR (aircraftCommander = 'V21521896' OR pax = 'V21521896') OR (aircraftCommander = 'V28678657' OR pax = 'V28678657') OR (aircraftCommander = 'V38621431' OR pax = 'V38621431') OR (aircraftCommander = 'V50409101' OR pax = 'V50409101') OR (aircraftCommander = 'V51098771' OR pax = 'V51098771') OR (aircraftCommander = 'V62316785' OR pax = 'V62316785') OR (aircraftCommander = 'V63218641' OR pax = 'V63218641') OR (aircraftCommander = 'V65213659' OR pax = 'V65213659') OR (aircraftCommander = 'V66725867' OR pax = 'V66725867') OR (aircraftCommander = 'V73144853' OR pax = 'V73144853') OR (aircraftCommander = 'V77536892' OR pax = 'V77536892') OR (aircraftCommander = 'V80727945' OR pax = 'V80727945') OR (aircraftCommander = 'V82465749' OR pax = 'V82465749') OR (aircraftCommander = 'V85843294' OR pax = 'V85843294')) AND ((status = 'Pending') OR (status = 'Approved') OR (status = 'Flown') OR (status = 'Completed') OR (status = 'Cancelled'))

[=JBO=]

Bonjour,

Le moteur de bases de données d'Access (JET) supporte des requêtes d'une taille maximale d'environ 64000 caractères. Donc le problème n'est pas là.

Dans ton cas, c'est le nombre d'opérateurs OR et AND qui doit dépasser les capacités de l'analyseur SQL.

A la lecture de ta requête, je suggère d'utiliser l'opérateur IN:

Code SQL :

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

1
2
3
4
5
 
WHERE dateDep >= #1/1/2007# AND dateDep <= #12/31/2007# AND 
(aircraftCommander IN ('A10355250', 'A14270257', 'A48770475') OR 
pax  IN ('A10355250', 'A14270257', 'A48770475')) AND 
(status IN ('Pending', 'Approved', 'Flown', 'Completed', 'Cancelled'))

[acheo]

Super, ca fonctionne! Par curiosite, avez-vous une idee du nombre d'operateurs logiques qu'Access peut accepter dans une requete?

merci

Acheo

[=JBO=]

Bonjour,

Par curiosite, avez-vous une idee du nombre d'operateurs logiques qu'Access peut accepter dans une requete?

Voilà ce que je sais:

QUERY SPECIFICATIONS

The following table gives the maximum size for various attributes of a query.

Attribute -- > Maximum

Number of tables in a query -- > 32
Number of fields in a recordset -- > 255
Number of fields in an ORDER BY clause -- > 10
Number of levels of nested queries -- > 50
Number of characters in a parameter name -- > 255
Number of ANDs in an expression -- > 40
Number of characters in an SQL statement -- > Approximately 64,000

Dans ces spécifications il n'est pas question d'une limite du nombre d'opérateurs logiques.
Il s'agit plutôt du degré de complexité pour l'exécution d'une requête.

En revanche, en ce qui concerne l'analyse syntaxique de la requête, on peut "deviner" que l'analyseur du moteur de bases de données JET sera confronté à certaines limites de la structure de données nécessaire à la représentation de la requête (par exemple, un arbre syntaxique).
Dans ton cas, je pense que c'est là que ça se passe...