Concatenate state/month/recordID

dark11984

Registered User.
Local time
Tomorrow, 05:38
Joined
Mar 3, 2008
Messages
129
Hi,
I’ve usually got a basic idea with access but this one has me stumped…

I’ve got a text box on my form that I want to display a log number for each record.
The log number should consist of the state (which is selected form a combo box on the form, the current month (month in a 2 digit format i.e. 04) and the record id (in a 4 digit format i.e. 0001).

So an example of how I want it to look is N040001. I also want the number to be stored in the table that is linked to the form (tbldata).

Is this possible in either access or VBA?

Cheers,

Nick
 
You can do it in an unbound text box, by setting the Control Source to something like;

Code:
=[Field1] & ", " & [Field2]
 
Thanks for your quick reply.

That's how i was going to do it originally but how do i get the data back into the table if the control source is being used by the code? and also how do i format the numbers so that they are 2 digits for the date & 4 digits for the record id?
 
Are you wanting to store the concatenated result :eek: if so have a read of this thread before you go any further.
 
I have read it but it didn't really help me. Yes I do want to store the
concatenated result.

Later down the track I want to be able to search by log number (concatenated result).
 
I have read it but it didn't really help me. Yes I do want to store the
concatenated result.

Later down the track I want to be able to search by log number (concatenated result).

:eek: NO, that is NOT the way to do it. You do not store concatenated data as that is redundant and NON-NORMALIZED. Use a QUERY when you want to search and you can create your field in there. There is absolutely no need to store the value.
 
ok that helps me with the first part - so i'm keeping it as an unbound field but how do I get jsut the month part of date() and format it as 2 digits?

Thanks.
 
ok that helps me with the first part - so i'm keeping it as an unbound field but how do I get jsut the month part of date() and format it as 2 digits?

Thanks.

You can use Format(Date, "mm")
 
Hi,
I’ve usually got a basic idea with access but this one has me stumped…

I’ve got a text box on my form that I want to display a log number for each record.
The log number should consist of the state (which is selected form a combo box on the form, the current month (month in a 2 digit format i.e. 04) and the record id (in a 4 digit format i.e. 0001).

So an example of how I want it to look is N040001. I also want the number to be stored in the table that is linked to the form (tbldata).

Is this possible in either access or VBA?

Cheers,

Nick

Something to think about:

What happens when you've reached 9999 records. Could this happen in 1 mth? There is also a risk of duplication as next yr 04 mth could have 0001 in it as well. If there is no chance of 9999 records in a month I would put 2 digits of the yr in as well to stop duplication in the same month next yr etc. ie N04090001.

Use the Format fn to get the date part out.

It has to start at 0001 again. The easiest way that I can think of is to set up an extra fld as an Autonumber and use the Str fn to pull out the last 4 digits and add 0's if Len(extrafld)<4.

Concatenate the 3 parts and put in a field (say LogNoID).

But, as Bob has said, why do you need to store it - get it out with a calc fld in a query.

Do you have a PK fld in the table?
 
Last edited:
Great!! Thanks Bob, Now i know how to format, i can use this for other things too now.

Thanks Wis, but definately won't have more than 1000 records in a month.

Thanks everyone for your help!
 

Users who are viewing this thread

Back
Top Bottom