Query: changed criteria and broke it

[wendyp]

Hi.

I have a query with a column called datInstall (date). When I place the criteria of not null on it, a procedure where I use the query works fine:

(in the procedure)
rsQry.Open "qryPaySalesTax", CurrentProject.Connection, adOpenStatic, adLockReadOnly


When I change the criteria for that one column to:
Between [Forms]![frmFinReports]![txtBegDat] And [Forms]![frmFinReports]![txtEndDat]

I get the following error on the rsQry.Open line:

Error No: -2147217900
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The query itself runs fine from the database window. I've looked at the SQL for the query and it seems OK - very, very long, but OK. (I'll post it the last thing).

Why does changing the criteria mess it up? I could (and have in the past, taken the SQL from the query and placed it into an SQL statement which I modify with VBA and then do rs.Open SQL

But I'd really rather not do that this time - this is for a report that will be changing a lot in the next month and it would be easier to modify a query than the code (although if it breaks every time, maybe that's not true)

Here's the SQL:
SELECT qryPayBaseOrder.datInstall, tblCustomer.strCustState, tblCustomer.strCounty, tblCustomer.strCustCity, IIf(IsNull([strCustZip]),"",Left([strCustZip],5)) AS Zip, tblCustomer.blnCounty, GetTaxAuth([strCustCity],[strCounty],[Zip],[blnCounty]) AS TaxAuth, GetTaxKey([strCustCity],[strCounty],[Zip],[blnCounty]) AS TaxKey, qryPayBaseOrder.TotalNonTaxable, qryPayBaseOrder.TotalTaxable, qryPayBaseOrder.SalesTax, IIf(IsNull([numStateTaxRate]),0,[numStateTaxRate]) AS StateTaxRate, IIf(IsNull([numCityTaxRate]),0,[numCityTaxRate]) AS CityTaxRate, IIf(IsNull([numCountyTaxRate]),0,[numCountyTaxRate]) AS CountyTaxRate, IIf(IsNull([numDiscountTaxRate]),0,[numDiscountTaxRate]) AS DiscountTaxRate
FROM tblCustomer LEFT JOIN qryPayBaseOrder ON tblCustomer.autCustomerID = qryPayBaseOrder.lngCustomerID
WHERE (((qryPayBaseOrder.datInstall) Between [Forms]![frmFinReports]![txtBegDat] And [Forms]![frmFinReports]![txtEndDat]));

 

[Zion7]

Maybe this is obvious, but "frmFinReports", HAS TO BE OPEN
in order to obtain the values, otherwise, your SQL statement
will be invalid?

sorry, if you've done this already....

 

[wendyp]

Thanks for the idea. I won't say that I haven't done something like that. Not the case now though.

frmFinReports has 2 text fields: txtBegDat and txtEndDat
to pick the beginning of the reporting period and the end

The user fills these in (or they default to the current day) and then selects the report. The button runs a process which opens this query and then creates a table for the report to use. I could just pass the installDate to the new table and use the report to filter it, but then the query would retrieve everything and it would be even slower than it is.

Anyway, that's a long way of saying the form has to be open, because the button is on it.

Thanks again, Wendy

 

[PHV]

A recordset can't be based on a parametized query, I think.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886