By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Combine date and autonumber field in Access

P: 28
I want to combine date field and autonumber field to make a Incident number field in access table and on an Access form
Feb 18 '07 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Please look in this thread and consider adding those items necessary to give an answer.
(POSTING GUIDELINES: Please read carefully before posting to a forum)

MODERATOR.
Feb 18 '07 #2

nico5038
Expert 2.5K+
P: 3,072
Combining two basic fields into one is considered bad design.
The "normalized" way is to use a composite key, being constructed from both fields. This is achieved by selecting both fields and pressing the Key button in the table design mode.

Finally on a form a "read only" field can be used with an algoritm like:
=[AutoID] & " " & [SequenceNo]
Even formatting could be used to get a 1 displayed like 001.

Getting the idea ?

Nic;o)
Feb 18 '07 #3

P: 28
Please help need to have date and add autonumber to end of the date.
Feb 18 '07 #4

ADezii
Expert 5K+
P: 8,597
Please help need to have date and add autonumber to end of the date.
What exactly are you requesting? Is this it?
02/16/2007_1, 02/16/2007_2, 02/16/2007_3, 02/16/2007_4, 02/17/2007_5, etc.
Feb 18 '07 #5

P: 28
What exactly are you requesting? Is this it?
02/16/2007_1, 02/16/2007_2, 02/16/2007_3, 02/16/2007_4, 02/17/2007_5, etc.
Yes that is it I will format the autonumber a little different but I do want it at the end like you have it.
Feb 18 '07 #6

nico5038
Expert 2.5K+
P: 3,072
This is a duplicate post, see:
http://www.thescripts.com/forum/thread604005.html

I'll close this one so all comments can be concentrated in the above thread.

When you have additional information, please add that as a [Reply] to the original thread:-)

Nic;o)
Feb 18 '07 #7

P: 28
Combining two basic fields into one is considered bad design.
The "normalized" way is to use a composite key, being constructed from both fields. This is achieved by selecting both fields and pressing the Key button in the table design mode.

Finally on a form a "read only" field can be used with an algoritm like:
=[AutoID] & " " & [SequenceNo]
Even formatting could be used to get a 1 displayed like 001.

Getting the idea ?

Nic;o)
If you do not want to answer this question why even mess with it. At least I got an answer on the page till you closed it out.
Feb 18 '07 #8

P: 28
If you do not want to answer this question why even mess with it. At least I got an answer on the page till you closed it out.
This is what I want when I am done Date_AutoNumber (02-18-07-001) I want it done automaticaly for the user.
Feb 18 '07 #9

nico5038
Expert 2.5K+
P: 3,072
If you do not want to answer this question why even mess with it. At least I got an answer on the page till you closed it out.
Dear DeanO,

Posting the same question twice is considered bad behaviour and spoiling the effort of the experts here at the site. Having two threads will leave the experts unaware of all solutions posted in the other thread and thus multiple identical solutions can be proposed.
The experts here are volunteers and investing their precious spare free time to help others. Gratitude isn't required, but your statement posted here doesn't really help to motivate experts to help you.

Please take a moment to read my proposed solution and comment on that instead of starting a flame. Keep your comments professional and to the subject at hand.

Nic;o)
Feb 18 '07 #10

P: 28
Dear DeanO,

Posting the same question twice is considered bad behaviour and spoiling the effort of the experts here at the site. Having two threads will leave the experts unaware of all solutions posted in the other thread and thus multiple identical solutions can be proposed.
The experts here are volunteers and investing their precious spare free time to help others. Gratitude isn't required, but your statement posted here doesn't really help to motivate experts to help you.

Please take a moment to read my proposed solution and comment on that instead of starting a flame. Keep your comments professional and to the subject at hand.

Nic;o)
Thank you. Your answer did not give the results I need. As I understand I need to combine the date field and the autonumber field to get one field. with
02-18-07-001 I have tried this line after update mode. txtbox name = date() & ID
Feb 18 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
DeanO

The experts here volunteer their time to help others with their problem. They are at least deserving of your respect. Site Rules prohibit the double posting of questions as it is confusing for everybody concerned and moderators are operating under instructions to either merge the questions or close/delete one of the questions. There are many great experts on the Access forum who will be more than willing to help you if you do the following.

1. Respect all answers given even if they are incorrect
2. If you don't understand an answer then ask for further clarification
3. If you try an answer and it doesn't work. Then post the full code of what you have tried and a detailed explanation of any error received or why it doesn't suit your requirements.

This site is an invaluable resource which could provide you with ongoing assistance as long as you treat the experts with respect and follow the Posting Guidelines as laid out in the FAQ.

ADMIN

Note: I am merging both questions together
Feb 18 '07 #12

P: 28
What exactly are you requesting? Is this it?
02/16/2007_1, 02/16/2007_2, 02/16/2007_3, 02/16/2007_4, 02/17/2007_5, etc.
Yes this is what I want to do
Feb 18 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes this is what I want to do
What are the names of the two fields you are trying to combine. Is the date field a date or text type?

When these two fields combine they will be a text field so make sure the new field is set to text.

Do you want to update existing records or just add this to the after update event of the date field?

Mary
Feb 18 '07 #14

nico5038
Expert 2.5K+
P: 3,072
Thank you. Your answer did not give the results I need. As I understand I need to combine the date field and the autonumber field to get one field. with
02-18-07-001 I have tried this line after update mode. txtbox name = date() & ID
You should have added a new textbox to the form and set the controlsource to:
=Date() & "-" & format([ID],"000")
also add a (hidden) field with a default value of Date() that's bound to the tablefield that will have to hold the Date().
On a new record this field won't show as long as no autonumber has been assigned by Acccess.

Nic;o)
Feb 19 '07 #15

P: 28
You should have added a new textbox to the form and set the controlsource to:
=Date() & "-" & format([ID],"000")
also add a (hidden) field with a default value of Date() that's bound to the tablefield that will have to hold the Date().
On a new record this field won't show as long as no autonumber has been assigned by Acccess.

Nic;o)
Thank you this solved my problem.
Feb 20 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.