Auto Date Field

[pekuiper]

Good day to all;

I am a beginner to Access and currently using 2003 – I want to add a date field with a button that when clicked will fill the date field with the current date. The same with a start time and end time field (With button setting the times) with a total field giving the total time. For advanced users this will be simple but for me very confusing.

Thank you for your time

 

[boblarson]

It's better to have ONE field DATE AND TIME because it is more work to concatenate them for calculations, whereas it is much easier to use FORMAT to display various parts of it. And, for the click event (make sure you put it in the VBA IDE window and NOT in the PROPERTIES dialog):

Me.YourTextBoxNameHere = Now

 

[missinglinq]

Another practical advantage of Bob's approach is that there's way less hassle if the Start and End times occur on two separate days! Theoretically, Time really doesn't exist as a separate entity, but rather is always a part of a Date. 3 PM toady is not the same time as 3 PM tomorrow.

 

[terraplane]

Hello all,
I have been searching for an answer to this for a while now.
I am trying to create a simple DB of temperature recordings with the date and time of the recording automatically filled in.
I have seen a few posts here dealing with this subject of auto filling a date field based on the record being created or modified. To be honest I have tried the suggestion here plus the 2 located on this page; http://www.access-programmers.co.uk/forums/showthread.php?t=63933 - And on yet another page which was based on another field's value being "not null".
None of them are working for me. I don't know what I am doing wrong but every time I go to enter data in my form it does not populate the date and time...
I have tried on dirty, before update, after update, on enter, on exit; all in the VBA code with things like "me.fieldname=now()" "me.fieldname=date()" "If Not IsNull(Me![otherfield]) Then Me![fieldname] = Date()"
fieldname = Date Time Stamp
otherfield = Temperature

And I'm not even getting any errors....it just won't operate.

I can not seem to make this work. Can some one explain what i might be doing wrong, please?

Any help at all will be greatly appreciated!!
Thanks,

 

[boblarson]

And I'm not even getting any errors....it just won't operate.

You aren't by chance using Access 2007 are you? If so, have you done this:
http://www.btabdevelopment.com/main...owtoenablecodeandmacros/tabid/57/Default.aspx

 

[terraplane]

No - in fact it's 2000 that I am working on right now....I haven't tried it on my 2003 version yet (diff computer) Do you think that would make a difference?

 

[boblarson]

Can you post the db?

 

[terraplane]

db post

Here it is.
It is in it's infancy as far as what data is to be tracked, the auto date was something I was trying to figure out before we went any further with everything that may or may not get tracked.

 

[boblarson]

1. Your Date/Time stamp should be DATE/TIME datatype, not text.

2. You can put Now() in the field's DEFAULT property within the table. This will add the date/time stamp anytime that a new record is added. It won't add one for any data that didn't have one to begin with.

3. I would create another date/time field called LastModified and then in the form's Before Update event, put
Me.YourLastModifiedTextBoxName = Now()

That way, if any data gets modified it will get updated.

 

[terraplane]

Thank you. It works....I knew I had to be over thinking the problem.

Thanks again,

Scott

 

[neelram]

Could you explain again?

Hi, I have the same problem as Scott. I made a form for filling in contact information. The date is a crucial part, thats why i want it to be filled in automatically every time a new record is added. I tried boblarson's advice

2. You can put Now() in the field's DEFAULT property within the table. This will add the date/time stamp anytime that a new record is added. It won't add one for any data that didn't have one to begin with.

But i think im not advanced enough yet. Could you please explain this to me again?
By 'Default property' you mean the 'default value' in the 'general' tab
(in the design view of table)?

I would really appreciate your help!

 

[DCrake]

Simple Software Solutions

You need to enter it in the Default Value property whilst in the design mode of the table.

CodeMaster:

 

[themurph2000]

Hi, I have the same problem as Scott. I made a form for filling in contact information. The date is a crucial part, thats why i want it to be filled in automatically every time a new record is added. I tried boblarson's advice

