Issues reading date field from a form

[bdean22]

Hi, I am having an issue with getting the value of a date field on a form.

Depending on the variable type I declare I get an error when using the .GetValue method, or later when trying to put it in a string. I am at a loss as to where to go from here so any help appreciated. If more details are required let me know!

Sub	AddJobLabour(oEvent as Object)
	Dim Job_Number As String

	Dim LabourType as String
	Dim LabourStaff as String
	Dim sSQL As String
	Dim LabourHours As Double
	Dim BlankFields as Boolean
	Dim oStatement As Object
	Dim LabourDate as Object
	'Wont work if set as date or string.
	Dim oForm As Object
	Dim oField As Object
	Dim oButton As Object
	BlankFields = False
	oForm = ThisComponent.Drawpage.Forms.getByName("Job_Add_Labour")
    oField = oForm.getByName("Job_No")
	Job_Number = oField.getCurrentValue

 	
	oField = oForm.getByName("Labour_Date") ' This is a date field on the form
	LabourDate = oField.getCurrentValue
	'error here if LabourDate is set to Date or String.

	oField = oForm.getByName("txtHours")
	LabourHours = oField.getCurrentValue
	oField = oForm.getByName("LabourTypeList")
	LabourType = oField.getCurrentValue
	oField = oForm.getByName("LabourStaffList")
	LabourStaff = oField.getCurrentValue
	
     
'Ensure no blank fields
   if IsEmpty(Job_Number) then
    	MsgBox "Job Number is Empty"
    	BlankFields = True
    endif

    if IsEmpty(LabourDate) then
    	MsgBox "Item Number is Empty"
    	BlankFields = True
    endif

    if LabourHours<=0 then
    	MsgBox "Qty must be positive"
    	BlankFields = True
    endif

    if LabourType = "" then
    	MsgBox "Item Number is Empty"
    	BlankFields = True
    endif

    if LabourStaff = "" then
    	MsgBox "Item Number is Empty"
    	BlankFields = True
    endif
    
    ' Returns True
    if BlankFields = False then 
		sSQL = "INSERT INTO ""Job_Labour""(""Job_Number"",""Date"",""Staff_Member"",""Labour_Type"",""Actual_Hours"")VALUES('" & Job_Number & "','" & LabourDate &"','" & LabourStaff & "','" & LabourType & "','" & Labour_Hours & "')"
' fails to make SQL statement as object not string.
      if IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
          ThisDatabaseDocument.CurrentController.connect
      endif
      oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
      oStatement.execute(sSQL)
      oForm = ThisDatabaseDocument.FormDocuments.getByName("Job_Add_Items") 
	End if
    
	iJobID = Job_Number
	msgbox "Job ID : " & iJobID
	FormChangeClose("Job_Detail")   
End Sub

[Ratslinger]

Hello,

The data is accessed with additional qualifications of Month, Day and Year. As tested

Dim LabourDate As New com.sun.star.util.Date
oField = oForm.getByName("Labour_Date") ' This is a date field on the form
LabourDate = oField.getCurrentValue
ldYear = LabourDate.Year
Rem access from control drops leading zero - this puts it back
ldMonth = Right("0" & LabourDate.Month,2)
ldDay = Right("0" & LabourDate.Day,2)
Rem display result
Print ldYear & ldMonth & ldDay

For writing Date & Time see this post → Table not updating from form when ‘default’ values applied

[bdean22]

Thank you!

[Ratslinger]

Have also done this using ScriptForge ( The ScriptForge Library )

Code:

Sub gettingDateField
Rem Date Field retrieve value Tested OK 9/14/21 6:24 pm
    GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
Rem Create Document service
    Dim oDoc as Object : Set oDoc = CreateScriptService("Document", ThisDataBaseDocument)
Rem Get internal form; Parameters are external & internal form names
    Dim oForm as Object : oForm = oDoc.Forms("FieldTesting", "Form")
Rem Get field on form
    Dim oControl as Object : oControl = oForm.Controls("DateField1")
Rem Display Value
    MsgBox oControl.Value
End Sub

ScriptForge has some issues. Its’ syntax is different. Not enthusiastic about it at this point.

[Villeroy]

Tutorial and Basic code solving any date/time related problem in Open/LibreOffice (if you understand the problem anyway).
https://forum.openoffice.org/en/forum/viewtopic.php?f=74&t=82181