CODEWITHSUNDEEP
ms-access
ABC
ABC

Reputation: 25

How to get rid of "the expression you entered exceeds the1,024-character limit for the query design

How to get rid of "the expression you entered exceeds the 1,024-character limit for the query design? As I would need to extract the huge data based on the query in the access below.

Please see my query below. Any help is appreciated.

SELECT dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Year, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Month, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Day, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PERIOD, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.CONFIGURATIONNAME, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.CONNECTIONMEMBERNAME, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.VALUE, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PRICINGRUNGROUPCODE, DateValue([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]![STARTDATE]) AS STARTDATE FROM dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING, Input_BP WHERE (((dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PRICINGRUNGROUPCODE)="BP1B") AND ((DateValue([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]![STARTDATE])) Between #4/1/2021# And #4/30/2021#)) GROUP BY dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Year, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Month, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Day, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PERIOD, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.CONFIGURATIONNAME, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.CONNECTIONMEMBERNAME, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.VALUE, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PRICINGRUNGROUPCODE, DateValue([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]![STARTDATE]) HAVING (((dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300140218" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300140226" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300140952" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300310001" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300313005" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300352052" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300469013" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300473072" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300482024" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300582096" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300582120" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300602001" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300629020" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300654028" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300670016" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300761021" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300774099" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300786051" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300786069" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300786085" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300843142" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300894517" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300897973" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300897981" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300964005" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300992725" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300992733" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300992741" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301001294" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301001302" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301001310" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301001328" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301003407" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301003464" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301003472" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301010147" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301010154" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301030434" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301030558" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035045" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035052" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035060" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035144" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035151" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035169" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035276" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035284" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035300" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035318" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035326" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301035334" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301036027" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301040052" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301047008" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301049269" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301049293" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301049301" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301049319" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301049343" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301064003" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301128006" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301176898" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301218849" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9301344538"));

Thanks

Upvotes: 0

Views: 833

Answers (2)

Nicholas Hunter
Nicholas Hunter

Reputation: 1845

Using an alias for the table name and replacing OR col = val1 OR col = val1 OR col2 = val ... with col in (val1, val2, val3, ...) will save you a lot of characters. It will also make the code a lot easier to understand. I also removed the reference to Input_BP, which did not seem to be contributing anything except the possibility of a ton of duplicate results.

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", ...));

Upvotes: 1

Lauren Ashley Martinez
Lauren Ashley Martinez

Reputation: 76

If you want to be able to access the query design builder, I recommend copying and pasting maybe half of the repeated fields towards the bottom to a note/word file so you can delete some of the fields in SQL? And I'm not sure if this is for a report or a form, but try to use VBA to reduce the query; or maybe use the function Right() for the field MSSLID so you don't have to write the first "9300"?

(This is just a reduced version of your code; I'm not sure if you're making a report or form or if you just wanted to make a query)

SELECT dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Year, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Month, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Day, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PERIOD, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.CONFIGURATIONNAME, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.CONNECTIONMEMBERNAME, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.VALUE, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PRICINGRUNGROUPCODE, DateValue([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]![STARTDATE]) AS STARTDATE FROM dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING, Input_BP WHERE (((dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PRICINGRUNGROUPCODE)="BP1B") AND ((DateValue([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]![STARTDATE])) Between #4/1/2021# And #4/30/2021#)) GROUP BY dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Year, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Month, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.Day, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PERIOD, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.CONFIGURATIONNAME, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.CONNECTIONMEMBERNAME, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.VALUE, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID, dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.PRICINGRUNGROUPCODE, DateValue([dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING]![STARTDATE]) HAVING (((dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300140218" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300140226" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300140952" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300310001" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300313005" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300352052" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300469013" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300473072" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300482024" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300582096" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300582120" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300602001" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300629020" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300654028" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300670016" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300761021" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300774099" Or (dbo_plp_MCSEMVALCONSUMPTIONVALIDATIONVIEW_BILLING.MSSLID)="9300786051"));

Let me know if this helps!

Upvotes: 0

Related Questions