Trying to insert a WHERE clause in a SQL script

[Antenore]

I drawed a form in LibreOffice Base with a control proposing several dates (formatted in the table as DATE): once selected one, a macro is charged to insert this date as a WHERE clause in a SQL script.
Script is extract from a generic query (“qListaUtenti_perRPT”), modified and saved in a new query (“qPerRPT_TMP”).
The task is performed, no errors when the macro runs, but when I launch the new query, the result is even “Incorrect data type”.
The problem is I don’t know which is the right syntax to use for the date itself.
I tryed including the date in quotes, double quotes, hash symbol…
Below an example:

Sub StampaSchedaData (poEvent As Object)
Dim oConnexion as Object, ocCombo1 As Object, sSQL As String

	oConnexion = poEvent.Source.Model.Parent.ActiveConnection

	Set ocCombo1 = Events(poEvent).Source

	sSQL =	 oConnexion.queries.getByName("qListaUtenti_perRPT").Command
	sSQL = sSQL & " AND ""00_DATI_UTENTE"".""DATA_INS"" = '" & ocCombo1.Value & "'"

	oConnexion.queries.getByName("qPerRPT_TMP").Command = sSQL
	
End Sub

Thank’s in advance for any help!

[RobertG]

I would try to set
msgbox ocCombo1.Value
to see the value, which is provided there. It has to be written in ‘YYYY-MM-DD’. If this is correct - the code for query will be right.

[Villeroy]

How to filter record sets by dates without a single line of code: https://ask.libreoffice.org/uploads/short-url/tAGmtk91qwUT53NEhE9ci6q8YBc.odb
Notice that the reports use the same filtered record set.

[Antenore]

@ RobertG
This is the result of msgbox ocCombo1.Value

[Antenore]

@ Villeroy
Your example is really very smart, I’ll use it to solve other questions in this DB.
Regarding the present problem, I think I’m so near to make my code working that I’d prefer to learn how to manage date format in Base SQL.

[Villeroy]

The syntax for parsed queries (contrary to direct SQL) is documented here: Query Design
The syntax for direct SQL depends entirely on the actual database engine. For embedded HSQL, the syntax is documented here: Chapter 9. SQL Syntax
An 16(!) year old tutoral of mine is still valid: Apache OpenOffice Community Forum - [Tutorial] Structured Query Language in Base - (View topic)

[Antenore]

Finally, I’d found a solution adding some rows to the script, to change the date in string and to format it as “YYYY-MM-DD”, as below:

Sub StampaSchedaData (poEvent As Object)

Dim oConnexion as Object,  ocCombo1 As Object, sSQL As String
Dim myDate as Date, myDateStr as String, myDateDef as String

	oConnexion = poEvent.Source.Model.Parent.ActiveConnection

	Set ocCombo1 = Events(poEvent).Source
	myDate =  ocCombo1.Value
	
	sSQL =	 oConnexion.queries.getByName("qListaUtenti_perRPT").Command

	myDateStr =  Trim(Str((myDate))
	myDateDef = MID(myDateStr,7,4) & "-" & MID(myDateStr,4,2) & "-" & MID(myDateStr,1,2)

	sSQL = sSQL & " AND ""00_DATI_UTENTE"".""DATA_INS"" = '" & myDateDef & "'"

	oConnexion.queries.getByName("qPerRPT_TMP").Command = sSQL
	
End Sub

It was running good until yesterday, but this morning, with no evident reason (for me at least), it began to stop at row:
Set ocCombo1 = Events(poEvent).Source
with message: “Sub-procedure or function not defined”
What is happening?