Run-time error 3075

[HS_1]

I wonder if you would be willing to help me one more time with the database I inherited
I ma trying to create table, add the values and sum totals on click event. Everything works up to summing then I am getting the message:
Run-time error 3075
Syntax error (missing operator) in query expression
‘Sum(tblCWP_PercentComplete.3DCadd complete)’.

Would be the number "3" which stops me executing the command and if so how to fix it.
Below is part of the code which doesn't work.
Thanks

' Add Totals line at the bottom
strsql = "SELECT Sum(tblCWP_PercentComplete.Total) AS SumOfTotal, Sum(tblCWP_PercentComplete.Assembled) AS SumOfAssembled, " & _
"Sum(tblCWP_PercentComplete.Reset) AS SumOfReset, Sum(tblCWP_PercentComplete.ReDesignRequired) AS SumOfReDesignRequired, " & _
"Sum(tblCWP_PercentComplete.DesignComplete) AS SumOfDesignComplete, Sum(tblCWP_PercentComplete.3DCaddComplete) AS SumOf3DCaddComplete " & _
"Sum(tblCWP_PercentComplete.ReCaddRequired) AS SumOfReCaddRequired, Sum(tblCWP_PercentComplete.2DCaddComplete) AS SumOf2DCaddComplete " & _
"Sum(tblCWP_PercentComplete.QCReviewComplete) AS SumOfQCReviewComplete, Sum(tblCWP_PercentComplete.CheckingComplete) AS SumOfCheckingComplete " & _
"Sum(tblCWP_PercentComplete.IFCPending) AS SumOfIFCPending, Sum(tblCWP_PercentComplete.IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete;"
intTotals = OpenRecordSet(rsTotals, strsql)


If intTotals > 0 Then


dblPercentComplete = ((rsTotals.Fields("SumOfConfirmed") * dblConfirmedPercent) _
+ (rsTotals.Fields("SumOfAssembled") * dblPackageAssembledPercent) _
+ (rsTotals.Fields("SumOfReset") * dblResetPercent) _
+ (rsTotals.Fields("SumOfReDesignRequired") * dblReDesignRequiredPercent) _
+ (rsTotals.Fields("SumOfDesignComplete") * dblDesignCompletePercent) _
+ (rsTotals.Fields("SumOf3DCaddComplete") * dbl3DCaddCompletePercent) _
+ (rsTotals.Fields("SumOfReCaddRequired") * dblReCaddRequiredPercent) _
+ (rsTotals.Fields("SumOf2DCaddComplete") * dbl2DCaddCompletePercent) _
+ (rsTotals.Fields("SumOfQCReviewComplete") * dblQCReviewCompletePercent) _
+ (rsTotals.Fields("SumOfCheckingComplete") * dblCheckingCompletePercent) _
+ (rsTotals.Fields("SumOfIFCPending") * dblIFCPendingPercent) _
+ (rsTotals.Fields("SumOfIFC") * dblIFCPercent)) _
/ CInt(rsTotals.Fields("SumOfTotal"))
Else
dblPercentComplete = 0
End If
strsql = "INSERT INTO tblCWP_PercentComplete (EHT_EWP, Total, Confirmed, Assembled, Reset, ReDesignRequired, DesignComplete, 3DCaddComplete, " & _
"ReCaddRequired, 2DCaddComplete, QCReviewComplete, CheckingComplete, IFCPending, IFC, PercentComplete) " & _
"SELECT 'TOTAL', " & rsTotals.Fields("SumOfTotal") & ", " & _
rsTotals.Fields("SumOfConfirmed") & ", " & rsTotals.Fields("SumOfAssembled") & ", " & _
rsTotals.Fields("SumOfReset") & ", " & rsTotals.Fields("SumOfReDesignRequired") & ", " & _
rsTotals.Fields("SumOfDesignComplete") & ", " & rsTotals.Fields("SumOf3DCaddComplete") & ", " & _
rsTotals.Fields("SumOfReCaddRequired") & ", " & rsTotals.Fields("SumOf2DCaddComplete") & ", " & _
rsTotals.Fields("SumOfQCReviewComplete") & ", " & rsTotals.Fields("SumOfCheckingComplete") & ", " & _
rsTotals.Fields("SumOfIFCPending") & ", " & _
rsTotals.Fields("SumOfIFC") & ", " & Format(dblPercentComplete, "0.00") & ";"
CurrentDb.Execute strsql

DoCmd.OpenTable "tblCWP_PercentComplete"


End Sub

[CJ_London]

