cruisin
Posts: 31
|
| Posted: 11/24/2009, 5:52 AM |
|
I'm trying to create an account numbering system that mimics an older system that I converted data from. I'm trying to increment the old value which is no longer the field id and it doesn't seem to be working. This code works fine if I use the caseid field instead of max_num. Otherwise neither field gets updated. Suggestions.
----------
Dim Connection
Dim SQL
Dim SQL2
Dim RecordSet
Set Connection = New clsDBConnection1
Dim max_num
Connection.Open
max_num = CCDLookup("max(casenum)","cases","",DBconnection1)
SQL2 = "update cases set casenum = max_num where caseid =" & caseid
SQL = "update cases set casecode = (STR(YEAR(createdate)) +'-'+ STR(max_num)) where caseid =" & caseid
Set RecordSet = Connection.Execute(SQL)
|
 |
 |
Edd
Posts: 547
|
| Posted: 11/25/2009, 4:06 PM |
|
You wrote 2 SQL statements and only executed the second.
Try (if you are using MSSQL)
SQL = "update cases set casenum = (Select max(casenum) + 1 from cases) , CAST( DatePart(Year, createdate) as Char(4)) + '-' + CAST( (Select max(casenum) + 1 from cases) as varchar(7)) where caseid =" & Connection.ToSQL( caseid, ccsInteger)
Connection.Execute(SQL)
Do it in one hit
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
|
