2. ASPRunnerPro General questions
  3. Key Field / AutoNumber
This topic is locked

Key Field / AutoNumber

 		5/11/2004 12:45:36 PM
ASPRunnerPro General questions
author

In my Access database, I have set the key field to auto number with the format set to "L1304"00000. In the database it the output displays as L130400001 and follows the formating. In the ASPRunner, it only shows a number 1 instead of the full string.

Any ideas?
Sergey Kornilov admin 5/14/2004

Glenn,
I guess Access actualy stores numbers 1,2,3 ... in the database while L130400001 is just a visual representation which works in MS Access only.
If you need to display the same full number some where on the ASP page I would recommend to modify SQL query this way (I assume this field name is ID).

select

ID,

'L1304'+String(5-Len(Cstr(ID)),' ') as FullID,...

From [TableName]


You still need to use ID as a key column.
S
 		surfermac 5/15/2004

Will this SQL statement also enable you to add?
Sergey Kornilov admin 5/19/2004

You don't have to worry about adding because you use autonumber field and MS Access will increment your key field automatically.
500110 5/20/2004

I've tried every which way to make the SQL query adjustments. If you have a minute, would you look at the current query and let me know how to fit in your new quote. The ID number is "LPO number"
select [LPO number],

[Date],

[Location/Program],

[Contractor Name],

[Amount],

[Center Code],

[Account],

[Employee Name]

From [LPO LOG]
Sergey Kornilov admin 5/20/2004

Glenn,
try this:

select [LPO number],

'L1304'+String(5-Len(Cstr([LPO number])),' ') as FullID,

[Date],

[Location/Program],

[Contractor Name],

[Amount],

[Center Code],

[Account],

[Employee Name]

From [LPO LOG]


I hope this helps.
500111 5/20/2004

Almost there. I now have a new column called FullID (I sort of see where you are going with this). But all the records in this field now only say "L1304" and are not picking up the LPO number after. Hmmm.....
Sergey Kornilov admin 5/20/2004

Glenn,
please try this one. Seems to be working now.

select [LPO number],

'L1304'+String(5-Len(Cstr([LPO number])),'0')+CStr([LPO number]) as FullID,

[Date],

[Location/Program],

[Contractor Name],

[Amount],

[Center Code],

[Account],

[Employee Name]

From [LPO LOG]
500112 5/21/2004

Your query will come in very helpfull for future Intranet applications.

Thanks so much...