you have names starting with a number which is probably the reason - see this link for the rationale and discussion http://stackoverflow.com/questions/1...t-with-numeric

It may be solvable by using square brackets around the name, but better to change the name

[HS_1]

Ajax
Thanks for your help, the number was also my "suspect" but with my limited knowledge of vba I wasn't sure. Looks like renaming the fields is the right solution as you suggest.
HS_1

[CJ_London]

it's not so much vba (tho' not advisable there either) as sql - you are just using vba to create a sql statement

[HS_1]

it's not so much vba (tho' not advisable there either) as sql - you are just using vba to create a sql statement

Ajax sorry for bothering you again. I have renamed the fields as you suggested but new error message appeared.

The SELECT statement includes a reserved word or an argument
Name that is misspelled or misusing, or the punctuation isincorrect.

I am looking at it for hours but can't find a mistake, do you think the "Total" would be the problem?
Thanks




' Add Totals line at the bottom
strsql = "SELECT Sum(tblCWP_PercentComplete.Total) AS SumOfTotal, Sum(tblCWP_PercentComplete.Assembled) AS SumOfAssembled, " & _
"Sum(tblCWP_PercentComplete.Reset) AS SumOfReset, Sum(tblCWP_PercentComplete.ReDesignRequired) AS SumOfReDesignRequired, " & _
"Sum(tblCWP_PercentComplete.DesignComplete) AS SumOfDesignComplete, Sum(tblCWP_PercentComplete.Cadd3DComplete) AS SumOfCadd3DComplete " & _
"Sum(tblCWP_PercentComplete.ReCaddRequired) AS SumOfReCaddRequired, Sum(tblCWP_PercentComplete.Cadd2DComplete) AS SumOfCadd2DComplete " & _
"Sum(tblCWP_PercentComplete.QCReviewComplete) AS SumOfQCReviewComplete, Sum(tblCWP_PercentComplete.CheckingComplete) AS SumOfCheckingComplete " & _
"Sum(tblCWP_PercentComplete.IFCPending) AS SumOfIFCPending, Sum(tblCWP_PercentComplete.IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete;"
intTotals = OpenRecordSet(rsTotals, strsql)
If intTotals > 0 Then
dblPercentComplete = ((rsTotals.Fields("SumOfConfirmed") * dblConfirmedPercent) _
+ (rsTotals.Fields("SumOfAssembled") * dblPackageAssembledPercent) _
+ (rsTotals.Fields("SumOfReset") * dblResetPercent) _
+ (rsTotals.Fields("SumOfReDesignRequired") * dblReDesignRequiredPercent) _
+ (rsTotals.Fields("SumOfDesignComplete") * dblDesignCompletePercent) _
+ (rsTotals.Fields("SumOfCadd3DComplete") * dblCadd3DCompletePercent) _
+ (rsTotals.Fields("SumOfReCaddRequired") * dblReCaddRequiredPercent) _
+ (rsTotals.Fields("SumOfCadd2DComplete") * dblCadd2DCompletePercent) _
+ (rsTotals.Fields("SumOfQCReviewComplete") * dblQCReviewCompletePercent) _
+ (rsTotals.Fields("SumOfCheckingComplete") * dblCheckingCompletePercent) _
+ (rsTotals.Fields("SumOfIFCPending") * dblIFCPendingPercent) _
+ (rsTotals.Fields("SumOfIFC") * dblIFCPercent)) _
/ CInt(rsTotals.Fields("SumOfTotal"))
Else
dblPercentComplete = 0
End If
strsql = "INSERT INTO tblCWP_PercentComplete (EHT_EWP, Total, Confirmed, Assembled, Reset, ReDesignRequired, DesignComplete, Cadd3DComplete, " & _
"ReCaddRequired, Cadd2DComplete, QCReviewComplete, CheckingComplete, IFCPending, IFC, PercentComplete) " & _
"SELECT 'TOTAL', " & rsTotals.Fields("SumOfTotal") & ", " & _
rsTotals.Fields("SumOfConfirmed") & ", " & rsTotals.Fields("SumOfAssembled") & ", " & _
rsTotals.Fields("SumOfReset") & ", " & rsTotals.Fields("SumOfReDesignRequired") & ", " & _
rsTotals.Fields("SumOfDesignComplete") & ", " & rsTotals.Fields("SumOfCadd3DComplete") & ", " & _
rsTotals.Fields("SumOfReCaddRequired") & ", " & rsTotals.Fields("SumOfCadd2DComplete") & ", " & _
rsTotals.Fields("SumOfQCReviewComplete") & ", " & rsTotals.Fields("SumOfCheckingComplete") & ", " & _
rsTotals.Fields("SumOfIFCPending") & ", " & _
rsTotals.Fields("SumOfIFC") & ", " & Format(dblPercentComplete, "0.00") & ";"
CurrentDb.Execute strsql

