Error: Query too long therefore will be truncated

J

JOM

I would like to add the following statement in my query but an error pops up
to say that my query is too long therefore it was truncated.... How will I
solve this!

***********************************************************
DCount("*","Holidays","HolidayDate Between " &
Format$([RqstDateRcvd],"\#mm\/dd\/yyyy\#") & " And " &
Format$([TxDocDateRcvd],"\#mm\/dd\/yyyy\#") & " And Weekday(HolidayDate, 1)
<> 1 " & " And Weekday(HolidayDate, 1) <> 7 ")
************************************************************

CompOnTime:
IIf([tblTaxDoc]![TaxDocStatus]="Complete",(IIf((([tblTaxDoc]![TaxDocName]="W2"
Or [tblTaxDoc]![TaxDocName]="1099") And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd])-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],7)-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],1)<=10)
Or ([tblTaxDoc]![TaxDocName] In ("1040","1065","1120") And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd]-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],7)-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],1))<=2),"OnTime","OutStd")))
 
J

Jerry Whittle

Try this. In the FROM statement of the SQL make it look something like:
FROM tblTaxDoc T, tblBorrower B
Then where the specific tables are in the SQL, replace them with T or B
respectively.

Also if your table or field names don't have spaces, you can remove the
square brackets [] around them. Lastly Access tends to format SQL with way
too many open and close parenthesis (). You could try getting rid of some of
them.

Statements like this:
[tblTaxDoc]![TaxDocName]="W2" Or [tblTaxDoc]![TaxDocName]="1099")
Could be shortened to:
[tblTaxDoc]![TaxDocName] In ("W2","1099")
 
J

JOM

That worked, but when I to put the in clause where there are only 2 items,
its gives me an error...

Jerry Whittle said:
Try this. In the FROM statement of the SQL make it look something like:
FROM tblTaxDoc T, tblBorrower B
Then where the specific tables are in the SQL, replace them with T or B
respectively.

Also if your table or field names don't have spaces, you can remove the
square brackets [] around them. Lastly Access tends to format SQL with way
too many open and close parenthesis (). You could try getting rid of some of
them.

Statements like this:
[tblTaxDoc]![TaxDocName]="W2" Or [tblTaxDoc]![TaxDocName]="1099")
Could be shortened to:
[tblTaxDoc]![TaxDocName] In ("W2","1099")

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

JOM said:
I would like to add the following statement in my query but an error pops up
to say that my query is too long therefore it was truncated.... How will I
solve this!

***********************************************************
DCount("*","Holidays","HolidayDate Between " &
Format$([RqstDateRcvd],"\#mm\/dd\/yyyy\#") & " And " &
Format$([TxDocDateRcvd],"\#mm\/dd\/yyyy\#") & " And Weekday(HolidayDate, 1)
<> 1 " & " And Weekday(HolidayDate, 1) <> 7 ")
************************************************************

CompOnTime:
IIf([tblTaxDoc]![TaxDocStatus]="Complete",(IIf((([tblTaxDoc]![TaxDocName]="W2"
Or [tblTaxDoc]![TaxDocName]="1099") And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd])-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],7)-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],1)<=10)
Or ([tblTaxDoc]![TaxDocName] In ("1040","1065","1120") And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd]-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],7)-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],1))<=2),"OnTime","OutStd")))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top