regarding autonumber as custom output result

[naina]

hi,
I've a Table with auto number field[auditid] , it generate 1 2 3 ..
Now, I'd like to represent 00001 00002 00003 instead of 1 2 3 as a output result.
that's why I'm trying this

select right(str("0000" & [auditid]),4) from AuditTrailT

Result shows

"Invalid column name '0000'."
would you please guide me to write the actual syntax for achieving the result

 

[dkinley]

Format([AuditID],00000)

-dK

 

[naina]

Thanks Dk

When I run this
select Format([AuditID],00000) from AuditTrailT

en error msg shows

'Format' is not a recognized function name.

 

[dkinley]

Oops, forgot the quotes, but you have to add an alias for it to work ....

For instance:

In the QBE it would look like:
AuditNum: Format([AuditID],"00000")

In SQL it would be like:

SELECT Format([AuditID],"00000") As AuditNum FROM AuditTrailT

-dK

 

[naina]

same error when run this in Sql

SELECT Format([AuditID],"00000") As AuditNum FROM AuditTrailT

Note: My Application is Based on A2003 ADP & Sql 2000 Server.

 

[vbaInet]

I would advise your query returns the raw data and you perform the formatting on the control's Format property.

 

[naina]

thanks vbaInet
But how to do this would you please ...

 

[vbaInet]

dk gave you the format you need, just put the zeros in the Format property of the control.

 

[naina]

Thanks vbaInet

It turns the Table filed Value 00001 00002 ..
But problem is When I Run the Sql Query to get this value
it shows '1' not '00001'

Select Query

SELECT AuditID As AuditNum FROM AuditTrailT

 

[Khalid_Afridi]

Hi naina!
Autonumber Field has unique id which can not be changed. its work like a primary keys. you can Add Prefix to autonumber field.

 

[naina]

thanks Khalid
Problem is not changing table field value . Table field is auto number it will generate 1 2 3 4 ... or if I Use Format 0000 then it will generate 00001 00002 00003 ..No problem With this

but I would like to get the value as 00001 not 1 via select Query which is
used for combobox row source.

 

[Khalid_Afridi]

naina!

As I said Auto Number field value you can not change, it has very important role in access while using them as a primary key, relationships and data integrity.

you can use text Field with No Duplicate to Yes and can chose what ever the format you want. you can use running sum of records on form and reports as follows:
001
002
003
004

I am not clear what you want exactly, can you show your query?

 

[dkinley]

same error when run this in Sql

SELECT Format([AuditID],"00000") As AuditNum FROM AuditTrailT

Note: My Application is Based on A2003 ADP & Sql 2000 Server.

Hmmm. I am not sure why you would get this error. I am not sure which reference the format command is from but should be find with standard references when project was created ....

Well, these SQL functions might provide some relief: http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

As with vbaInet's post, the only time I use this in a query is if I am exporting the data, in forms or reports I only format at the control level, not the query level.

-dK

 

[vbaInet]

but I would like to get the value as 00001 not 1 via select Query which is used for combobox row source.

You are not applying the format property correctly on the combo box. If a format is set on the COMBO BOX via its FORMAT property then the value would be formatted. See the attached imaged on where to put the FORMAT.

Note that the Value of the combo box would return the underlying value and not the formatted value. The formatted string could be derived from the Text property.

That is, if the combo box is showing 000001 and you do Combo1.Value you would get 1, if the combo box has focus and you do Combo1.Text you would get 000001.

If you still want to go ahead with formatting on query level, then dk's last post contains a link for that. Table level, Khalid gave you a link for that too.

 

[gemma-the-husky]

naina

the thing is, an autonumber is a number

we write numbers without leading zeroes, and without trailing zeroes, after the last significant digit

so a number is stored as, say 12, not as 012, 0012, 00012 etc -in fact all these are the same (just 12)

by the same token, we store
12.5, not 12,50, 12,5000, 12,50000 etc

------------------------
now when we come to display the number, or use it, we can use the format function in a form or report's control to present this data differently

we can use format mynumber, "0000000.00" to force display of any number of leading or trailing zeroes. and we can use settings like "no of decimals" to force alignment of decimal values.

However, bear in mind, that although it is normal to align decimal points to a fixed number of places, it is more perverse to show leading zeroes. If the leading zeroes are significant (say they form part of a product number) then REALLY you should be storing this data as a STRING/TEXT, and not relying on formatting an autonumber to give you the presentation.

 

[naina]

Value of the combo box would return the underlying value and not the formatted value. The formatted string could be derived from the Text property.

That is, if the combo box is showing 000001 and you do Combo1.Value you would get 1, if the combo box has focus and you do Combo1.Text you would get 000001.

Hi Thanks to Everyone for excellent co-operation.
And one more question for vbaInet regarding the above Quote
is it a limitation of MSAccess or SQL ?

 

[vbaInet]

No it isn't a limitation of any sort.

The Text property refers to the visual string that you can see. The format property of a control formats a string for visual representation. The Value property returns the True nature of the underlying data (without the format). If the formatting was applied at query level then the Value property will return the formatted value, which is the underlying data.

 

[naina]

No it isn't a limitation of any sort.
If the formatting was applied at query level then the Value property will return the formatted value, which is the underlying data.

Would you please give an example of query level formatting for this context

 

[vbaInet]

dk provided you with a link to the SQL equivalent of vb's Format function. What you're looking for is an SQL Server specific function (from what I gathered in one of your posts). It's not easy to do in SQL Server which was why we advised you do it on Control level. If you seek that then you could post your question in the SQL Server section of the forum.

In the meantime, have a look at this:
http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html

 

[naina]

Hi,
at last I got the solution, Here is the Query

SELECT RIGHT('00000' + CONVERT(varchar(5), [auditid]), 5) as AuditNum FROM AuditTrailT

And thanks to Every one


<<<<<<<Naina>>>>>>>>>>