Wednesday , November 13 2019
Home / Microsoft Access & VBA / Query is too complex error in MS Access with Solution

Query is too complex error in MS Access with Solution

Query-is-Too-Complex error

Subject:  

Query is too complex  error in MS Access with step by step solution.

Summary:

This is common error in microsoft access. Here I shall discuss about-

  1. Cause to arise Query is too complex error.
  2. The steps to solve this error.

Cause: 

The error can arise for some crucial situation. When your Query SQL String is so long, this error can show and as result query will not run. The another cause is that if you use many IIF conditions in your query, then query SQL string will be so long. In this situation, your query yields this error message.

Solution:

Remove the IIF() conditions from your query and write functions in asscess module for the IFF conditions and finally call the VBA functions in your query field. In result SQL string will be reduced and run query successfully. Then it will not yield any errors.

If no  IFF() function exists in your query then reduce your SQL string from SQL View of your query. I hope, your problem will be solved. See the case study below to solve the problem step by step.

Case Study:

Once I was developing a school management software. In my project I had to design some queries. In that queries, I used many IIF() condition to generate student results like their result GRADE and GPA for each subject (13 Subjects). So, when I tried to run the query, it is showing  “Query is too complex” and my query is terminated to work. Then I opened my query in “SQL View” and then I saw my query SQL string has been too long.

Then I deleted my all IIF() functions which is providing Grade and GPA. Then I wrote function in Access Module using visual basic code. One function is to generate GRADE and another function is to generate GPA. Then I have called the two functions in my Query and pass “SUBJECT_MARKS” as parameter. Then these functions return result Grade and GPA depending on obtained marks of the particular subject like it takes English subject mark 70 and provide Grade “A” and GPA 4. Because I have written in those functions a  condition which is if marks is greater than and equal to 70 (>=70), then it return “A” grade and gpa 4.

Finally I tried to run my new query and it  runs successfully without errors.

When I create a query by using many IIF(), it shows the error.

In Query Field:

BN_GRADE: IIf([BNT]>=80,”A+”,IIf([BNT]>=70,”A”,IIf([BNT]>=60,”A-“,IIf([BNT]>=50,”B”, IIf([BNT]>=40,”C”, IIf([BNT]>=33,”D”,”F”))))))

BN_GPA:IIf([BNT]>=80,5,IIf([BNT]>=70,4,IIf([BNT]>=60,3.5,IIf([BNT]>=50,3,IIf([BNT]>=40,2, IIf([BNT]>=33,1,0))))))

Then I have written a funtion in Access Module which is below and called it in Query instead of IIF(), The Query runs fast successfully.

In Access Module:

Public Function GPA(mr As Integer) As Integer

  Dim g As Integer

  If mr >= 80 Then

    g = 5

  ElseIf mr >= 70 Then

    g = 4

  ElseIf mr >= 60 Then

    g = 3.5

  ElseIf mr >= 50 Then

    g = 3

  ElseIf mr >= 40 Then

    g = 2

  ElseIf mr >= 33 Then

    g = 1

  Else

    g = 0

  End If

    GPA = g

End Function

 

Public Function GRADE(mr As Integer) As String

  Dim gr As String 

  If mr >= 80 Then

    gr = “A+”

  ElseIf mr >= 70 Then

    gr = “A”

  ElseIf mr >= 60 Then

    gr = “A-“

  ElseIf mr >= 50 Then

    gr = “B”

  ElseIf mr >= 40 Then

    gr = “C”

  ElseIf mr >= 33 Then

    gr = “D”

  Else

    gr = “F”

  End If

  GRADE = gr

End Function

 

And call the function in your Query like below:

BN_GRADE: GRADE([BNT])

BN_GPA: GPA([BNT])

This is how; I have solved the “Query is too complex” error.

Visit  to Disable Design View for Forms, Reports, VBA Code in MS Access.

Leave a Reply

Your email address will not be published. Required fields are marked *