ColdFusion, Using Access DB
I am trying to create a project number that does appear on my submittal form and it consists of 3 parts(i.e. 4 SE-123)the 1st digit represent the year the next 2 letter are representing the department and the last 3 digits are ID autonumber from the table. The project number will be used in the search page and also to refer to corresponding word doc. I tried these codes and I get the error message. Please tell me what exactly my problem is.
<cfquery name="MaximumID" datasource="Capital2Reqs">
SELECT MAX (ID) FROM Capital2_Inputs
<cfquery name="ProjectNumber" datasource="Capital2Reqs">
SELECT (4 + '#Form.Departments#' + '-' + Max (ID)) AS Project_No FROM Capital2_Inputs
<CFQUERY NAME="UserInsert" DATASOURCE="Capital2Reqs" >
INSERT INTO Capital2_Inputs (Project_No,Div_Priority,Status,Just_Detail,Divisi
VALUES ('#Project_No#','#Form.Div_Priority#','#Form.Statu s#','#Just_Detail#','#Form.Division#','#Form.Depar tments#','#Form.Org_Number#','#Form.Org_Name#','#F orm.Director#');
THE ERROR MESSAGE IS:
ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
SQL = "SELECT (4 + 'AR' + '-' + Max (ID)) AS Project_No FROM Capital2_Inputs"
Data Source = "CAPITAL2REQS"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (52:1) to (52:56).