Ask Your Question

form macro not handling date correctly

asked 2019-04-28 08:07:38 +0200

jvglynnjr gravatar image

updated 2019-04-28 20:31:04 +0200

I'm trying to pass a formatted field containing a date from a form to a table through an update query, but I can't get it to work. The code below works when I switch the receiving column to a text-formatted one, but I need to be able to put this update into a date-formatted column.

This is the macro I'm using (relevant parts, anyway)

REM  *****  BASIC  *****

const TableName = "tbStockRoom"
const IDField = "SKU"
const ValueField = "On_Shelf"

REM  This needs to be changed to "expDate"
const DateField = "bogus_text"

global oConn as Object
global CtrlId as object, CtrlValue as object

global CtrlDate as object

Sub UpdateADDQuery(oEvent)

dim oQuery as Object 
dim oStatement as Object
dim Result as long

dim SQL as String

dim oForm as Object
dim oField as Object
dim CtlView as Object
dim item as String

    SQL = "update """ & TableName & """ set """ & ValueField & """ = """ & ValueField & """ + " & CtrlValue.currentValue & _
         " , """ & DateField & """ = " & CtrlDate.currentValue & " where """ & IDField & """ = " & CtrlId.currentValue

        oQuery = oConn.createStatement()
        Result = oQuery.executeUpdate(SQL)

End Sub

Sub InitForm(oEvent)

oConn = ThisDatabaseDocument.DataSource.getConnection("","") 
CtrlId = oEvent.source.parent.getByName("Form2").getByName("fmtID")
CtrlValue = oEvent.source.parent.getByName("Form2").getByName("fmtInteger")

CtrlDate = oEvent.source.parent.getByName("Form2").getByName("fmtDate")

It runs fine as is, but when I switch DateField to a date-formated column, I get this error message:

BASIC runtime error. An exception occurred Type: Message: Wrong data type: java.lang.IllegalArgumentException.

I tried inserting a CAST() function into the SQL to force the parameter to be interpreted as date, but I must be doing it wrong because it always gives nonsense syntax. Could this have something to do with handling quotes? I don't really understand why some of the above are single and not tripled, but like I said, this runs just fine.

Any help appreciated.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-04-28 18:25:54 +0200

Ratslinger gravatar image


Regardless of any formatting of dates used, dates in a database are always stored as YYYY-MM-DD and therefore dates used in any SQL need to use this format. Here is a simple conversion of converting a test string to proper format for use in SQL:

myDate = "02/01/2019"
convertedDate =  Format(myDate, "'YYYY-MM-DD'")
edit flag offensive delete link more


Thanks, Ratslinger, that did the trick. I just changed CtrlDate.currentValue to Format(CtrlDate.currentValue, "'YYYY-MM-DD'") in my SQL statement.

jvglynnjr gravatar imagejvglynnjr ( 2019-04-28 20:28:52 +0200 )edit


As you have been helped, please help others to know the question has been answered by clicking on the ✔ in upper left area of answer which satisfied the question. You should do this on all answered questions.

Ratslinger gravatar imageRatslinger ( 2019-04-28 20:34:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-28 08:07:38 +0200

Seen: 28 times

Last updated: Apr 28