How do I creat a Number that incorporates the ID?

[Guest]

I would like to create a number with an abbreviation, last two digest of a
year and then incorporate the ID number in one field:

IR(YY)0 1

The last digit would be the autonumber assigned by Access.

 

[Douglas J. Steele]

Actually, what you're suggesting is a violation of relational database
principles, one of which states that a field should only hold a single piece
of information.

My advice would be to store the information as 3 separate fields. If you
need to display such a code for display purposes, create a query that has a
computed field that concatenates the appropriate information into a single
field for display purposes, and use the query wherever you would otherwise
have used the table.

 

[Guest]

Hi. I did not ask this question, but it does apply to me. I have done exactly
what you have suggested below. The only problem is this. I have formatted my
date field to display the last two digits of the current year using yy.
However, when I concatenate the date field and autonumber field, it goes back
to the full year. So instead of getting 05-1 out of date field 05 and
autonumber 1, I get 2005-1. Is there a way of getting the combined query
field to just show 05?

 

[Rick B]

What is your formula where you combine them? Sounds like you did not format
the year properly.

 

[Guest]

ID: DatePart("yyyy",[DATE]) & "-" & [AUTOID]
But if I change the above to just yy, it gives me a message "invalid
procedure call".

 

[Douglas J. Steele]

Try ID: Format([Date], "yy") & "-" & [AutoID]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ID: DatePart("yyyy",[DATE]) & "-" & [AUTOID]
But if I change the above to just yy, it gives me a message "invalid
procedure call".

What is your formula where you combine them? Sounds like you did not
format
the year properly.

 

[Guest]

It worked. Thank you so much!

Try ID: Format([Date], "yy") & "-" & [AutoID]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ID: DatePart("yyyy",[DATE]) & "-" & [AUTOID]
But if I change the above to just yy, it gives me a message "invalid
procedure call".

What is your formula where you combine them? Sounds like you did not
format
the year properly.




Hi. I did not ask this question, but it does apply to me. I have done
exactly
what you have suggested below. The only problem is this. I have
formatted
my
date field to display the last two digits of the current year using yy.
However, when I concatenate the date field and autonumber field, it
goes
back
to the full year. So instead of getting 05-1 out of date field 05 and
autonumber 1, I get 2005-1. Is there a way of getting the combined
query
field to just show 05?

:

Actually, what you're suggesting is a violation of relational
database
principles, one of which states that a field should only hold a
single
piece
of information.

My advice would be to store the information as 3 separate fields. If
you
need to display such a code for display purposes, create a query that
has a
computed field that concatenates the appropriate information into a
single
field for display purposes, and use the query wherever you would
otherwise
have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I would like to create a number with an abbreviation, last two
digest
of a
year and then incorporate the ID number in one field:

IR(YY)0 1

The last digit would be the autonumber assigned by Access.