Using Version: 5.4.6.2 (x64)
I have a Base form combo-box that needs to use SQL statement assigned by event macro. The SQL statement needs to use quoted column names for HSQL database. In macro, I need to assemble the SQL statement based on values of other controls on the form. My problem is I cannot use [fieldname] syntax as Base crashes using normal SQL mode, presumably due to the SQL statement complexity (see example code below). How can I put quotes around column & table names within a macro? I tried using escaped quotes (") but this does not work.
Note I have tested the SQL statement via SQLTool so I know it will work if I can pass it to the HSQL server.
Set ocSiteName = Events(poEvent).Source
Set osfForm = ocSiteName.getProperty("Parent")
Set odCheckIn = osfForm.Controls("datCheckIn")
Set odCheckOut = osfForm.Controls("datCheckOut")
Set ocSiteType = osfForm.Controls("lbSiteType")
Set ocPowerType = osfForm.Controls("txtPowerType")
Set obSewer = osfForm.Controls("bSewer")
Set onLength = osfForm.Controls("txtRigLength")
ocSiteName.RowSourceType = com.sun.star.form.ListSourceType.SQLPASSTHROUGH
ocSiteName.RowSource = _
"SELECT SiteName,PowerType,Water,Sewer,Length " & _
"FROM Campsites C, Items I " & _
"WHERE ItemCode = " & ocSiteType.Value & _
" AND I.Description = C.SiteType " & _
" AND Status = 'Active' " & _
" AND PowerType = '" & ocPowerType.Value & "' " & _
" AND Sewer = " & obSewer.Value & _
" AND NVL(Length,0) >= " & onLength.Value & _
" AND SiteName NOT IN ( " & _
" SELECT NVL(R.SiteName,'NONE') " & _
" FROM CampingReservations R " & _
" WHERE R.SiteType = '" & ocSiteType.Value & "'" & _
" AND R.PowerType = '50Amp' " & _
" AND ( R.CheckOut > '" & Format(odCheckIn.Value, "yyyy-mm-dd") & "'" & _
" AND R.CheckIn < '" & Format(odCheckOut.Value, "yyyy-mm-dd") & "') " & _
" UNION " & _
" SELECT NVL(C.PeerSite,'NONE') " & _
" FROM CampingReservations R, Campsites C " & _
" WHERE R.SiteName = C.SiteName " & _
" AND R.SiteType = '" & ocSiteType.Value & "'" & _
" AND R.PowerType = '50Amp' " & _
" AND ( R.CheckOut > '" & Format(odCheckIn.Value, "yyyy-mm-dd") & "'" & _
" AND R.CheckIn < '" & Format(odCheckOut.Value, "yyyy-mm-dd") & "')) " & _
"ORDER BY Metric, SiteName"