ColdFusion, Using Access DB

Hello There
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>

<cfquery name="ProjectNumber" datasource="Capital2Reqs">
SELECT (4 + '#Form.Departments#' + '-' + Max (ID)) AS Project_No FROM Capital2_Inputs
</cfquery>

<CFQUERY NAME="UserInsert" DATASOURCE="Capital2Reqs" >
INSERT INTO Capital2_Inputs (Project_No,Div_Priority,Status,Just_Detail,Divisi
on,Departments,Org_Number,Org_Name,Director)
VALUES ('#Project_No#','#Form.Div_Priority#','#Form.Status#','#Just_Detail#','#Form.Division#','#Form.Depar tments#','#Form.Org_Number#','#Form.Org_Name#','#Form.Director#');
</CFQUERY>

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
Thanks
Sheri

You're trying to concantenate elements of different data types. You'll need to play with quote, etc. to get it to work. Maybe something like:

<cfquery name="ProjectNumber" datasource="Capital2Reqs">
SELECT ('4 ' + '#Form.Departments#' + '-' + Max(ID)) AS Project_No FROM Capital2_Inputs
</cfquery>

Also the ID field for which you are selecting the maximum value will also need to be a string I believe. Or are you really trying to insert the value from the first query into the second one? In that case try something like:

<cfquery name="MaximumID" datasource="Capital2Reqs">
SELECT MAX(ID) as maxID FROM Capital2_Inputs
</cfquery>

<cfquery name="ProjectNumber" datasource="Capital2Reqs">
SELECT ('4 ' + '#Form.Departments#' + '-' + '#maximumID.maxID#') AS Project_No FROM Capital2_Inputs
</cfquery>

Should I make a new table for ID only to make sure the maxID that I pull out is the same as the maxID in the project_No. If I do it with 2 different tables, then I do not need that maximumID query, and I'll be using this query only
<cfquery name="ProjectNumber" datasource="Capital2Reqs">
SELECT ('4 ' + '#Form.Departments#' + '-' + 'max(ID)') AS Project_No FROM Capital2_Inputs
</cfquery>
If I have the ID (autonumber) in 2nd table, then what do I need to change in my code making the project_No.

Thanks
Sheri

I'm missing the reason why you would need a second table to compare the max id to. If you want to select the highest id field from the captial2_inputs table and use it, you don't need another table. But you do either need a second query to get the max id first, or you need to convert the id to a string before you can use it in the concatenation. This is a guess since I don't have Access to test it on, but maybe something like:

<cfquery name="ProjectNumber" datasource="Capital2Reqs">
SELECT ('4 ' + '#Form.Departments#' + '-' + toString(max(ID))) AS Project_No FROM Capital2_Inputs
</cfquery>

That assumes that access has a toString() function or something similar.

Kiteless
Thanks for your replies. Let me start the questions from the beginning. I do not include any codes because everything I tried I ended with error message. I need to create Project_No and Just_Detail. Both of these fields do not appear in the submittal form, but they will be in the editform and the search page. Project number consistes of 3 parts(i.e. 4SE-123), and Just_Detail should be linked to word document (of detailed justification) for each project number. There should be 2 options for Just_Detail in the the search page 1) EDIT; if there is a word doc already made for that project number, or
2) CREATE if there is none, there should be a blank form that the user can go and fill it out then
The search page should include these( view and edit for project no should be linked to printform and edit form & Create and edit for Justification Detail should be linked to existing and blank word doc form).

Project Number : 12345 View Only Edit Only
Justification Detail : 12345.doc Create Only Edit Only

Would you please tell me what would your approach be to have both of these, also the codes that you would be using.

Thanks a lot,
Sheri