Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2006
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too Complex To Be Evaluated (2003)

    I am trying to create a query that the Month End date is filtered based on a text box from a form.

    Form Name: FrmHNWDashboard
    Text Box: TxtMonthEnd
    The text box defaults to last month's month ending date using DateSerial(Year(Now()),Month(Now())+0,0)
    The text box is also formatted as a Short Date

    I have created a month ending date in a column called "Month End" in a previous query based on a date field called [Signed and Complete]
    DateSerial(Year([Signed and Complete]),Month([Signed and Complete])+1,0)

    In the query that I am trying to build I include the Month End field from the previous query and in the Criteria section I am filtering based on the form using [forms]![FrmHNWDashboard]!TxtMonthEnd]
    I have also put this into the Parameters section for the query with the Data Type as date/time.

    When I try to run this query I get the error message:

    "The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning some parts of the expression to variables."

    Just in case it was a typing error in any of my information I have rebuilt the query multiple times as well as creating a different text box on the form and using that in my query. This was unsuccessful.

    Any help with this situation would be greatly appreciated. Have a great day.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too Complex To Be Evaluated (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Complex To Be Evaluated (2003)

    The problem appears to be that you have at least one record in the first query where the date is Null and so the DateSerial() function is unable to calculate the ending date. Enclose the DateSerial() function in that first query in an Iif() function like this:

    Iif(Not IsNull(ORDER_DATE), DateSerial(Year([ORDER_DATE]),Month([ORDER_DATE])+1,0), Null) AS EndDate

  4. #4
    New Lounger
    Join Date
    Jan 2006
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Complex To Be Evaluated (2003)

    YOU ARE THE MAN!!!!!

    I don't know why that worked because in my original query I put "Is Not Null" in the criteria section for the [Signed and Complete] column, but when I added that if statement where I calculated the month ending date it worked. This is going to be very useful to me. Do you know why it wouldn't work if I filtered just in the criteria section for the [Signed and Complete] column? Either way thanks a million.

  5. #5
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Complex To Be Evaluated (2003)

    I tried using a Not IsNull() on the end date parameter without the Iif() for testing the dates but Access still wanted to evaluate the DateSerial against the records where the value was Null. That's why I put the test in the Iif() function. You can further refine the WHERE clause by specifying to only return records where the dates are not Null but you still need that Iif() function. Adding the test to the WHERE clause may help with performance. Someone else may be able to answer why this is better than I can but I believe the problem is caused by the way Access loads the records into memory on the client machine, based on the SELECT statement, before it applies the conditions in WHERE clause.

    Another thing just for information. I used to use NOT NULL and IS NOT NULL in the WHERE clause of Access queries to stay consistent with SQL Server (I believe that is an ANSI 86 or ANSI 92 standard) but I found that the test didn't always work right. It's been a while now so I don't remember all the details but I now use IsNull() or Not IsNull() in Access and IS NULL or IS NOT NULL in SQL Server and other database server products.

    Patrick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •