Access-SQL query error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bkr6891 - 13 Jan 2007 06:21 GMT

I recently upsized my MS Access BE to an SQL server. Everything seems
to works great with the exception of a few queries (other queries are
still working great). When I try to execute the queries, I get
"expression is typed incorrectly or is too complex to be evaluated"
error message. These queries executed without a problem prior to
upsizing from Access. I will post the code from one of my problem
queries:

PARAMETERS [Forms]![frmCostPerTx]![ClinicNumber] Text ( 255 ),
[Forms]![frmCostPerTx]![BeginningDeliveries] DateTime,
[Forms]![frmCostPerTx]![EndDeliveries] DateTime;
SELECT tblProducts.ProductNumber, tblProducts.ProductName,
tblDeliveries.Quantity, tblClinics.ClinicNumber,
tblClinics.ClinicName, tblCategories.ProductCategory
FROM (tblCategories RIGHT JOIN (tblAccounts RIGHT JOIN tblProducts ON
tblAccounts.AccountID = tblProducts.AccountID) ON
tblCategories.ProductCatID = tblProducts.ProductCatID) LEFT JOIN
(tblClinics RIGHT JOIN tblDeliveries ON tblClinics.ClinicID =
tblDeliveries.ClinicID) ON tblProducts.ProductID =
tblDeliveries.ProductID
WHERE
(((tblClinics.ClinicNumber)=[Forms]![frmCostPerTx]![ClinicNumber]) AND
((tblAccounts.AccountNumber)="1540005") AND
((tblDeliveries.DeliveryDate) Between
[Forms]![frmCostPerTx]![BeginningDeliveries] And
[Forms]![frmCostPerTx]![EndDeliveries]));

Can anyone help me with this problem?

Thanks in advance for your help.
-Brian

Reply to this Message

Alex Dybenko - 13 Jan 2007 08:07 GMT

Hi,
If this is a read-only query - then I suggest to convert it to pass-through
query and pass values of parameters when you run the form. it will also run
faster in this case

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

>I recently upsized my MS Access BE to an SQL server. Everything seems
> to works great with the exception of a few queries (other queries are
[quoted text clipped - 27 lines]
> Thanks in advance for your help.
> -Brian

Reply to this Message

Sylvain Lafontaine - 13 Jan 2007 20:08 GMT

Another possibility would be to create your own recordset and bind it to the
form afterward; see http://support.microsoft.com/?kbid=281998

You could also try to simplify the expression by using views or even using a
single view on the SQL-Server; however, you might have trouble if you want
to have an updatable form. See http://support.microsoft.com/kb/q209123/ for
a possible solution.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

> Hi,
> If this is a read-only query - then I suggest to convert it to
[quoted text clipped - 32 lines]
>> Thanks in advance for your help.
>> -Brian

Reply to this Message