How to set a table field to date by year?

[Collins]

I need to create a table that is centralized around the year. Do I just use a number field or a date field?

[June7]

I don't know. What is your database for? What data will be in this table? Probably either will do. If you want to store a full date then use a date/time field. If you just want to enter a year value then use a number field. Keep in mind that the year part can always be extracted from a full date/time value.

[Collins]

I don't know. What is your database for? What data will be in this table? Probably either will do. If you want to store a full date then use a date/time field. If you just want to enter a year value then use a number field. Keep in mind that the year part can always be extracted from a full date/time value.

This is on the heals of my Traffic Database Thread. You had said to ask the questions individually as they come up and not have a long drawn out thread. So here I am. Do you want the details about the whole database or just the table I'm working on for the database? I have 239 intersections. So table one is called Intersections with 239 records. The purpose of the database is to enter last years crashes at each intersection. Some intersection will have no crashes some will have as many as 20. It will vary. But each year I will be entering the crashes that occurred the pervious year. The reports that I will have to create will take the previous 3 years of crashes then crunch some numbers. One report just adds up the last three years of crashes and then prioritizes the intersections in a spreadsheet with the intersection with the highest number of crashes at the top and then descends from there.

So the year is important. Is there a way to set that field to a default year in a form so i'm not entering the year 239 times? I guess I could modify the default setting in the table but a button or something would be so much nicer.

[June7]

A textbox has DefaultValue property so when entering a new record, a DefaultValue of Year(Date()) would automatically enter the current year when record is initiated. However, since appears you will be entering historic records and will need to input year relevant to the old record, DefaultValue will have to be managed with code. What can happen is that a form is opened to initiate a data entry session, user inputs year into first record and code can set the DefaultValue property with that input. When user subsequently moves to new record row and initiates a record by data entry to any other field, that same year value will automatically populate. Each new record will continue to use that year value until user manually inputs a different year value which will again trigger the code to set DefaultValue or user closes the form. Review http://access.mvps.org/access/forms/frm0012.htm

[Collins]

that looks cool... I like where you're going with that. It gives the user control over the year. Let me tinker with it and see if I can get it to work.

[Collins]

Guess I should have asked. For the field in the table does it need to be set to date or number? Then I just create a button with the code you linked and it should work right?

[June7]

Your choice. If all you have for the historic records is year part, then just make it a number field and enter year. If you want to enter a full date then make it a date/time type.

Don't need a button. Why make user do more key strokes or mousing - isn't that what you are trying to avoid? I know the example indicates using a button but code in the textbox AfterUpdate event procedure is friendlier.

BTW, year is a reserved word. Don't use reserved words as names. Also advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Collins]

Your choice. If all you have for the historic records is year part, then just make it a number field and enter year. If you want to enter a full date then make it a date/time type.

Don't need a button. Why make user do more key strokes or mousing - isn't that what you are trying to avoid? I know the example indicates using a button but code in the textbox AfterUpdate event procedure is friendlier.

BTW, year is a reserved word. Don't use reserved words as names. Also advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

I'm receiving this error message when I place that code in the AfterUpdate event procedure in the textbox. Do I need to name the textbox cQuote?

[June7]

No, the constant is for concatenating quote mark delimiters. Post your actual code for analysis.

I prefer to use apostrophe delimiter instead of doubled quote marks. However, that example is for text type fields.

If you are using number type field the code could be simply:

Me.yourcontrolname.DefaultValue = Me.yourcontrolname

I always name controls different from the fields they are bound to, like: tbxYear.

[Collins]

No, the constant is for concatenating quote mark delimiters. Post your actual code for analysis.

I prefer to use apostrophe delimiter instead of doubled quote marks. However, that example is for text type fields.

If you are using number type field the code could be simply:

Me.yourcontrolname.DefaultValue = Me.yourcontrolname

I always name controls different from the fields they are bound to, like: tbxYear.

I typed it verbatim the link you sent me - const cQuote=""""me!Control.DefaultValue = cQuote & me!Control.Value & cQuote

When you say control name that would be the name of the textbox? I typically have let Access name the controls. I'll try that string and see what I get. Thanks...

[June7]

The link shows two lines of code. You have only one.

[Collins]

The link shows two lines of code. You have only one.

When I try to enter in the AfterUpdate event as 2 lines of code I get the message "The expression you entered contains invalid syntax" "you may have entered an operand witout an operator"

I entered it like this.

const cQuote=""""
me!Control.DefaultValue = cQuote & me!Control.Value & cQuote

[Collins]

No, the constant is for concatenating quote mark delimiters. Post your actual code for analysis.

I prefer to use apostrophe delimiter instead of doubled quote marks. However, that example is for text type fields.

If you are using number type field the code could be simply:

Me.yourcontrolname.DefaultValue = Me.yourcontrolname

I always name controls different from the fields they are bound to, like: tbxYear.

When I enter - Me.CrashYear.DefaultValue = Me.CrashYear

I receive this error message.

[June7]

What is your control's name? Put that in place of 'Control'. If it is bound to a number field then eliminate the cQuote concatenation as shown in my earlier post.

You posted while I was composing.

The code works for me.

Are you putting that code directly into the event property? That's not where it goes. In the event property, select [Event Procedure] then click the ellipsis (...) to open VBA editor at the procedure. Type code there.


If you want to provide db for analysis, follow instructions at bottom of my post.

[Collins]

What is your control's name? Put that in place of 'Control'. If it is bound to a number field then eliminate the cQuote concatenation as shown in my earlier post.

You posted while I was composing.

The code works for me.

Are you putting that code directly into the event property? That's not where it goes. In the event property, select [Event Procedure] then click the ellipsis (...) to open VBA editor at the procedure. Type code there.


If you want to provide db for analysis, follow instructions at bottom of my post.

That got it. I had it in the wrong event procedure. I didn't have it in the VBA Editor.

Thanks June that works slick!!!