[Auto Number]+[(Current)MM-YYYY] as key?

[joepele]

Hi all,

First post and hope that someone can help.

I need to create an auto generated key with this format.

[Auto Number]+[(Current)MM-YYYY]
[Auto Number] = 0000
[(Current)MM-YYYY] = Current Month + Year

Pls help.

 

[RuralGuy]

Try searching this forum on DMax.

 

[joepele]

But that is for finding the highest auto number right?

But what should be used for the Current MM-YYYY?

 

[RuralGuy]

You should not be using an AutoNumber at all. Like I said, search here for DMax and you will find threads that are doing exactly what you describe.

 

[joepele]

Sorry Rural Guy,

I think I got what you mean, not auto number but Dmax where it simply +1 to the prev records.

Thanx!

I did a search but on the issue of current date?
I am a 0 @ coding + VB.
If I am not wrong
=date() gives current date, but what if I only want the month & year?

Came out with this which was pretty close but with no ans still.
http://www.access-programmers.co.uk/forums/showthread.php?t=146119&highlight=dmax

 

[RuralGuy]

Try Format(Date,"MM YYYY")

 

[joepele]

Try Format(Date,"MM YYYY")

Thanx.

I think this is also a very good validation rule.

Here's a sample of what I did.

I have created a table with 3 fields.
- Serial ID (As mentioned, to use DMAX)
- Date MM YYYY (Have tried to use the above but I need this to show automatically)
- Case (Just Details)

Currently, what I did was to create a form for this table but... I am trying to get the ID and Date (To show as 1 field) and to show automatically when there is a new case entered into the form.

*Am I doing the serial number + date thing correctly?

 

[RuralGuy]

*Am I doing the serial number + date thing correctly?

I don't see where you are doing anything with serial number + date! Did I miss something?

 

[joepele]

I don't see where you are doing anything with serial number + date! Did I miss something?

Errmmm...

Ok, I gotta admit, I am a newbie when it comes to access.

As a sample, I created this table to better show what I need help with.

The form here is supposed to "Auto complete" for the Serial ID field in here and the Date MM YYYY field.

But like what you mentioned earlier, the Serial ID should be using DMAX but need help for the other 2 parts.

1. To "Auto Complete" the Date MM YYYY
2. To "Auto Complete" the Serial ID

Hope this is clearer.

 

[gemma-the-husky]

although you might think you do, you really dont need (or want) to have a primary key structured in the way you mentioned

have an auotnumber key, which can then be used to link records in other tables - now if you THEN want additional data in the record, such as the date it was created, or a reference number, then add these in as separate fields. (note if it is unique, the reference number could be used as the primary key - but then you will probably get hung up on making a sequence intact, and stuff like that) - the reference number, and date can always be used to sort your data - the primary key is there to link your data tables together

there will be some discussion on here about whether autonumber primary keys are/can be superfluous - all i can say is i tend to use them, and it doesnt hurt.

the thing is not to get to hung about the structure of the primary key - if you are getting concerned, then you are probably talking about attributes of the entity, and not the primary key. - especially if you have repeated segments of the key - in many applications, primary keys would not necessarily be visible to the user, so it doesnt matter what they are!

 

[Rabbie]

although you might think you do, you really dont need (or want) to have a primary key structured in the way you mentioned

have an auotnumber key, which can then be used to link records in other tables - now if you THEN want additional data in the record, such as the date it was created, or a reference number, then add these in as separate fields. (note if it is unique, the reference number could be used as the primary key - but then you will probably get hung up on making a sequence intact, and stuff like that) - the reference number, and date can always be used to sort your data - the primary key is there to link your data tables together

there will be some discussion on here about whether autonumber primary keys are/can be superfluous - all i can say is i tend to use them, and it doesnt hurt.

the thing is not to get to hung about the structure of the primary key - if you are getting concerned, then you are probably talking about attributes of the entity, and not the primary key. - especially if you have repeated segments of the key - in many applications, primary keys would not necessarily be visible to the user, so it doesnt matter what they are!

To back up what Gemma says.

The advantages of having an autonumber PK include the following

1. They make it easy when you are adding a new record to the table because the PK is automatically inserted.
2. They are compact compared to alternative ways of having a PK and can be easily (and efficiently indexed).
3. You dont have problems if a decision is made at a later date to change the format of reference numbers etc.
4. There is nothing to stop you having unique reference numbers/codes as well.

 

[joepele]

although you might think you do, you really dont need (or want) to have a primary key structured in the way you mentioned

