Small problem to Small Problem

hey everyone I have a ? for ya. It probably isnt that hard but here goes. I have a field that is created from 3 different numbers, here is the data in that field :
'#DateFormat(now(),'mmyy')#-#form.projectnumber#-#next_num#'

it would read something like

idnum:
0804-0302-0001

I am trying to find a way for that #next_num# field to populate the field in the table by finding out what the next available autonumber in my num field is...do you understand what I am saying???? I have a column labeled num in the table that is an autonumber but it doesn't autonumber unless you have created the table. But I want it to attach a figure like 0001 onto the idnum column as soon as they submit the form. Also I had the problem of trying to format the numbers from 1 to 0001 and I couldnt figure that out. I tried working w/ CFSET but couldnt get it figured out. Any ideas?

You could select the maximum value of that key, and then just add 1 to it.

Keep in mind though, that if someone else inserts a record in between the time that you select the max value and the time you insert the new record, you'll be using an autonumber value that has already been inserted.

I know it isn't good practice to do this but the user requesting this form stated that there would not be very many users. I understand how to pull max(id) but if I want to use that in an insert into with

#date#-#projectnumber#-#max(num) +1# how would i do that so it would insert that number b/c I am not sure???

also I think to get 0000 format would I use decimalformat(max(num), "0000") or something ilke that right?

You'd have to do two SQL statements. One to select the max ID, and another to insert the new record (and add 1 to the previously selected max id).

You could also look into a nested select statement, but that's more complex.

ok so I think I am close but maybe not let me know if you have any ideas. I keep getting errors but this is the two queries....

<cfquery name="Insert_values" datasource="vacancy">
select max(id) max_num from dbo.vacancy_approved
<cfset next_id = max_num>
</cfquery>

<cfquery name="Insert_values" datasource="vacancy">

insert into dbo.vacancy_approved (idnum, position, programmanager, projectnumber, availability, travel, interview, start_date, type, salary, rate, temp_perm, customer, location, work, clearance, duties, experience, new)

values ('#DateFormat(now(),'mmyy')#-#form.projectnumber#-#decimalformat(next_id)#', '#form.position#', '#form.programmanager#', '#form.projectnumber#', '#form.availability#', '#form.travel#', '#form.interview#', '#form.start_date#', '#form.type#', '#form.salary#', '#form.rate#', '#form.temp_perm#', '#form.customer#', '#form.location#', '#form.work#', '#form.clearance#', '#form.duties#', '#form.experience#', '#form.new#')
</cfquery>

IT TELLS ME THAT MAX_NUM IS UNDEFINED...???

I have tried looking through the search engines multiple times for examples but dont see any that really apply for what I am trying to do. I can select the max(num) but I dont see how to insert that value into another cfquery....

In the first query you do something like:

<cfquery name="getMaxID">
select max( ID ) as theMaxID
from myTable
</cfquery>

Then do...

<cfquery name="insertNewRecord">
insert into myTable( ID, firstName, etc... )
values ( #getMaxID.theMaxID + 1#, '#form.firstName#', etc. )
</cfquery>

Kiteless - you da man. Look though, I swear my timing is just great. Right before you did that I figured out how to do it...I learned something today!! Haha. I appreciate your help though. don't get annoyed w/ me I will probably be trying to get a lot of help being that I just started w/ coldfusion.

Jack

The only thing that annoys me is when people ask questions without even trying to solve the problem. In other words, people who just expect others to spit out code for them. Since you did not do that, you have no reason to worry that I'll be annoyed.