2. You can put Now() in the field's DEFAULT property within the table. This will add the date/time stamp anytime that a new record is added. It won't add one for any data that didn't have one to begin with.

But i think im not advanced enough yet. Could you please explain this to me again?
By 'Default property' you mean the 'default value' in the 'general' tab
(in the design view of table)?

I would really appreciate your help!

In the table in question, switch to Design View. (either use the button at the far left of the toolbar or under the View menu) Click on the field that you want to set with the Now function. On the lower half of the Design View window, look for the "Default Value" property. In the box next to it, type "=Now()" (without the quotes) That will place the date and time as a default value in that field for each record. (This can be changed by the user if they want). Make sure to save, of course.

 

[neelram]

Thank you so much! it works!!!

 

[b1004u]

I am VERY GRATEFUL for this forum, and have succeeded in adding a date for new entries in my database. However, i was unable to follow the directions for creating a DATE MODIFIED field to entries that already exist and do not have a number already.

I tired to follow the directions by BobLarsen but did not understand what you meant by the Before Update field, or the command in defaualt that would read ----> me.....=now()

I am using Access 07

IN the end, the goal is to add a new field which will add a date modified stamp as data is entered in the older entries. does that make sense?

Thanks!

 

[wbheath]

Ok i'm having a similar problem with my auto date but this time it is when I print.

I have a field for my date/time created, the default value is "=now()" this works fine but when I print it uses the current time and not the time that is in the form. The form always stays on the date/time created which is what I want. Now I just need the printer to print whats on the form.

Thanks for the help

btw its access 2003

 

[themurph2000]

I am VERY GRATEFUL for this forum, and have succeeded in adding a date for new entries in my database. However, i was unable to follow the directions for creating a DATE MODIFIED field to entries that already exist and do not have a number already.

I tired to follow the directions by BobLarsen but did not understand what you meant by the Before Update field, or the command in defaualt that would read ----> me.....=now()

I am using Access 07

IN the end, the goal is to add a new field which will add a date modified stamp as data is entered in the older entries. does that make sense?

Thanks!

Change to Design View for the Form. In the upper-left corner of the form, (near where it says Detail), there should be a gray box with a black dot in it. Click on that box and open up the Properties Window. (make sure the Properties window says "Form," not Detail or anything else. Click on the
Event tab, then look for the "Before Update" option. In that box, you type the me.thenameofthedatebox = now(). (thenameofthedatebox is whatever you named the box where Date Modified would be)

 

[themurph2000]

Ok i'm having a similar problem with my auto date but this time it is when I print.

I have a field for my date/time created, the default value is "=now()" this works fine but when I print it uses the current time and not the time that is in the form. The form always stays on the date/time created which is what I want. Now I just need the printer to print whats on the form.

Thanks for the help

btw its access 2003

In your report, does the box show the formula "=now()" or does it show the name of the field where this is occurring? A text box in a report can be set to show the current date using that formula.

If you can, upload your database so we can have a look.

 

[wbheath]

In your report, does the box show the formula "=now()" or does it show the name of the field where this is occurring? A text box in a report can be set to show the current date using that formula.

If you can, upload your database so we can have a look.

In the report the box shows "Time" (the name of the field) and the control source is "time" as well. I'm hoping just to have the =now() function only change once the form is made and never again. But everytime I access the record that time stamp changes.

Sorry i can't upload the file, confidentiality stuff.......

 

[themurph2000]

In the report the box shows "Time" (the name of the field) and the control source is "time" as well. I'm hoping just to have the =now() function only change once the form is made and never again. But everytime I access the record that time stamp changes.

Sorry i can't upload the file, confidentiality stuff.......

The field name "time" might be messing you up. I think there are certain words you're not supposed to use for field names. Change it to "timeupdated" or something like that. See if it works after you do that. I just tried it myself on a dummy table and form, and that seemed to do the trick.