Need to pad-fill a counter 1

[lisaharris]

I have a daily table that is created by a make-table query from entries into a master table, and it needs to be numbered each day starting at 1. The numbers must have 0's filling the field to 7 bytes, such as 0000001, 0000002, etc. There are usually 2-300 records in the daily table.

Is there a way to accomplish this?

__________
It's better to have loved and lost than live with the idiot for the rest of your life.

 

[lespaul]

in your make table query maybe:

FORMAT(FieldName, "0000000")

the FORMAT function may actually be: format, fieldname

HTH




Leslie

Have you met Hardy Heron?

 

[Golom]

Do you want those leading zeros stored in the table or do you just want to see them when you retrieve the data?

If you want them in the table then you will need to use a text field. Numeric fields (e.g. single, double, long, etc.) do not store leading zeros.

Since relational databases have no notion of intrinsic ordering of records, most processes to do this are multi-step activities. Here are a few ideas ...

#1
- Add an autonumber to the table after you run the MAKE TABLE.

ALTER TABLE NewTable ADD FIELD aNum Counter(1,1)

- Write a query to display the records with leading zeros

Select Format(aNum, "0000000") As RecNum, ...

#2
- If there us a unique field in the new table then write a Rank query

SELECT A.UniqueField, 

       Format((Select Count(*)+1 
               From NewTable As B 
               Where B.UniqueField < A.UniqueField),"0000000") AS RecNum

FROM NewTable AS A

 

[lisaharris]

Golom - your #2 solution is just what I needed!! Thank you so much!!

__________
It's better to have loved and lost than live with the idiot for the rest of your life.