How do I creat a Number that incorporates the ID?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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?
 
What is your formula where you combine them? Sounds like you did not format
the year properly.
 
ID: DatePart("yyyy",[DATE]) & "-" & [AUTOID]
But if I change the above to just yy, it gives me a message "invalid
procedure call".
 
Try ID: Format([Date], "yy") & "-" & [AutoID]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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

Rick B said:
What is your formula where you combine them? Sounds like you did not
format
the year properly.
Click to expand...
Click to expand...
 
It worked. Thank you so much!

Douglas J. Steele said:
Try ID: Format([Date], "yy") & "-" & [AutoID]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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

Rick B said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top