The following Access SQL used with a form sometimes will and sometimes will not run. The error is "The expression is typed incorrectly, or is too complex to be evaluated...". It uses three VBA functions to calculate an Average, Minimum, and Maximum of 5 values:
SELECT * FROM
(SELECT SeedID, LocationID, SeedLength, SeedWidth, SeedPoint1, SeedPoint2, SeedPoint3,
SeedPoint4, SeedPoint5,
AvgValue([SeedPoint1], [SeedPoint2], [SeedPoint3], [SeedPoint4], [SeedPoint5]) AS AvgThickness,
MinValue([SeedPoint1], [SeedPoint2], [SeedPoint3], [SeedPoint4], [SeedPoint5]) AS SMin,
MaxValue([SeedPoint1], [SeedPoint2], [SeedPoint3], [SeedPoint4], [SeedPoint5]) AS SMax,
SMax - SMin AS SeedTTV,
SeedFeatures, SeedStrain, SeedPolish, Grade, SeedComments FROM SeedT)
AS Data
WHERE [Data].[LocationID] Is Not Null
AND [Data].[SeedID] LIKE "*" & [Forms]![SeedConditionalSearch2F]![SeedIDTxt] & "*"
AND [Data].[SeedLength] >= Val([Forms]![SeedConditionalSearch2F]![MinLengthTxt])
AND [Data].[SeedLength] <= Val([Forms]![SeedConditionalSearch2F]![MaxLengthTxt])
AND [Data].[SeedWidth] >= Val([Forms]![SeedConditionalSearch2F]![MinWidthTxt])
AND [Data].[SeedWidth] <= Val([Forms]![SeedConditionalSearch2F]![MaxWidthTxt])
AND [Data].[AvgThickness] >= Val([Forms]![SeedConditionalSearch2F]![MinThicknessTxt])
AND [Data].[AvgThickness] <= Val([Forms]![SeedConditionalSearch2F]![MaxThicknessTxt])
AND [Data].[SMin] >= Val([Forms]![SeedConditionalSearch2F]![MinTxt])
AND [Data].[SMax] <= Val([Forms]![SeedConditionalSearch2F]![MaxTxt])
AND [Data].[SeedTTV] >= Val([Forms]![SeedConditionalSearch2F]![MinTTVTxt])
AND [Data].[SeedTTV] <= Val([Forms]![SeedConditionalSearch2F]![MaxTTVTxt])
AND [Data].[SeedStrain] LIKE "*" & [Forms]![SeedConditionalSearch2F]![StrainTxt] & "*"
AND [Data].[SeedPolish] LIKE "*" & [Forms]![SeedConditionalSearch2F]![PolishTxt] & "*"
AND [Data].[Grade] LIKE "*" & [Forms]![SeedConditionalSearch2F]![GradeTxt] & "*"
AND [Data].[SeedComments] LIKE "*" & [Forms]![SeedConditionalSearch2F]![CommentsTxt] & "*";
Can anyone suggest what I can change to resolve the error?
Declare the parameters' data types to free Access SQL from guessing:
PARAMETERS
[Forms]![SeedConditionalSearch2F]![MinLengthTxt] Text,
[Forms]![SeedConditionalSearch2F]![MaxLengthTxt] Text,
... etc.
[Forms]![SeedConditionalSearch2F]![CommentsTxt] Text;
SELECT * FROM
<snip>