An append query Compile ERRORS

[darinmc]

Hi
The code below, pasted from the SQL window in Microsoft Access.
I am trying to get an append query to work, I have pasted the code below. Recently I just learnt how to do it using the docmd.RunSql method.
The below code is more complex and i'm receiving COMPILE errors.

DoCmd.RunSQL "INSERT INTO tblInvoice ( DelWend, DelClReg, DelJid, ClNo, InvNo, InvDt, Com1, Com2 )" & _
"SELECT tblPayInv.WEdate, TESTjobs.ClientRegNo, tblPayInv.JobsID, TESTclient.ClientNo, Null AS InvNo, Format([Please type in the Invoice Date, dd/mm/yyyy],"dd/mm/yyyy") AS InvDt, tblPayInv.Notes AS Com1, Null AS Com2 " & _
"FROM TESTclient INNER JOIN (tblEmployee INNER JOIN (TESTjobs INNER JOIN tblPayInv ON TESTjobs.JobsID = tblPayInv.JobsID) ON tblEmployee.EmpRegNo = tblPayInv.EmpRegNo) ON TESTclient.ClientRegNo = TESTjobs.ClientRegNo " & _
"GROUP BY tblPayInv.WEdate, TESTjobs.ClientRegNo, tblPayInv.JobsID, TESTclient.ClientNo, Null, Format([Please type in the Invoice Date, dd/mm/yyyy],"dd/mm/yyyy"), tblPayInv.Notes, Null, Null, Left(UCase([EmpForename] & "_" & [EmpSurname] & "_" & [EmpNo]),30), tblPayInv.OT1InvCharge, -1, 0, [EmpForename] & " " & [EmpSurname] & " - " & "O/Time 1", Null, "Product Invoice", Null, Null, Null, Null, Null, Null, 0 " & _
"HAVING (((tblPayInv.WEdate) = [dd/mm/yyyy]) And ((Sum(tblPayInv.Act6)) > 0)) " & _
"ORDER BY TESTclient.ClientNo, Left(UCase([EmpForename] & "_" & [EmpSurname] & "_" & [EmpNo]),30);"

Thx... I hope ur able to help
DArin

 

[darinmc]

Sorry, I forgot to say, the compile errors were around the date
eg "dd/mm/yyyy"
"_" Which i use as part of product code

I didnt manage to go much further...

I would really appreciate if some1 could look and correct this code..
Thx
Darin

 

[TheAceMan1]

How are ya darinmc . . .

Missing space:

[blue]"INSERT INTO tblInvoice ( DelWend, DelClReg, DelJid, ClNo, InvNo, InvDt, Com1, Com2 )[COLOR=black red] [/color]" & _[/blue]

As for:

[blue]Format([[purple][b]Please type in the Invoice Date[/b][/purple], dd/mm/yyyy],"dd/mm/yyyy") AS InvDt[/blue]

Get the date thru an inputbox, format it and use that instead. In any case this has to be resolved before you move on.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Eupher]

Also, remember that anything enclosed in double quotes is part of the literal string. So functions, variables, and such must be correctly referenced and concatenated into the string. For instance:

"...Null, Format([Please type in the Invoice Date, dd/mm/yyyy],"dd/mm/yyyy"),..."

will always throw an error, because the Format function is not recognized as a function by the compiler, it's just part of the string - and the quotes around the format argument serve to delimit the string with no contatenation operator. Instead:

"...Null, "[highlight] & [/highlight]Format([Please type in the Invoice Date, dd/mm/yyyy],[highlight]'[/highlight]dd/mm/yyyy[highlight]'[/highlight])[highlight] & [/highlight]", ..."

AceMan's advice is also good, I would avoid an input parameter in code, better to use the InputBox function.

HTH,

Ken S.

 

[Eupher]

Or to demonstrate it another way:

[highlight cyan]"[/highlight][highlight]...Null, Format([Please type in the Invoice Date, dd/mm/yyyy],[/highlight][highlight cyan]"[/highlight]dd/mm/yyyy[highlight cyan]"[/highlight][highlight]),...[/highlight][highlight cyan]"[/highlight]

The compiler sees everything highlighted in yellow above as literal string data, delimited by the double quotes in cyan. See the problem?

Ken S.

 

[darinmc]

Thx Ken and TheAceMan1, I will try it out, sound advice... I do intend to take the invoice date from a form.
Darin