Need to create an incremental sequence number

[Guest]

I am creating a query that needs to produce a fixed-length flat file to be
uploaded into banking software.

According to the file criteria, I need to create the following for each
individual record:

Field Size - 6 chars
Field Type - Number
Formatted - "000000"

Description: This number is used as the file identifier. It should be
unique for each transfer. For example, the first transfer would be "000001"
and the second transfer would be "000002".

I thought of using Autonumber - But I cannot figure out how to have the
leading 0's. I am really stumped how to achieve this. Can anyone give some
suggestions??

Thanks

 

[RuralGuy]

I am creating a query that needs to produce a fixed-length flat file to be
uploaded into banking software.

According to the file criteria, I need to create the following for each
individual record:

Field Size - 6 chars
Field Type - Number
Formatted - "000000"

Description: This number is used as the file identifier. It should be
unique for each transfer. For example, the first transfer would be "000001"
and the second transfer would be "000002".

I thought of using Autonumber - But I cannot figure out how to have the
leading 0's. I am really stumped how to achieve this. Can anyone give some
suggestions??

Thanks

I would keep the number in a system file and increment it every time I used it.
Format([YourNumber],"000000") will give you a 6 digit number.
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Ron2006]

DONT use autonumber.


If you ever had to rerun the process, you will have a problem because
the autonumber will have been incremented. If it is a field on a
special table you can always reset it at least manually to get it to
have the proper sequential number.

Ron

 

[david epsom dot com dot au]

This is an article about creating custom counters:

http://support.microsoft.com/kb/q191253/

There is also a version for dao 3.6/ jet 5, but
it is substantially the same, and i don't have
the reference.

It's more sophisticated than you want, but you might
be interested anyway.

(david)