Invalid SQL statement - when trying to run a query with form parameters

[Santiago Gomez]

Hello,

I am getting an error when I run vba code that looks at the following query:

SELECT qryCombinedKVA.Date, qryCombinedKVA.Total, qryCombinedKVA.Max,
qryCombinedKVA.Avg, qryCombinedKVA.Min
FROM qryCombinedKVA
WHERE (((qryCombinedKVA.Date) Between [Forms]![frmDateRange].[calStart] And
[Forms]![frmDateRange].[calEnd]));

The query is looking at another query for some of its data. It is also
filtering by a date range based on calendar controls on the open form.

Here is the code:

Sub RetrieveQuery()
Dim rst As New ADODB.Recordset

rst.Open "qryFormDateRangeKVA", CurrentProject.Connection, adOpenStatic
With rst
Do While Not .EOF
Debug.Print .Fields("Date")
.MoveNext
Loop
.Close
End With
End Sub

If I run this against a table it works, if I run this against a simple query
it works. Is there a limitation for queries based on sub-queries?

Thanks

 

[Santiago Gomez]

I tried using DAO instead like this:

Dim db As DAO.Database
Dim rst1 As DAO.Recordset

Set db = CurrentDb()
Set rst1 = db.OpenRecordset("qryFormDateRangeKVA", dbOpenDynaset)

and now it is saying that "too few parameters. 2 expected."

It is looking for the [Forms]![frmDateRange].[calStart] And
[Forms]![frmDateRange].[calEnd]. Why is it not getting them form the open
form?

I do a debug.print and they are correctly assigned. The query is just not
finding them.

please help.

thanks

 

[Van T. Dinh]

When you execute the Query through the GUI, the Expression Service resolves
the references to the Form Controls for you.

When you execute the Query through VBA code, the Expression Service is not
available so you have to resolve the Forms Controls before JET can execute
the Query.

Try: (untested)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst1 As DAO.Recordset
Dim param As DAO.Parameter

Set db = CurrentDb()
Set qdf = db.QueryDefs(("qryFormDateRangeKVA")
For Each param in qdf.Parameters
param = Eval(param.Name)
Next param
Set rst1 = qdf.OpenRecordset(dbOpenDynaset)

....


--
HTH
Van T. Dinh
MVP (Access)

I tried using DAO instead like this:

Dim db As DAO.Database
Dim rst1 As DAO.Recordset

Set db = CurrentDb()
Set rst1 = db.OpenRecordset("qryFormDateRangeKVA", dbOpenDynaset)

and now it is saying that "too few parameters. 2 expected."

It is looking for the [Forms]![frmDateRange].[calStart] And
[Forms]![frmDateRange].[calEnd]. Why is it not getting them form the open
form?

I do a debug.print and they are correctly assigned. The query is just not
finding them.

please help.

thanks

Hello,

I am getting an error when I run vba code that looks at the following query:

SELECT qryCombinedKVA.Date, qryCombinedKVA.Total, qryCombinedKVA.Max,
qryCombinedKVA.Avg, qryCombinedKVA.Min
FROM qryCombinedKVA
WHERE (((qryCombinedKVA.Date) Between [Forms]![frmDateRange].[calStart] And
[Forms]![frmDateRange].[calEnd]));

The query is looking at another query for some of its data. It is also
filtering by a date range based on calendar controls on the open form.

Here is the code:

Sub RetrieveQuery()
Dim rst As New ADODB.Recordset

rst.Open "qryFormDateRangeKVA", CurrentProject.Connection, adOpenStatic
With rst
Do While Not .EOF
Debug.Print .Fields("Date")
.MoveNext
Loop
.Close
End With
End Sub

If I run this against a table it works, if I run this against a simple query
it works. Is there a limitation for queries based on sub-queries?

Thanks