Creaing Dynamic generic Cust ID

srichary · November 2nd, 2004, 10:14 AM

I am trying to create this customer ID which has some parameters
contatinated like
WD as the first two parameters then the next two are location name which is numeric and then the next two are center id which is numeric and then the sequence id which incremented with each new record inserted .
NOw can any one please tell me the code or logic to do it

MY Id will look something like this
WD217 ,WD218,

CAUTION: except for WD all other parameters are retreived from database

happygv · November 2nd, 2004, 05:25 PM

Hi Srichary,

Are you extracting the LocationId, CenterId and SequenceID all based on some criteria? If so what is that? You can use something like this.

Code:

SELECT 'WD' + cast(LocationId as varchar(5)) + cast(CenterId as varchar(5)) + 
    (select Cast(Coalesce(1,max(SequenceId)) as varchar(5)) 
    FROM TABLENAME WHERE criteria_if_required) 
from TABLENAME WHERE criteria_if_required

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

srichary · November 3rd, 2004, 04:56 AM

Hey Vijay,
Thanks .You know once it gets used to it becomes easier;) well i could figure out how to do it.
heres the logic....
wlkid contains the DB value of the max ID and lid is the location id and cenid is center id

if len(walkid) = 1 then
wkid="WD"& lid & cenid & "00000" & walkid
elseif len(walkid) = 2 then
wkid="WD"& lid & cenid & "0000" & walkid
elseif len(walkid) = 3 then
wkid="WD"& lid & cenid & "000" & walkid
elseif len(walkid) = 4 then
wkid="WD"& lid & cenid & "00" & walkid
elseif len(walkid) = 5 then
wkid="WD"& lid & cenid & "0" & walkid
elseif len(walkid) = 6 then
wkid="WD"& lid & cenid & walkid
end if

happygv · November 9th, 2004, 08:54 AM

Hi Srichary,

Glad that you solved that. But you didnot mention about fixing ZEROs based on the length of WALKID, else the one I posted would be an easier solution. Still I wasn't sure if you are using ACCESS/MS SQL server. My solution could work only for SQL server, not for MS Access.

Cheers!

_________________________
- Vijay G
Strive for Perfection