Últimos mensajes - Powered by IBM

Palabras claves

Consulta

21/10/2003 - 17:43 por Simancas Ediciones S.A. | Informe spam

Tengo la siguiente consulta en la pestaña de consulta, y me funciona, pero
si la meto en un db.execute, no me funciona. Alguien me sabe decir por que??

INSERT INTO SIMANCAS_ZAGENDASV ( TIPOAG_0, ENCUADERNA_0, TIPO_0, AGENDA_0,
CANTANT_0, IMPANT_0, CANTACT_0, IMPACT_0 )
SELECT IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O" And
[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",[IT].[_GAGENDA])))
AS TIPOAG, IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]="OOO" Or
[MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or
[MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf([MO].[CODART]="XX
B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]="COSID","CO",IIf([I
T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]="ANILL","AN",IIf([IT].[_CLASE]="B
","CO",[IT].[_CLASE]))))))))) AS ENCUADER, IIf(([MO].[CODART]="OOO" Or
[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or
[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG")) AS TIPO, IIf([MO].[CODART]="MBI" Or
[MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","OOO",[MO].[CODART]) AS CODART, Sum(MO.UNIDADES) AS
CANTPED, Sum(([OP].[COTIZACION]*(Val([MO].[TOTAL])))) AS TOTAL, 0 AS Expr1,
0 AS Expr2
FROM ((dbo_DATEN0902 AS EN RIGHT JOIN dbo_DATOP0902 AS OP ON EN.NIF OP.NIF) LEFT JOIN dbo_DATMO0902 AS MO ON (OP.FECHA = MO.FECHA) AND
(OP.NUMERO = MO.NUMERO)) LEFT JOIN dbo_DATIN0902 AS IT ON MO.CODART IT.CODIGO
WHERE (((OP.TIPOOPER)="C") AND ((OP.FECHA)<=Day([Formularios]![Filtro de
Agendas vendidas]![txtHastaFecha]) & "/" & Month([Formularios]![Filtro de
Agendas vendidas]![txtHastaFecha]) & "/" & Year([Formularios]![Filtro de
Agendas vendidas]![txtHastaFecha])-1))
GROUP BY IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O" And
[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",[IT].[_GAGENDA]))),
IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]="OOO" Or [MO].[CODART]="MBI"
Or [MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf([MO].[CODART]="XX
B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]="COSID","CO",IIf([I
T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]="ANILL","AN",IIf([IT].[_CLASE]="B
","CO",[IT].[_CLASE]))))))))), IIf(([MO].[CODART]="OOO" Or
[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or
[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG")), IIf([MO].[CODART]="MBI" Or
[MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","OOO",[MO].[CODART]), 0, 0
HAVING (((IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O" And
[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",[IT].[_GAGENDA]))))<

"X") AND ((IIf([MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or

[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","OOO",[MO].[CODART]))<>"LIB"))
ORDER BY IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O" And
[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",[IT].[_GAGENDA]))),
IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]="OOO" Or [MO].[CODART]="MBI"
Or [MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf([MO].[CODART]="XX
B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]="COSID","CO",IIf([I
T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]="ANILL","AN",IIf([IT].[_CLASE]="B
","CO",[IT].[_CLASE]))))))))), IIf(([MO].[CODART]="OOO" Or
[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or
[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG"));

Oscar Julio Martínez Cabañero
Simancas Ediciones S.A. - Dpto. Informática
Tlfno: 979 76 15 99 Fax: 979 76 14 17
e-Mail: oscarjmc@agendas-simancas.com
Web: www.agendas-simancas.com

Siga el debate

2 respuestas

Tengo una respuesta

Preguntas similare

Mostrar todos los temas similares

Leer las respuestas

#1 Victor Delgadillo

21/10/2003 - 17:54 | Informe spam

Si funciona en el editor de consultas y no funciona en VBA es posible que te
hagan falta referencias a bibliotecas DLL.
Revisa que tengas las referencias DAO

Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
"Simancas Ediciones S.A." wrote in message
news:eC$uar#

Tengo la siguiente consulta en la pestaña de consulta, y me funciona, pero
si la meto en un db.execute, no me funciona. Alguien me sabe decir por

que??

INSERT INTO SIMANCAS_ZAGENDASV ( TIPOAG_0, ENCUADERNA_0, TIPO_0, AGENDA_0,
CANTANT_0, IMPANT_0, CANTACT_0, IMPACT_0 )
SELECT IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O" And
[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",[IT].[_GAGENDA])))
AS TIPOAG, IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]="OOO" Or
[MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or
[MO].[CODART]="SEP" Or

[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf([MO].[CODART]="XX

B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]="COSID","CO",IIf([I

T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]="ANILL","AN",IIf([IT].[_CLASE]="B

","CO",[IT].[_CLASE]))))))))) AS ENCUADER, IIf(([MO].[CODART]="OOO" Or
[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or
[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG")) AS TIPO, IIf([MO].[CODART]="MBI" Or
[MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","OOO",[MO].[CODART]) AS CODART, Sum(MO.UNIDADES) AS
CANTPED, Sum(([OP].[COTIZACION]*(Val([MO].[TOTAL])))) AS TOTAL, 0 AS

Expr1,

0 AS Expr2
FROM ((dbo_DATEN0902 AS EN RIGHT JOIN dbo_DATOP0902 AS OP ON EN.NIF > OP.NIF) LEFT JOIN dbo_DATMO0902 AS MO ON (OP.FECHA = MO.FECHA) AND
(OP.NUMERO = MO.NUMERO)) LEFT JOIN dbo_DATIN0902 AS IT ON MO.CODART > IT.CODIGO
WHERE (((OP.TIPOOPER)="C") AND ((OP.FECHA)<=Day([Formularios]![Filtro de
Agendas vendidas]![txtHastaFecha]) & "/" & Month([Formularios]![Filtro de
Agendas vendidas]![txtHastaFecha]) & "/" & Year([Formularios]![Filtro de
Agendas vendidas]![txtHastaFecha])-1))
GROUP BY IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O" And

[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",[IT].[_GAGENDA]))),

IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]="OOO" Or

[MO].[CODART]="MBI"

Or [MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or

[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf([MO].[CODART]="XX

B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]="COSID","CO",IIf([I

T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]="ANILL","AN",IIf([IT].[_CLASE]="B

","CO",[IT].[_CLASE]))))))))), IIf(([MO].[CODART]="OOO" Or
[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or
[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG")), IIf([MO].[CODART]="MBI" Or
[MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","OOO",[MO].[CODART]), 0, 0
HAVING (((IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O" And

[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",[IT].[_GAGENDA]))))<

>"X") AND ((IIf([MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or
[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","OOO",[MO].[CODART]))<>"LIB"))
ORDER BY IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O" And

[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",[IT].[_GAGENDA]))),

IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]="OOO" Or

[MO].[CODART]="MBI"

Or [MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or

[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf([MO].[CODART]="XX

B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]="COSID","CO",IIf([I

T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]="ANILL","AN",IIf([IT].[_CLASE]="B

","CO",[IT].[_CLASE]))))))))), IIf(([MO].[CODART]="OOO" Or
[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or
[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG"));

Oscar Julio Martínez Cabañero
Simancas Ediciones S.A. - Dpto. Informática
Tlfno: 979 76 15 99 Fax: 979 76 14 17
e-Mail:
Web: www.agendas-simancas.com

Responder a este mensaje

#2 CarCar

21/10/2003 - 18:04 | Informe spam

Hola:

Las sentencias que se ejecutan en un Execute no pueden
llevar dobles comillas en su definición y ahí veo muchas.

Saludos,
CarCar
MVP-Access

Tengo la siguiente consulta en la pestaña de consulta, y

me funciona, pero

si la meto en un db.execute, no me funciona. Alguien me

sabe decir por que??

INSERT INTO SIMANCAS_ZAGENDASV ( TIPOAG_0, ENCUADERNA_0,

TIPO_0, AGENDA_0,

CANTANT_0, IMPANT_0, CANTACT_0, IMPACT_0 )
SELECT IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]="O"

And

[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",

[IT].[_GAGENDA])))

AS TIPOAG, IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]

="OOO" Or

[MO].[CODART]="MBI" Or [MO].[CODART]="MCP" Or [MO].

[CODART]="MVD" Or

[MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf

([MO].[CODART]="XX

B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]

="COSID","CO",IIf([I

T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]

="ANILL","AN",IIf([IT].[_CLASE]="B

","CO",[IT].[_CLASE]))))))))) AS ENCUADER, IIf(([MO].

[CODART]="OOO" Or

[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].

[CODART]="MCP" Or

[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG")) AS TIPO, IIf([MO].[CODART]

="MBI" Or

[MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].

[CODART]="SEP" Or

[MO].[CODART]="BLO","OOO",[MO].[CODART]) AS CODART, Sum

(MO.UNIDADES) AS

CANTPED, Sum(([OP].[COTIZACION]*(Val([MO].[TOTAL])))) AS

TOTAL, 0 AS Expr1,

0 AS Expr2
FROM ((dbo_DATEN0902 AS EN RIGHT JOIN dbo_DATOP0902 AS OP

ON EN.NIF >OP.NIF) LEFT JOIN dbo_DATMO0902 AS MO ON (OP.FECHA =
MO.FECHA) AND

(OP.NUMERO = MO.NUMERO)) LEFT JOIN dbo_DATIN0902 AS IT ON

MO.CODART >IT.CODIGO

WHERE (((OP.TIPOOPER)="C") AND ((OP.FECHA)<=Day

([Formularios]![Filtro de

Agendas vendidas]![txtHastaFecha]) & "/" & Month

([Formularios]![Filtro de

Agendas vendidas]![txtHastaFecha]) & "/" & Year

([Formularios]![Filtro de

Agendas vendidas]![txtHastaFecha])-1))
GROUP BY IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]

="O" And

[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",

[IT].[_GAGENDA]))),

IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]="OOO" Or

[MO].[CODART]="MBI"

Or [MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].

[CODART]="SEP" Or

[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf

([MO].[CODART]="XX

B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]

="COSID","CO",IIf([I

T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]

="ANILL","AN",IIf([IT].[_CLASE]="B

","CO",[IT].[_CLASE]))))))))), IIf(([MO].[CODART]="OOO" Or
[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].

[CODART]="MCP" Or

[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG")), IIf([MO].[CODART]="MBI"

[MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].

[CODART]="SEP" Or

[MO].[CODART]="BLO","OOO",[MO].[CODART]), 0, 0
HAVING (((IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]

="O" And

[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",

[IT].[_GAGENDA]))))<

"X") AND ((IIf([MO].[CODART]="MBI" Or [MO].[CODART]

="MCP" Or

[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO","OOO",[MO].[CODART]))<>"LIB"))
ORDER BY IIf([MO].[CODART]="BL","M",IIf([IT].[_GAGENDA]

="O" And

[IT].[_CLASE]="CANUT","W",IIf([IT].[_CLASE]="WIREO","W",

[IT].[_GAGENDA]))),

IIf([MO].[CODART]="TFQ","CA",IIf([MO].[CODART]="OOO" Or

[MO].[CODART]="MBI"

Or [MO].[CODART]="MCP" Or [MO].[CODART]="MVD" Or [MO].

[CODART]="SEP" Or

[MO].[CODART]="BLO","MAR",IIf([IT].[_GAGENDA]="M","CO",IIf

([MO].[CODART]="XX

B","CO",IIf([IT].[_CLASE]="CANUT","CA",IIf([IT].[_CLASE]

="COSID","CO",IIf([I

T].[_CLASE]="WIREO","WI",IIf([IT].[_CLASE]

="ANILL","AN",IIf([IT].[_CLASE]="B

","CO",[IT].[_CLASE]))))))))), IIf(([MO].[CODART]="OOO" Or
[MO].[CODART]="TAC") Or ([MO].[CODART]="MBI" Or [MO].

[CODART]="MCP" Or

[MO].[CODART]="MVD" Or [MO].[CODART]="SEP" Or
[MO].[CODART]="BLO"),"OT",IIf([MO].[_COD_TAPA]="REC" Or
[MO].[_COD_TAPA]="","IN","AG"));

Oscar Julio Martínez Cabañero
Simancas Ediciones S.A. - Dpto. Informática
Tlfno: 979 76 15 99 Fax: 979 76 14 17
e-Mail:
Web: www.agendas-simancas.com

.

Siga el debate Respuesta

Responder a este mensaje

Ads by Google

Hacer una pregunta

Tengo una respuesta

Busqueda sugerida