Autonumber Format Question

[Geezer]

Essentially two related question, a workaround for either would be appreciated.

Trying to format an autonumber field:

"GEF"####

e.g. GEF0015, GEF1258, etc

Alternatively is there anyway around finding the max of a field of the format "GEF"#?

The issue is that when I run a query to find the Max of a certain field with for example the following two entries:

GEF800
GEF1000

A max query spits out GEF800 as the max, whereas if I make the GEF800 GEF0800 then it spits of GEF1000 as a max, which is ideally what I want.

Thanks.

 

[pbaldy]

For starters:

="GEF" & Format(AutonumberFieldName, "0000")

should give you

GEF0015

 

[Geezer]

Thanks Paul.

Have just realised I'm unable to change an existing field to an Autonumber field. Is there perhaps any way of doing this? Perhaps adding some code to update the field automatically or the likes?

Thanks.

 

[pbaldy]

You could simply increment a number field with DMax(), then format it as above, adding the GEF and zero padding. Searching on DMax should turn up numerous examples, as these types of custom autonumber questions come up all the time.

 

[Geezer]

Thanks Paul,

Dmax seems to work well for finding the max record in my ID field, e.g.

ID
GEF005
GEF312
GEF807

Unbound box on form containing following Control Source code:

=Dmax("ID","MasterTable")

gives: GEF807

However, when I add +1 to the end:

=Dmax("ID",MasterTable")+1

I get #Error in the box.

So, what you're saying is I need to remove the GEF from my existing ID field and leave only the numbers behind. Then change the field format to something like "GEF"0000, as for the Autonumber example you provided earlier?

Then, in theory the above expression should work, correct?. This may seem like a daft question, how do I then apply this to the actual ID field rather than an unbound field?

Thanks for your input so far.

 

[Pat Hartman]

If "GEF" is the only prefix value, there is no need to store it or use it when searching. See my response to your other message. It tells you how to use your existing numeric values as the new autonumber value.

 

[irish634]

For starters:

="GEF" & Format(AutonumberFieldName, "0000")

should give you

GEF0015

I haven't tried it, but what happens when the "AutoNumberFieldName" exceeds 9999? You're allowing 4 characters, so what happens on record 10,000?

Just curious.

 

[pbaldy]

In a brief test, it disregarded the 4 zeros and displayed however many digits existed. That said, my suggestion was based on the OP's sample. In real life, the number of zeros in the Format function should be adjusted as appropriate to the application.

 

[irish634]

In a brief test, it disregarded the 4 zeros and displayed however many digits existed. That said, my suggestion was based on the OP's sample. In real life, the number of zeros in the Format function should be adjusted as appropriate to the application.

I thought it might ignore it. Just haven't had time to try.
Thanks