We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Base: "assign a calculated value to a form field" [closed]

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

EasyTrieve gravatar image

updated 2021-05-27 14:25:52 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-14 22:40:55.626943

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 com.sun.star.util.Date
dim PysTime as new com.sun.star.util.Time

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

Question Tools

1 follower


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

Seen: 1,375 times

Last updated: Nov 03 '16