report "this expression is typed Incorrectly...", query works

G

Guest

I have a report that is built on a query called ordersQuery. The ordersQuery
prompts for a start and end date.

I have been using the report with no problem for many years. I load our
order records into the table, run the report, the query prompts for start and
end date and the report works as expected.

Recently I loaded may 2006 data and ran the report. After entering
05/01/2006 and 05/31/2006 as the start and end date respectively, I received
the error: "the expression is typed incorrectly or it is too complex to be
evaluated..."

When I execute the underlying query, the query works perfectly. Here are
some observations for the report:
- entering the range 05/01/2006 and 05/31/2006 does not work
- entering the range 05/01/2006 and 05/04/2006 works
- entering the range 05/05/2006 and 05/05/2006 works
- entering the range 05/06/2006 and 05/31/2006 works
- entering the range 05/01/2006 and 05/06/2006 does not work

Here is the underlying query as generated by the design editor:

SELECT webOrders.ID AS ID, webOrders.DATE AS Expr3, webOrders.BILL_FNAME AS
BILL_FNAME, webOrders.BILL_LNAME AS BILL_LNAME, webOrders.SHIP_STATE AS
SHIP_STATE, webOrditems.LINE_ID AS LINE_ID, webOrditems.QUANTITY AS QUANTITY,
webOrditems.NAME AS Expr9, webOrditems.PRICE AS PRICE,
Left([weborditems]![NAME],5) AS discount, Mid([weborditems]![NAME],7) AS
stripName, IIf(InStr(LCase([NAME]),"future")>0,True,False) AS futures,
[QUANTITY]*[PRICE] AS grossPrice,
IIf([futures],[grossPrice]*0.01,[grossPrice]*0.025) AS commission,
[grossPrice]-[grossPrice]*[discount]/100 AS discGrossPrice,
IIf([futures],[discGrossPrice]*0.01,[discGrossPrice]*0.025) AS
discCommission, [grossPrice]*[discount]/100 AS discAmt,
orderQuantity.totQuant, IIf([totQuant]>=12,[discCommission],[commission]) AS
actualCommisson, IIf([discount]="00.00","Net","Dis") AS Discountable,
IIf([totQuant]>=12,[discAmt],0) AS actualDiscount, webOrders.cvtDate,
webOrditems.includeCommission, webOrders.includeCommission, webOrders.checked
FROM (webOrders INNER JOIN orderQuantity ON webOrders.ID =
orderQuantity.Expr1) INNER JOIN webOrditems ON webOrders.ID =
webOrditems.ORDER_ID
WHERE (((webOrders.cvtDate) Between [Type the beginning date:] And [Type the
ending date:]) AND ((webOrditems.includeCommission)=Yes) AND
((webOrders.includeCommission)=Yes) AND ((webOrders.checked)=Yes))
ORDER BY webOrditems.LINE_ID, webOrders.cvtDate, webOrders.DATE;

tia,
jd
 
G

Guest

Hi Jeff,

I don't know if this will help, but you can try defining [Type the beginning
date:]
and [Type the ending date:] as explicit parameters by adding them to the
Query Parameters dialog box. To do this, open the query in design view.

1.) Copy the parameter exactly as you have it in the QBE grid. You'll need
to do these one at a time. For example, copy: [Type the beginning date:]

2.) Click Parameters on the Query menu. Paste the parameter that you copied
into the Query Parameters dialog box, and then set the data type as
Date/Time. Make sure that you do not accidently add a leading space to the
parameter when pasting it in (an easy mistake to make).

3.) Close the parameters box. Repeat steps 1 and 2, using [Type the ending
date:].


In addition, you should know that Date is a reserved word. You should avoid
naming anything in Access with reserved words. Here is a nice compiled list
of reserved words to avoid:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

jeff davidson said:
I have a report that is built on a query called ordersQuery. The ordersQuery
prompts for a start and end date.

I have been using the report with no problem for many years. I load our
order records into the table, run the report, the query prompts for start and
end date and the report works as expected.

Recently I loaded may 2006 data and ran the report. After entering
05/01/2006 and 05/31/2006 as the start and end date respectively, I received
the error: "the expression is typed incorrectly or it is too complex to be
evaluated..."

When I execute the underlying query, the query works perfectly. Here are
some observations for the report:
- entering the range 05/01/2006 and 05/31/2006 does not work
- entering the range 05/01/2006 and 05/04/2006 works
- entering the range 05/05/2006 and 05/05/2006 works
- entering the range 05/06/2006 and 05/31/2006 works
- entering the range 05/01/2006 and 05/06/2006 does not work

Here is the underlying query as generated by the design editor:

SELECT webOrders.ID AS ID, webOrders.DATE AS Expr3, webOrders.BILL_FNAME AS
BILL_FNAME, webOrders.BILL_LNAME AS BILL_LNAME, webOrders.SHIP_STATE AS
SHIP_STATE, webOrditems.LINE_ID AS LINE_ID, webOrditems.QUANTITY AS QUANTITY,
webOrditems.NAME AS Expr9, webOrditems.PRICE AS PRICE,
Left([weborditems]![NAME],5) AS discount, Mid([weborditems]![NAME],7) AS
stripName, IIf(InStr(LCase([NAME]),"future")>0,True,False) AS futures,
[QUANTITY]*[PRICE] AS grossPrice,
IIf([futures],[grossPrice]*0.01,[grossPrice]*0.025) AS commission,
[grossPrice]-[grossPrice]*[discount]/100 AS discGrossPrice,
IIf([futures],[discGrossPrice]*0.01,[discGrossPrice]*0.025) AS
discCommission, [grossPrice]*[discount]/100 AS discAmt,
orderQuantity.totQuant, IIf([totQuant]>=12,[discCommission],[commission]) AS
actualCommisson, IIf([discount]="00.00","Net","Dis") AS Discountable,
IIf([totQuant]>=12,[discAmt],0) AS actualDiscount, webOrders.cvtDate,
webOrditems.includeCommission, webOrders.includeCommission, webOrders.checked
FROM (webOrders INNER JOIN orderQuantity ON webOrders.ID =
orderQuantity.Expr1) INNER JOIN webOrditems ON webOrders.ID =
webOrditems.ORDER_ID
WHERE (((webOrders.cvtDate) Between [Type the beginning date:] And [Type the
ending date:]) AND ((webOrditems.includeCommission)=Yes) AND
((webOrders.includeCommission)=Yes) AND ((webOrders.checked)=Yes))
ORDER BY webOrditems.LINE_ID, webOrders.cvtDate, webOrders.DATE;

tia,
jd
Click to expand...
 
Top