have an auotnumber key, which can then be used to link records in other tables - now if you THEN want additional data in the record, such as the date it was created, or a reference number, then add these in as separate fields. (note if it is unique, the reference number could be used as the primary key - but then you will probably get hung up on making a sequence intact, and stuff like that) - the reference number, and date can always be used to sort your data - the primary key is there to link your data tables together

there will be some discussion on here about whether autonumber primary keys are/can be superfluous - all i can say is i tend to use them, and it doesnt hurt.

the thing is not to get to hung about the structure of the primary key - if you are getting concerned, then you are probably talking about attributes of the entity, and not the primary key. - especially if you have repeated segments of the key - in many applications, primary keys would not necessarily be visible to the user, so it doesnt matter what they are!

Thanx for the tip so far,

The "Pri key" that was requested was this,
MMYY-0000
Where
Jan 2009 will reset the 0000 back to 0001 again.
e.g.
2008
0108-0001 to maybe 1208-5460
When it become
0109-0001 again.
The MMYY is there so that the (Autonumber) does not need to be "unique"

Basically the other purpose for such a PK is for easy searching too.

But what I have managed to do is to keep "MMYY" as 1 field and "Serial" number as another field. But I am trying to keep both a 1 field MMYY-0000.

See attached.

From the form, I managed to "lock" / prevent the user from making changes, I know that I can also hide it, but this is for viewing purposes.


The other problem that I am facing now is when I were to try to generate a report from this data.

See the report in the attached.

The Serial Number 0004 becomes 4 and the date became MM/DD/YYYY.

As I managed to set the field for each of the field individually but is there a way to Set the format for these 2 fields?

Thanx in advance.

 

[joepele]

To back up what Gemma says.

The advantages of having an autonumber PK include the following

1. They make it easy when you are adding a new record to the table because the PK is automatically inserted.
2. They are compact compared to alternative ways of having a PK and can be easily (and efficiently indexed).
3. You dont have problems if a decision is made at a later date to change the format of reference numbers etc.
4. There is nothing to stop you having unique reference numbers/codes as well.

Hi Rabbie,

The person who does the work always hopes to do it the easiest way... But usually it's the person who "sets" the requirements have wonderful hopes for the application.

If they could, they'd wished that the application can help them in the click of a button.

 

[joepele]

Anyone? Bump

 

[Rabbie]

But what I have managed to do is to keep "MMYY" as 1 field and "Serial" number as another field. But I am trying to keep both a 1 field MMYY-0000.

The other problem that I am facing now is when I were to try to generate a report from this data.

See the report in the attached.

The Serial Number 0004 becomes 4 and the date became MM/DD/YYYY.

As I managed to set the field for each of the field individually but is there a way to Set the format for these 2 fields?

Thanx in advance.

How your data appears in your report depends on the formatting of the text box you display it in

To get MMYY use something like =Format(Datefield,"MMYY")

To get 0004 use something like =Format(SerNo,"0000")


Hope this helps

 

[joepele]

How your data appears in your report depends on the formatting of the text box you display it in

To get MMYY use something like =Format(Datefield,"MMYY")

To get 0004 use something like =Format(SerNo,"0000")


Hope this helps

Thanx for the response,

I managed to store them in the correct format when they are stored as individual fields.

But when I were to combine them as 1 field... they went bonkers.

See attached report, text 13 to see what I mean.

Any solution around it?

 

[Rabbie]

Change the data source for your Text13 to =Format([serial id],"0000") & "-" & Format([date mm yyyy],"mmdd")

I have tested this and I think it is what you want.

Goodluck

 

[joepele]

Change the data source for your Text13 to =Format([serial id],"0000") & "-" & Format([date mm yyyy],"mmdd")

I have tested this and I think it is what you want.

Goodluck

Hi Rabbie,

Thanx for your help.
But I got a Syntex error. Not sure what is wrong.

Upz on your repo

 

[georgedwilkinson]

The person who does the work always hopes to do it the easiest way... But usually it's the person who "sets" the requirements have wonderful hopes for the application.

If they could, they'd wished that the application can help them in the click of a button.

Why is your user looking at your primary key? Just put the fields in as attributes as suggested earlier and calculate what the user thinks he wants to see.

Remind your user of the Y2K problem. It was exactly this kind of thinking (using codes instead of real live data) that caused that problem.

The application can help them at the click of a button. Just don't let them tell you how to build it...find out their real business requirements for the system, not their inaccurate ideal of how it should be implemented. For all they know, you're going to create a neural interface that doesn't require a primary key, keyboard, monitor, or mouse.