Error in the following code:

sql = "INSERT INTO SPCCalculator ( [Value], [Date], [Description] ) "

sql = sql + "SELECT (CDPTL_Activity.CDPTL_DateAdmitted-CDPTL_Activity.CDPTL_DecisionToAdmitDate)/" + CStr(TimeDivider) + " AS [Value], CDPTL_Activity.CDPTL_DateAdmitted as [Date],Description as [Description]"
sql = sql + "FROM CDPTL_Activity "
sql = sql + "where cdptl_procedurecode in "
sql = sql + "(SELECT CodeGroupMembers.[OPCS no dot] "
sql = sql + "FROM CodeGroupMembers INNER JOIN CodeGroupNames ON CodeGroupMembers.CodeGroupID = CodeGroupNames.CodeGroupID "
sql = sql + "where CodeGroupNames.CodeGroupID = "
sql = sql + CStr(cmbOPCS.value)
sql = sql + ")"
sql = sql + " AND CDPTL_Activity.CDPTL_SpecialtySplit Like """
sql = sql + cmbSpecialtySplit.value
sql = sql + """ AND CDPTL_Activity.CDPTL_SpecialtyGroup Like """
sql = sql + cmbSpecialtyGroup.value
sql = sql + """ "
sql = sql + DtRange
sql = sql + IntMgtFilter
sql = sql + "order by cdptl_DateAdmitted ASC;"

DoEvents

db.Execute (sql)

Any ideas.

Many Thanks

Tim

__________________
Tim

its the "AS" in

SELECT fieldname AS value

That is what causes the error you reported.
ie use

SELECT field

in the INSERT part just put the corresponding field names there
ie

INSERT INTO tablename (field1,field2,field3)

I know this because i tried something similar last week.
But then again you might know something i dont.

***********************************
If for some reason that does not work i would debug it by:
1) Printing out the sql to the immediate window
     debug.print sql
2) Run the sql in the immediate window
         docmd.runquery(".....")
3) Make alterations to the sql until it works or run individual
     portions of the SQL statement

Note: any quotes (") in your sql have to be altered to (""") or (')

I generally find it best to start off basic so that the sql works and then successively build up the whole sql.

Thanks Mate,

Will give it a try!!



Tim