DoCmd.OpenTable "tblCWP_PercentComplete"
End Sub

[davegri]

Quick look finds 3 missing commas causing syntax errors:

strsql = "SELECT Sum(tblCWP_PercentComplete.Total) AS SumOfTotal, Sum(tblCWP_PercentComplete.Assembled) AS SumOfAssembled, " & _
"Sum(tblCWP_PercentComplete.Reset) AS SumOfReset, Sum(tblCWP_PercentComplete.ReDesignRequired) AS SumOfReDesignRequired, " & _
"Sum(tblCWP_PercentComplete.DesignComplete) AS SumOfDesignComplete, Sum(tblCWP_PercentComplete.Cadd3DComplete) AS SumOfCadd3DComplete, " & _
"Sum(tblCWP_PercentComplete.ReCaddRequired) AS SumOfReCaddRequired, Sum(tblCWP_PercentComplete.Cadd2DComplete) AS SumOfCadd2DComplete, " & _
"Sum(tblCWP_PercentComplete.QCReviewComplete) AS SumOfQCReviewComplete, Sum(tblCWP_PercentComplete.CheckingComplete) AS SumOfCheckingComplete, " & _
"Sum(tblCWP_PercentComplete.IFCPending) AS SumOfIFCPending, Sum(tblCWP_PercentComplete.IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete;"

[HS_1]

Thanks davegri, and Ajax without your help I wouldn't be able to figure this out, finally it works as it suppose to
HS_1

[CJ_London]

some tips for the future to make you code more readable, shorter and quicker

1. in vba coding use indents - and when presenting to others to help, preserve that indenting by surrounding the posted code with the code tags (the # button)
2. in sql don't use such long names for tables - and if you have to, alias them - as an example this

...
...
"Sum(tblCWP_PercentComplete.IFCPending) AS SumOfIFCPending, Sum(tblCWP_PercentComplete.IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete;"

is reduced to

...
...
"Sum(PC.IFCPending) AS SumOfIFCPending, Sum(PC.IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete PC;"

and in fact, on this occasion since there is only one table (or more precisely, there is no question over where a field name comes from) you don't need to identify the table

...
...
"Sum(IFCPending) AS SumOfIFCPending, Sum(IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete;"

3. use With so you don't have to keep referring to the object

so this

rsTotals.Fields("SumOfConfirmed") & ", " & rsTotals.Fields("SumOfAssembled") & ", " & _
rsTotals.Fields("SumOfReset") & ", " & rsTotals.Fields("SumOfReDesignRequired") & ", " & _

becomes

with rsTotals
   !SumOfConfirmed & ", " & !SumOfAssembled & ", " & _
   !SumOfReset & ", " & !SumOfReDesignRequired & ", " & _
   ...
   ...
end with

[HS_1]

Ajax thanks for the helpful tips, I really appreciate your help and advice.
HS_1

some tips for the future to make you code more readable, shorter and quicker

1. in vba coding use indents - and when presenting to others to help, preserve that indenting by surrounding the posted code with the code tags (the # button)
2. in sql don't use such long names for tables - and if you have to, alias them - as an example this

...
...
"Sum(tblCWP_PercentComplete.IFCPending) AS SumOfIFCPending, Sum(tblCWP_PercentComplete.IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete;"

is reduced to

...
...
"Sum(PC.IFCPending) AS SumOfIFCPending, Sum(PC.IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete PC;"

and in fact, on this occasion since there is only one table (or more precisely, there is no question over where a field name comes from) you don't need to identify the table

...
...
"Sum(IFCPending) AS SumOfIFCPending, Sum(IFC) AS SumOfIFC " & _
"FROM tblCWP_PercentComplete;"

3. use With so you don't have to keep referring to the object

so this

rsTotals.Fields("SumOfConfirmed") & ", " & rsTotals.Fields("SumOfAssembled") & ", " & _
rsTotals.Fields("SumOfReset") & ", " & rsTotals.Fields("SumOfReDesignRequired") & ", " & _

becomes

with rsTotals
   !SumOfConfirmed & ", " & !SumOfAssembled & ", " & _
   !SumOfReset & ", " & !SumOfReDesignRequired & ", " & _
   ...
   ...
end with