Ask Your Question

Base: "assign a calculated value to a form field"

asked 2016-11-02 10:45:47 +0200

EasyTrieve gravatar image

Q1) In Base table Edit, for a "Date/Time [ DATETIME ]" type field, under Default value, how to I set the date/time to now, as in right this second, or =now()?

The only thing I can seem to enter for a Date/Time [ DATETIME ] field in the Default value box is an integer. Can one calculate a default value? There doesn't seem to be any relevant help via F1 for the "Default value" input box.

I am converting a bunch of databases from MS Access and have many tables containing a Created field. In MS Access this is set to =Now() or similar functions.

Q2) Similarly, I have some fields named "Edited" that want to get updated with the date/time stamp when the record is edited. How do I do that? (This might have to be done at the Form level via an event, but I think the the Created fields in Q1 should be done at the Table level if at all possible.)

Using LO on a MariaDB 10.1, but I would like a solution that is universal if at all possible.

This is really the same exact question, as was closed without ever being answered here.


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-11-02 11:53:05 +0200

pierre-yves samyn gravatar image

updated 2016-11-03 11:24:29 +0200


Run ToolsSQL , type (paste) this command (adapt to your table & column names):


Click Execute

Of course your field need to be Date/Time [Timestamp]


If MS Access doesn't support Timestamp, you can associate this script to the Before Record Action event.

I suppose you have two controls: Date Field & Time Field (named TSDate & TSTime in the script, adapt the name of course)

Sub PysTimeStamp

dim oCtrlDate as object, oCtrlTime as object

dim PysDate as new
dim PysTime as new

oCtrlDate = thiscomponent.DrawPage.Forms.getByName("MainForm").getByName("TSDate")
oCtrlTime = thiscomponent.DrawPage.Forms.getByName("MainForm").getByName("TSTime")

with PysDate
    .Day = day(date)    
    .Month = month(date)
    .Year = year(date)
end with

with PysTime
    .Hours = hour(now)  
    .Minutes = minute(now)
    .Seconds = second(now)
end with

oCtrlDate.Date = PysDate
oCtrlTime.Time = PysTime

End Sub


edit flag offensive delete link more


Thanks! This helps, and probably would work for most people. However, in my case I'm dual booting a database and so can't change from DateTime to Timestamp because the MS Access I'm using on the other side doesn't support Timestamp, so I'm stuck w/ DateTime. ...Which now from a careful reading of the MySQL docs won't work. They say that the DEFAULT value must be a literal, with one exception, for CURRENT_TIMESTAMP which is magic I guess.

EasyTrieve gravatar imageEasyTrieve ( 2016-11-03 09:10:56 +0200 )edit

answered 2016-11-03 09:17:56 +0200

EasyTrieve gravatar image

The answer unfortunately is no, DEFAULT can not be computed.

It must be a literal, with one special exception, it can be set to CURRENT_TIMESTAMP, which will default the field to the current timestamp, but that only works on a field that is of type timestamp.

So a field of type DateTime type can not be timestamped. Not this way. However it might be possible to run a script inside a form, so I'll work on that.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-11-02 10:45:47 +0200

Seen: 1,172 times

Last updated: Nov 03 '16