如何摆脱"您输入的表达式超过查询设计的 1,024 个字符限制

Question

如何摆脱"您输入的表达式超过了查询设计的1024个字符限制？因为我需要根据下面访问中的查询提取大量数据。

请参阅下面的查询。感谢您的帮助。

选择dbo_plp_MCSEMVAL消费验证视图_照明。年，dbo_plp_MCSEMVAL消费验证VIEW_BILLING。月份，dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING。天，dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING。周期，dbo_plp_MCSEMVAL消费验证视图_计费。CONFIGURATION NAME，dbo_plp_MCSEMVAL CONSUMPTION VALIDATION VIEW_BILLING。CONNECTIONMEMBERNAME，dbo_plp_MCSEMVAL消费验证视图_填充。值，dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING。MSSLID，dbo_plp_MCSEMVAL消费验证视图_照明。PRICINGROUPCODE，日期值([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]！[STARTDATE])为起始日期自dbo_plp_MCSEMVAL消费验证视图_照明，输入_BP其中((dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PRICINGROUPCODE)="；BP1B")AND(日期值([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]！[STARTDATE]))介于2021年4月1日和2021年4日30日之间)按dbo_plp_MCSEMVAL消费验证视图分组。年，dbo_plp_MCSEMVAL消费验证VIEW_BILLING。月份，dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING。天，dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING。周期，dbo_plp_MCSEMVAL消费验证视图_计费。CONFIGURATION NAME，dbo_plp_MCSEMVAL CONSUMPTION VALIDATION VIEW_BILLING。CONNECTIONMEMBERNAME，dbo_plp_MCSEMVAL消费验证视图_填充。值，dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING。MSSLID，dbo_plp_MCSEMVAL消费验证视图_照明。PRICINGROUPCODE，日期值([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]！[STARTDATE])HAVING(((dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="；9300140218"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300140226"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300140952"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300310001"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300313005"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300352052"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300469013"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300473072"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300482024"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300582096"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300582120"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300602001"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300629020"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300654028"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300670016"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300761021"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300774099"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300786051"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300786069"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300786085"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300843142"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300894517"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300897973"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300897981"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300964005"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300992725"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300992733"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300992741"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301001294"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301001302"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301001310"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301001328"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301003407"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301003464"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301003472"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301010147"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301010154"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301030434"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301030558"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035045"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035052"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035060"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035144"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035151"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035169"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035276"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035284"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035300"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035318"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035326"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301035334"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301036027"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301040052"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301047008"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301049269"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301049293"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301049301"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9301049319"；或(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)=&

感谢

Answer 1

为表名使用别名并将OR col = val1 OR col = val1 OR col2 = val ...替换为col in (val1, val2, val3, ...)将节省大量字符。它还将使代码更容易理解。我还删除了对Input_BP的引用，除了可能出现大量重复结果外，它似乎没有任何贡献。

SELECT      T.Year,
T.Month,
T.Day,
T.PERIOD,
T.CONFIGURATIONNAME,
T.CONNECTIONMEMBERNAME,
T.VALUE,
T.MSSLID,
T.PRICINGRUNGROUPCODE,
DateValue([T]![STARTDATE]) AS STARTDATE 
FROM        dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING As T
WHERE       (((T.PRICINGRUNGROUPCODE)="BP1B") 
AND         ((DateValue([T]![STARTDATE])) Between #4/1/2021# And #4/30/2021#)) 
GROUP BY    T.Year,
T.Month,
T.Day,
T.PERIOD,
T.CONFIGURATIONNAME,
T.CONNECTIONMEMBERNAME,
T.VALUE,
T.MSSLID,
T.PRICINGRUNGROUPCODE,
DateValue([T]![STARTDATE]) 
HAVING      (T.MSSLID IN ("9300140218", ...));

Answer 2

如果您希望能够访问查询设计生成器，我建议您将重复字段的一半向下复制并粘贴到注释/单词文件中，这样您就可以删除SQL中的一些字段？我不确定这是针对报表还是表单，但请尝试使用VBA来减少查询；或者可以将函数CCD_ 4用于字段MSSLID；9300"；？

(这只是你的代码的简化版本；我不确定你是在做报告或表格，还是只是想做一个查询)

选择dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIDATIONVIEW_BILLING.Year，dbo_plp_MCSEMVAL CONSUMPTION VALIDATIONFIEW_BILLING.MMonth，dbo_plp_MCSEMWAL CONSUMOPTIONVALIDATIONWIE_BILLING.Day，dbo_plp_MCSEAVAL CONSUMptiONVALIDAATIONVIEW-BILLING.PERIOD，dbo_pl p_MCSEMWALCONSUMOPTION VALIDATION_VIEW_BILLING.CONFIGURATIONNAME，dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.VALUE，dbo_plp_MCSEMVAL CONSUMPTION VALIDATIONFIEW_BILLING.MSSLID，dbo_plp_MCSEMWAL CONSUMTITIONVALIDATION VIEW_BILLING.PRICINGRUNGROUPCODE，日期值([dbo_plp_MCSEMVALCONSUMPTionVALIDATIONWIEW_BLLING]！[STARTDATE]，Input_BP WHERE(((dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PRICINGROUPCODE)="；BP1B")AND((日期值([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]！[STARTDATE]))介于2021年4月1日#和2021年4日30日#之间))按dbo_plp_MCSEMVALCONSUMPTION VALIDATION_VIEW_BILLING.年，dbo_plp_MCSEMVAL CONSUMPTionVALIDATIOONVIEW_BILLING.月，dbo_plp_MCSEMWALCONSUMOPTIONVALIDATIONWIEW_BILLING.Day，dbo_plp_MCSEMVAL CONSUMPTION VALIDATION VIEW_BILLING.CONFIGURATIONNAME，dbo_plp_MCSEMVALCONSUMPTIONVALIDATION VIEW _BILLING.CONNECTIONMEMBERNAME，dbo_plp_MCSeMVAL CONsumPTION VALICATION VIEW_BILLING.VALUE，dbo_plp_MCSEMWAL CONSUMTITION VALIDATION VIEW _BILLING.MSSLID，日期值([dbo_plp_MCSEMVALCONSUMPTIONVALIDATION_BILLING]！[STARTDATE])具有；9300140218"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300140226"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300140952"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300310001"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300313005"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300352052"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300469013"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300473072"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300482024"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300582096"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300582120"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300602001"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300629020"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300654028"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300670016"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300761021"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300774099"；或者(dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)＝"；9300786051")；

如果这有帮助，请告诉我！