I need to make the column “revision date” default to today’s date. Every time I create a record in a base form. Please advise of a macro or an alternate method. I can not write letter characters in the default field of a date formatted field.
Hello,
What database are you using? HSQL embedded (default), Firebird embedded (experimental default), external such as MySQL, PostgreSQL, etc.
Also the question need clarity. Your column is “revision date” but you state when you create a record. So is this for new only or new & each & every update?
What does “I can not write letter characters in the default field of a date formatted field.” mean?
I think his “I cannot write letter characters…” comment refers to in the control properties dialog, on the General tab, “Default Date” box… you can only type numbers if it is a Date (or other numeric) type field. So he was probably trying to type something like a SQL “today” equivalent here without success. I don’t have a quick answer, but I figured I’d clarify that one bit anyway since I’ve been playing with similar things recently.
Thanks for the quick reply. I am using HSQL embedded. I want to default the “revision date” field to todays’s current date and time. When creating a new record and each and every update. The Properties of the date field in the Control properties dialog window shows and option for Default date, but I am not able to enter something like Datestamp (), because it only allows choosing numbers. All I want to do is a method to stamp de current date and time as default on the “revision date” field.
Hello @jorengo,
Most people using Base do not realize that the Default value stated in the table design is not a default for the field in the database but rather for data entry. So to start, look at my answer in this post → TIMESTAMP data type auto-fill problem what am I missing?. Please note the reference at the start of the answer to the linked Bug reports. Please read these as they will provide further insight as to the Default setting.
This answer is not enough to answer your question. Please also see this post → Inserting current date in field on new record. Please read through the entire posting. It shows a method (ALTER statement) to automatically enter CURRENT_DATE (timestamps also work). Further down the post is a macro used to update the date upon each modification.
If this type of process is new to you, it is advisable to first make a backup of your .odb file for safety and you can freely test the process knowing you won’t mess up your existing set-up.
Hopefully after all this you can also see why you cannot enter text in the Default section.