Date criteria for query in VBA

[Neruda]

Hi,
have a crosstab query that works ok so far

Need a dynamic date criteria. I use a tempVar from a Form. If I write in vba

TempVars!Select edReportDate = "05/06/2020" this is ok

TempVars!Select edReportDate = "Between 8/05/2020 and 08/06/2020"

this does not work. when I requery the subform I get error 3420 Object invalid or no longer set and the subform gives me 1 records with all the 9 fields shown as #Name?
Tried many combinations with apostrophe but could not get it to work. the two date will come eventually form 2 textboxes on the form. Any ideas? thanks

TRANSFORM CDbl(Nz(Sum([PaymentAmount]),0)) AS AmountTotal
SELECT tblInvoices.DateProcessed AS RepDate, Count(tblPayments.PaymentID) AS CountOfPaymentID, Sum([AmountTotal]) AS GrandTotal
FROM tblInvoices INNER JOIN (tblPaymentType INNER JOIN tblPayments ON tblPaymentType.PaymentTypeID = tblPayments.fkPaymentTypeID) ON tblInvoices.InvoiceID = tblPayments.fkInvoiceID
WHERE (((tblInvoices.DateProcessed)=[TempVars]![SelectedReportDate]))
GROUP BY tblInvoices.DateProcessed
PIVOT tblPaymentType.PaymentType In ("Card","Cash","Other","Voucher","Discount","Refund");

[NeoPa]

Hi Neruda.

It seems you're confusing a value, on the one hand, with code on the other. When you set it to a single date then TempVars!Select edReportDate stores the text value as a string. When you refer to that variable within you SQL it recognizes that the value can be interpreted as a date and so interprets it thus and manages to execute the SQL normally.

When you store the longer string then this is clearly not able to be interpreted as a date value. At no point does the SQL engine try to take the value of TempVars!Select edReportDate and interpret it as part of the SQL instruction code as you seem to be trying to do. If it did it would still fail as the syntax is also wrong. If you open out the value as below you'll see it isn't valid SQL.

WHERE (((tblInvoices.DateProcessed)=[TempVars]![SelectedReportDate]))

becomes

WHERE (((tblInvoices.DateProcessed)=Between 8/05/2020 and 08/06/2020]))

Not that you should worry too much about that. It doesn't support what you're trying to do.

There are various solutions but the one most consistent with how you do things already would be to use two TempVars. You may also want to ensure you save them as Date values rather than strings too, but Jet/ACE will actually handle strings for you if you do it that way as you've already found out.

[Neruda]

so I wrote

Between tempvars!Select edReportDate And tempvars!Select edReportDate1
in the query criteria and it works.
was wondering:if use between keyword, can a date be left blank? (guess it dos not make sense though)
Can the criteria be constructed in vba at all?

[Neruda]

ok, think I got it now, have 2 textboxes instead and do the job as expected, Thanks!

[NeoPa]

Neruda:
Can the criteria be constructed in vba at all?

That's a very important question. Hopefully my answer will help you to understand why.

SQL, as we know, is formed of commands held in Text format. One would expect then, that one could use VBA to create/modify a text string with SQL code in to be used. The truth is that it can - sometimes.

There are many places SQL can be used, and by extension, parts of SQL can be. SQL strings can be found in various Access objects - QueryDefs being the most obvious, but also in the RecordSource properties of Forms & Reports as well as the RowSources of various Controls. Forms & reports, as well as some of the Controls, also have properties that allow filtering to be specified independently of the RecordSource etc. The Filter property of Forms & Reports can be set explicitly as well as automatically when the object is opened.

All of these allow you to specify your SQL but some only allow you to set the SQL by updating the design of the object itself. To be avoided where possible. That does leave setting filters at the point of opening and, something I haven't yet mentioned, Action Queries can also be executed as SQL passed directly as a String, as well as from within a saved QueryDef of course.

So, there are many ways of creating and using SQL that has been created or modified using VBA. Unfortunately though, this flexibility & power comes with certain restrictions.

One of the things I use a lot is having the basic part of the SQL in the Tag property of an object with place-holders for the items I know will change. When the object is opened it will take one or more values as passed and put them into the SQL string before using the result as the RecordSource.

EG. (Simplified) strClientID passed as 'X000365'. Tag contains :

SELECT * FROM [tblClient] WHERE ([ClientID]='%C')

The Form_Open() procedure would include :

With Me
    .RecordSource = Replace(.Tag, "%C", strClientID)
End With

I hope that gives a bit of an idea of what can be done using VBA, as well as why one can't simply use it in all scenarios.