Syntax Error in Code

[pattynewbie]

I'm new to access code and I have the following query that works but I need to put it in code. The follow query works but my code has syntax errors and I'm not quite sure how to
fix it.

Any help would be appreciated.

(Query)

INSERT INTO TSumActHrs ( SumOfHr, CN, TargetCode )
SELECT Sum(ENGHOURS.Hr) AS SumOfHr, ENGHOURS.CN, IIf([dcl]="B",5,0) AS TargetCode
FROM ENGHOURS
WHERE (((ENGHOURS.DCL)="B") AND ((ENGHOURS.CC)=1))
GROUP BY ENGHOURS.CN, IIf([dcl]="B",5,0)
HAVING (((ENGHOURS.CN)=[me].[jobnumber]));

(Code)

strSQL = "INSERT INTO TSumActHrs ( SumOfHr, CN, TargetCode )" _
& " SELECT Sum(ENGHOURS.Hr) AS SumOfHr, ENGHOURS.CN, IIf([dcl]='B',5,0 AS TargetCode" _
& " FROM ENGHOURS" _
& " WHERE (ENGHOURS.DCL)='B' And ((ENGHOURS.CC) = 1))" _
& " GROUP BY ENGHOURS.CN, IIf([dcl]= 'B',5,0)" _
& " HAVING (ENGHOURS.CN)= '" & Me.Jobnumber & "';"

db.Execute strSQL

 

[HarleyQuinn]

Just missing a parenthesees (at first glance)

strSQL = "INSERT INTO TSumActHrs ( SumOfHr, CN, TargetCode )" _
  & " SELECT Sum(ENGHOURS.Hr) AS SumOfHr, ENGHOURS.CN, IIf([dcl]='B',5,0[red])[/red] AS TargetCode" _
  & " FROM ENGHOURS" _
  & " WHERE (ENGHOURS.DCL)='B' And ((ENGHOURS.CC) = 1))" _
  & " GROUP BY ENGHOURS.CN, IIf([dcl]= 'B',5,0)" _
  & " HAVING (ENGHOURS.CN)= '" & Me.Jobnumber & "';"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[dhookom]

I think I found the missing paren. There was an extra after =1:

strSQL = "INSERT INTO TSumActHrs ( SumOfHr, CN, TargetCode )" _
  & " SELECT Sum(ENGHOURS.Hr) AS SumOfHr, ENGHOURS.CN, IIf([dcl]='B',5,0) AS TargetCode" _
  & " FROM ENGHOURS" _
  & " WHERE (ENGHOURS.DCL)='B' And ((ENGHOURS.CC) = 1)[red][b])[/b][/red]" _
  & " GROUP BY ENGHOURS.CN, IIf([dcl]= 'B',5,0)" _
  & " HAVING (ENGHOURS.CN)= '" & Me.Jobnumber & "';"

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Anyway, why not simply this ?

strSQL = "INSERT INTO TSumActHrs (SumOfHr, CN, TargetCode)" _
  & " SELECT Sum(Hr),CN,5" _
  & " FROM ENGHOURS" _
  & " WHERE DCL='B' AND CC=1 AND CN='" & Me!Jobnumber & "'" _
  & " GROUP BY CN"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pattynewbie]

Suggestions worked great. I tryed both and I liked the simplified one best.

Thanks!