Concatenate state/month/recordID (1 Viewer)

dark11984 · Apr 9, 2009

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

John Big Booty · Apr 9, 2009

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

Code:

=[Field1] & ", " & [Field2]

dark11984 · Apr 9, 2009

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?

John Big Booty · Apr 9, 2009

Are you wanting to store the concatenated result

if so have a read of this thread before you go any further.

dark11984 · Apr 9, 2009

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).

boblarson · Apr 9, 2009

dark11984 said:
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).

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.

dark11984 · Apr 9, 2009

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.

boblarson · Apr 9, 2009

dark11984 said:
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")

WIS · Apr 9, 2009

dark11984 said:
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?

dark11984 · Apr 9, 2009

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!

Concatenate state/month/recordID (1 Viewer)

dark11984

Registered User.

John Big Booty

AWF VIP

dark11984

Registered User.

John Big Booty

AWF VIP

dark11984

Registered User.

boblarson

Smeghead

dark11984

Registered User.

boblarson

Smeghead

WIS

Registered User.

dark11984

Registered User.

Similar threads

Users who are viewing this thread