Append Query

[RickyAP]

I need some SQL help Have an Access database with an append query. Everything works fine until it is deployed then Query PCQ3 will not run. Any help on writing the vb code to do this chore would be appreciated. This portion of the database can be found at http://www.northshoregolfcar.com/Access or I can be emailed at Ricky@NorthShoreGolfCar.com.
Thanks in advance

If it matters its going on XP machines
It works fine on the machine it was created on it's 2000, someone suggested checking dll files but I don't know how to find out what it needs, tried to write an INSERT INTO statement, but that failed horribly, Any ideas?

 

[RichO]

I have Access 2000 and XP and I couldn't open your DB on my machine.

Are you able to successfully make an append query in query mode?

If so, copy the SQL statement from that query and paste it into VBA, and then break it up into manageable strings. That's a good way to build a SQL statement.

 

[RickyAP]

Yes it was written in Access 2003, I tried to put the SQL statement into VB
but it did not work. I'm not very good with SQL Here is the Query in SQL
INSERT INTO PERCOMP ( Job_ID, Location, Week, Cost_Code, SumOfHours, ID )
SELECT DISTINCTROW PCQ2.Job_ID, PCQ2.Location, Format(Forms!INPC2!EDate,"ww\,yyyy") AS Week, PCQ2.Cost_Code, Sum(PCQ2.Hours) AS SumOfHours, PCQ2.ID AS Rn
FROM PCQ2
GROUP BY PCQ2.Job_ID, PCQ2.Location, Format(Forms!INPC2!EDate,"ww\,yyyy"), PCQ2.Cost_Code, PCQ2.ID;

PCQ2 is a Query if the table Input_Time

 

[RichO]

Right away I noticed a couple syntax errors and expression errors...

INSERT INTO PERCOMP ( Job_ID, Location, Week, Cost_Code, SumOfHours, ID )
SELECT DISTINCTROW PCQ2.Job_ID, PCQ2.Location, Format([b][Forms]![INPC2]![EDate][/b],"ww\,yyyy") AS [b]Expr1[/b], PCQ2.Cost_Code, Sum(PCQ2.Hours) AS [b]Expr2[/b], PCQ2.ID
FROM PCQ2
GROUP BY PCQ2.Job_ID, PCQ2.Location, Format([b][Forms]![INPC2]![EDate][/b],"ww\,yyyy"), PCQ2.Cost_Code, PCQ2.ID;

The expression Sum(PCQ2.Hours) looks suspicious. I haven't yet used a query for summing so it may take another forum member to help you if that gives you a problem.

Hope that helps a bit...

 

[RickyAP]

Thanks Rich

It was the reference to a couple of dll files, thanks for your help