Microsoft Access Error 3071

[jessef1980]

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

I'm getting this error on a query that I designed. I only get it from one computer and don't understand why. Any incite would be great. here is SQL of the query.


PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Employee.Emp, SYSADM_PRODUCT_SIZE.STYLE, SYSADM_RECEIPT_LINE.QTY, SYSADM_CUSTOMER.LAST_NAME, SYSADM_RECEIPT_LINE.SELLING_PRICE
FROM (((SYSADM_RECEIPT_LINE INNER JOIN SYSADM_PRODUCT_SIZE ON SYSADM_RECEIPT_LINE.SKU = SYSADM_PRODUCT_SIZE.SKU) INNER JOIN Employee ON SYSADM_RECEIPT_LINE.SALES_ASSOCIATE = Employee.SALES_ASSOCIATE) INNER JOIN SYSADM_RECEIPT ON SYSADM_RECEIPT_LINE.RECEIPT_ID = SYSADM_RECEIPT.RECEIPT_ID) INNER JOIN SYSADM_CUSTOMER ON SYSADM_RECEIPT.CUSTOMER_ID = SYSADM_CUSTOMER.CUSTOMER_ID
WHERE (((SYSADM_RECEIPT_LINE.QTY)<>0) AND ((CDate(Left([SYSADM_RECEIPT]![SALES_DATE],10))) Between [Enter Start Date:] And [Enter End Date:]) AND ((SYSADM_RECEIPT_LINE.SALES_CODE)="S") AND ((SYSADM_PRODUCT_SIZE.VENDOR_NO)="31") AND ((SYSADM_RECEIPT_LINE.DEPARTMENT)<>"Misc"))
ORDER BY CDate(Left([SYSADM_RECEIPT]![SALES_DATE],10));

 

[dhookom]

What are some typical values in [SALES_DATE] and what is its data type? Is Vendor_No text and Qty numeric?

Duane
Hook'D on Access
MS Access MVP

 

[lamarw]

Did you distribute this program with MODS making it installable with the runtime module or is each user running their own version of Access? If their own version, what version is the offender at? Is it different than the other successful versions?

 

[jessef1980]

Same version of access on all the computers. In the [sales date] it is formatted with date/time [vendor number] is formatted as a number.

I have never used "MODS" Half the computers seem to run it the other have can't run this query.

 

[lamarw]

Is there a difference in the version of MSAccess on the machines that are working and the ones that are not?

 

[jessef1980]

No all access 2003

 

[dhookom]

jessef1980,
I didn't ask how the values are formatted. It generally doesn't make any difference how they are formatted. "Data type" is not "format".

If Vendor Number is numeric data type then you syntax is wrong. It should be:

((SYSADM_PRODUCT_SIZE.VENDOR_NO)=31) AND

If SALES_DATE is a date/time value then don't use a string function like Left() with it. If you want to remove the time portion of a date and time field, use DateValue() rather than Left().

Duane
Hook'D on Access
MS Access MVP

 

[jessef1980]

worked, I did not have the change the data type in vendor number. I did take out the Left() function.