Hello,
I come here after many tests and web browsing…
I have a Base-embedded Firebird database. From a dialog box, I get in a macro the value from the date control, in order to insert it into the table through a SQL command.
The fact is that it is not mandatory to fill the date control, and so some record will come with no date value, while other yes.
My questions are
- how to declare the variable in the macro (variant ? string ? date ? integer ? long ?)
- which value and form give it
- how to input it in the SQL command.
For the moment, I’ve tried:
DIM mydateyear AS VARIANT
DIM mydatemonth AS VARIANT
DIM mydateyear AS VARIANT
DIM mydate AS VARIANT
...
mydateyear = Dlg.getControl("DateControl").Date.Year
mydatemonth = Dlg.getControl("DateControl").Date.Month
mydateday = Dlg.getControl("DateControl").Date.Day
If dateyear = 0 Then 'meaning no date has been selected'
mydate = "NULL"
Else
mydate = mydateyear & "-" & mydatemonth & "-" & mydateday ' to get the appropriate format yyyy-mm-dd'
End If
...
sSQL = "INSERT INTO ""myTable"" (""myDateField"") VALUES ('"+mydate+"')"
This works well when a date is provided in the control in the dialog. But i get an error when no date is provided. Alternatively, I’ve tried the following:
'same as above'
sSQL = "INSERT INTO ""myTable"" (""myDateField"") VALUES (" & mydate & ")"
This option works well with no date input (NULL option), but not when a date is provided.
What could be the compromise so that in both cases (date/no date) the code works?
Thank you very much for your help!
Benjamin
[EDIT] & [SOLUTION]
Having tried again, and looked deeper inside documentation (particularly OpenOffice.org french document Initiation à l’utilisation de SQL avec OOo Basic dans Base), it seemed that it was a problem of quote…
So here is the option that seemed to work in both cases (input date/no input date):
...
If dateyear = 0 Then 'meaning no date has been selected'
mydate = "NULL"
Else
mydate = "'" & mydateyear & "-" & mydatemonth & "-" & mydateday & "'"
' to get the appropriate format yyyy-mm-dd'
End If
...
sSQL = "INSERT INTO ""myTable"" (""myDateField"") VALUES (" & mydate & ")"