Date stamp

elvira-kate · General User Joined: 23 Oct 2008 Posts: 26

I would like a field in my Form which enters automatically today's date when an entry is created or changed. Is that possible? I have tried creating a 'Timestamp' field in my Table, and putting that onto the Form, but it doesn't do anything.

Kate

probe1 · Posted: Thu Nov 06, 2008 5:55 am Post subject:

Kate, this has been discussed many times.
Using the search function for "timestamp" i find 50 posts in BASE forum. I'm sure you'll find your answer there.
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs

elvira-kate · General User Joined: 23 Oct 2008 Posts: 26

Thanks for this. I had been searching on datestamp and found nothing. I didn't think of searching on timestamp because that's not really what I wanted. However, on searching for timestamp I get a lot of posts referring to really complicated SQL procedures and macros. Is this the only way to do it? I've created several databases using Access with this feature and it was jolly easy to put in, but I don't understand the first thing about SQL.

probe1 · Posted: Thu Nov 06, 2008 7:53 am Post subject:

No, there is no automatic way.

Simplest thing is to set up a column with a default date/time/timestamp using SQL:

elvira-kate · General User Joined: 23 Oct 2008 Posts: 26

I'm sorry, I don't understand this. Where do I type these? What and where is a column? And does this mean I can't get an autoupdating field into a Form at all? I only need a current date, not a time.

Kate

r4zoli · Posted: Thu Nov 06, 2008 11:37 am Post subject:

Tools>SQL...
SQL command window, Write commands into it and execute. If Ok changes happend.

probe1 · Posted: Thu Nov 06, 2008 1:50 pm Post subject:

A column is a "field" in your table, which is the base data source of your form.

You won't get an autoupdate field on the form: but with my suggestion you don't need it. If you insert a new record to the table (filling all the other fields of the table by typing in the form) the field/column will fill itself automatically with the CURRENT_DATE (that is a database function).

Does this help?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs

elvira-kate · General User Joined: 23 Oct 2008 Posts: 26

Sorry to be so thick. Where do I select or type in 'CURRENT_DATE'? In the Table or somewhere in the form field properties? I still don't understand what a column is.

probe1 · Posted: Fri Nov 07, 2008 9:19 am Post subject:

Try the WIki en.wikipedia.org/wiki/Column_(database)

For changing the column definition (someone cannot define it on table creation time), follow r4zoli's advice:

Open your Base file.
Tools>SQL...
There you can type SQL commands.

Available commands depend on data base engine.
Find the name of the engine on status bar from the base file.
If it's HSQLDB you'll find the manuals here: HSQLDB Documentation

Other sources: Tutorial on Structured Query Language

If you have change your column in the way described you don't need a field in a form to be filled: the data base engine will do it on any update operation automatically.

Got it?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs

elvira-kate · General User Joined: 23 Oct 2008 Posts: 26

Thank you - I've found the SQL input, but I tried to type in probe1's code and it gave me this error:

probe1 · Posted: Fri Nov 07, 2008 1:10 pm Post subject:

Substitute tablename with the name of your table.
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs

elvira-kate · General User Joined: 23 Oct 2008 Posts: 26

Sorry, it does the same thing: there is only one table and it is called 'mailing'

sliderule · Super User Joined: 29 May 2004 Posts: 1357

Kate:

Welcome to the world of OpenOffice Base.

The solution, is, simple, once the rules are known Smile

When entering an SQL statement . . . a few 'rules', a few of which I have tried to list below.

When entering a LITERAL . . . a LITERAL must be surrounded by SINGLE QUOTES . . . for example . . . 'OpenOffice' . . . or . . . 'Base' . A literal might be used when you want to 'assign' a column with a specific value, or, default value.
When entering a Column ( Field ) or Table name . . . it must be surrounded by DOUBLE QUOTES . . . for example . . . "MyTable" . . . or . . . "mailing"
When entering a Column, or, Table name . . . the name must be entered EXACTLY as defined in the database. This means, the 'names' ARE CASE SENSITIVE. I mean . . . UPPER CASE and lower case . So, for example, a Table defined as "mailing" is NOT the same as "Mailing", nor, "MAILING".

Now that I have said all of the above . . . perhaps . . . the following might help.

elvira-kate · General User Joined: 23 Oct 2008 Posts: 26

Wow. Am not sure I'll ever be any good at this. The goalposts seem to change as soon as you look at them! And sadly, there is still a problem:

sliderule · Super User Joined: 29 May 2004 Posts: 1357

Kate:

Sorry.

Now, I could be mistaken ( will not be the first time for me today, and, most assuredly, not the last Smile

) . . . but . . . I think you can 'automatically' insert ( set by default ) either the date, time, or, timestamp ( both date and time ) to a field WHEN THE ROW IS CREATED ( added to the database ), BUT, when any field ( column ) in the table is 'changed - updated' . . . you have to code the new value ( date / time etc ).

So, the following SQL, in your case, will place the current date in the field, when a row is ADDED ( that is a NEW row ). If we assume your TABLE name is mailing and the field name is Date_Added ( change the above to your actual names ) . . .