Number to Text Where the number size may change

[Julie Alley]

Hi All,

I need to convert a number field to a text field in a query, and I have used
format(quoteID, "00000000")

This isn't working for me for a couple reasons; 1st reason is that by
changing the number to have padded zero's this table won't coorelate to the
rest of the system. 2nd reason is the field is currently two characters,
but since it is an autonumber field natively we need to adjust when the
number reaches a 3 digit, 4 digit and so on.

Any ideas are appreciated!

 

[Klatuu]

Can you provide some more detail on this statement?

1st reason is that by changing the number to have padded zero's this table
won't coorelate to the rest of the system.

2nd reason is the field is currently two characters, but since it is an
autonumber field natively we need to adjust when the number reaches a 3
digit, 4 digit and so on.

Autonumber fields are not limited to to characters. The data type for an
Autonumber field is Long Integer. If you are trying to limit the length of
an autonumber field, you may create other problems. What do you mean by
"adjust when the number reaches a 3 digit..."

 

[John Spencer]

[Some Number Field] & ""

This will force the number to be a string. It will not pad it with leading
zeroes or other characters.

If the field ALWAYS has a value, you can convert the datatype with
CStr([Some Number Field])

 

[David F Cox]

You should not be converting an autonumber field to text. You should not be
comparing it to a text value. It sounds like you are using an autonumber
field as a sequence number, and you should not be doing that either.

 

[Julie Alley]

All Solved

It was quite easy to resolve by evaluating my data further.

helpful thoughts by Klatuu and John Spencer - thanks for just talking with
me - sometimes that is all it takes!

Julie