Bruno Campanini
unread,Nov 24, 2020, 11:59:22 AM11/24/20You do not have permission to delete messages in this group
Sign in to report message
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
to
I have an Access query that looks like this:
SELECT [1_GEN].F1,
Sum([1_GEN].[F2]+[2_FEB].[F2]) AS FF2,
Sum([1_GEN].[F3]+[2_FEB].[F4]) AS FF3,
Sum([1_GEN].[F4]+[2_FEB].[F4]) AS FF4,
Sum([1_GEN].[F5]+[2_FEB].[F5]) AS FF5,
Sum([1_GEN].[F6]+[2_FEB].[F6]) AS FF6,
[FF2]+[FF3]+[FF4]+[FF5]+[FF6] AS S
FROM 1_GEN
INNER JOIN 2_FEB ON [1_GEN].F1 = [2_FEB].F1
GROUP BY [1_GEN].F1;
I have transformed to Excel in this way:
SQL = "SELECT [GEN$A1:A100].F1,"
SQL = SQL & "Sum([GEN$A1:A100].F2 + [FEB$A1:A100].F2) AS FF2, "
SQL = SQL & "Sum([GEN$A1:A100].F3 + [FEB$A1:A100].F3) AS FF3,"
SQL = SQL & "Sum([GEN$A1:A100].F4 + [FEB$A1:A100].F4) AS FF4,"
SQL = SQL & "Sum([GEN$A1:A100].F5 + [FEB$A1:A100].F3) AS FF5,"
SQL = SQL & "Sum([GEN$A1:A100].F6 + [FEB$A1:A100].F4) AS FF6,"
SQL = SQL & "FF2+FF3+FF4+FF5+FF6 AS S "
SQL = SQL & "FROM [GEN$A1:A100] "
SQL = SQL & "INNER JOIN [FEB$A1:A100] ON [GEN$A1:A100].F1 =
[FEB$A1:A100].F1 "
SQL = SQL & "GROUP BY [GEN$A1:A100].F1;"
in order to run it, from Excel, with:
Set RS = xlDB.OpenRecordset(SQL, dbOpenDynaset)
Destination(2, 1).CopyFromRecordset RS
But it does'n work!
Eoor:
Two few parameters, expected 8.
The error seems to be in the four "Sum(... expression.
Any suggestion?
Bruno