Início / coding / Perguntas / 79555555
Accepted
BillWee
Asked: 2025-04-04 22:14:17 +0800 CST

Mensagem de erro SQL do Microsoft Access 2021

  • 772

O seguinte SQL do Access usado com um formulário às vezes será executado e às vezes não será. O erro é "A expressão foi digitada incorretamente ou é muito complexa para ser avaliada...". Ele usa três funções VBA para calcular uma Média, Mínimo e Máximo de 5 valores:

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] & "*";

Alguém pode sugerir o que posso mudar para resolver o erro?

sql

1 respostas

  1. Best Answer

    Declare os tipos de dados dos parâmetros para evitar que o Access SQL precise adivinhar:

    PARAMETERS
    [Forms]![SeedConditionalSearch2F]![MinLengthTxt] Text,
    [Forms]![SeedConditionalSearch2F]![MaxLengthTxt] Text,
    ... etc.
    [Forms]![SeedConditionalSearch2F]![CommentsTxt] Text;
SELECT * FROM
    <snip>
    • 0

relate perguntas