Gerated Record Numebr

[MaleNurse325]

I have a database and I am struggling to format the appearance of an AutoNumber to comply with the company standard as an example it should appear like this PUK-NWI-OHS-325-19
The AutoNumber in this is the 325. However, I cannot format this to pin the year on the end I'm using "PUK-NWI-OHS-"0000"-19" But I have typed in the 19 and obviously I don't want to go in next year and change it to 20
I have a date field ConsgnDte from which I can lift the year. :banghead:

thanks guys.
I'm guessing this, for you will be a 30 second job but I have been fighting for over an hour and I'm :banghead::banghead::banghead:

 

[theDBguy]

Have you tried?

"PUK-NWI-OHS-" & [AutonumberField] & Format([DateField], "-YY")

 

[MaleNurse325]

Thanks Bob, all I get is #Name?

 

[theDBguy]

Thanks Bob, all I get is #Name?

Did you replace [AutonumberField] and [DateField] with the actual names of your Autonumber field and the other field where you said you can get the Year from?

 

[MaleNurse325]

Like this "PUK-NWI-OHS-"&[Consignment Number]& Format( [ConsgnDate],"-YY")

 

[theDBguy]

Like this "PUK-NWI-OHS-"&[Consignment Number]& Format( [ConsgnDate],"-YY")

Oh wait, did you put that in the Format property of the Autonumber field? If so, I was thinking this is something you would put in a query or form or report. No one should be looking at the table anyway.

 

[MaleNurse325]

It's in the format property of the Consignment Number field on the form

 

[theDBguy]

It's in the format property of the Consignment Number field on the form

Try putting it in the ControlSource property instead (just try).


Edit: Oh, and don't forget to put an equal sign (=) in front of it.

 

[Mark_]

Before you go too far in, are they expecting the number to start over at 0000 some point in the near future?

 

[MaleNurse325]

The number is not too important at present. I have set it at 345 at present, during the design phase. I can reset it later.

I tried to put it into the control source with no effect ( yes I remembered the =)
I'm thinking about dropping the year to be honest. I'm starting to think its a nice to have not a must have.

 

[theDBguy]

The number is not too important at present. I have set it at 345 at present, during the design phase. I can reset it later.

I tried to put it into the control source with no effect ( yes I remembered the =)
I'm thinking about dropping the year to be honest. I'm starting to think its a nice to have not a must have.

"no effect" means what exactly? You didn't get an error instead? Or, are you saying it still said #Name?
If the latter, try these two lines separately and tell us what happen:
=[Consignment Number]
and
=[ConsgnDate]

 

[MaleNurse325]

Call me a looser but I'm calling it quits.
I have been fighting with this for a couple of hours and its time to go home .
its 5:30pm here
Thank you very much for trying for me.
This is the second or third time you have helped me.
Thank you

 

[theDBguy]

Call me a looser but I'm calling it quits.
I have been fighting with this for a couple of hours and its time to go home .
its 5:30pm here
Thank you very much for trying for me.
This is the second or third time you have helped me.
Thank you

Hi. No worries. If you could post a sample db, we could help you get to the bottom of the issue faster. Cheers!

 

[Mark_]

OK, just a few things to go over to help you understand what you are trying to bite off;
1) Autonumber fields do just that. They start at 1, add 1 every time a record is added, and are a number only.
2) What you display to your users does not need to be what is in the table.
3) You do not need to save "PUK-NWI-OHS-" if this is a static value that never changes. Likewise you do not need to save the year if that is already some place else in the record.

Lets say you have a DATE field in your record for when the record is entered. You may want to do something as simple as

TxtLableForThisNumber.Caption = "PUK-NWI-OHS-" & FORMAT( YourAutoNumberField MOD 10000, "0000" ) & "-" & FORMAT( YourDateField, "YY")

What this translate to is
Create a LABEL that you set the CAPTION value to (so user won't try to change it)
Assign the text constant of "PUK-NWI-OHS-" to your label
AND add the last 4 of your auto number key (I have a habit of using MOD to return this so I can immediately see EXACTLY what I did)
AND add a "-" to separate your number from the year.
AND add the last two digits of your year at the end.

This is basic concatenation to build up a user readable value from different parts. First few times you do this stuff it is always a pain to get your head wrapped around, then it becomes second nature. It is how I put together name/address for mailing labels.

Hang in there, if you can get through putting a foley catheter in you can get this to.

 

[Gasman]

Like this "PUK-NWI-OHS-"&[Consignment Number]& Format( [ConsgnDate],"-YY")

Have you spelt the consign date correctly?, as you have consign for the number.?

FWIW I just gave it a quick go and it worked for me.?