Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Error 3071

  1. #1
    Join Date
    Aug 2007
    Posts
    8

    Unanswered: Error 3071

    Hi,

    I am experiencing a strange problem with a query that I cannot resolve.
    The database itself is a 3rd party product so I have limited control of tables structures, relationships, etc. but we can write our own queries, etc.
    In one of the tables there is a 'notes' text field that we are using to record the date when the record was last update and I am trying to write a query to allow us to search for records updated between certain dates.
    The problem is that I get an error saying...
    "This expression has been typed incorrectly, or is too complex to evaluate..." which turns out to be error 3071.

    The error seems to be caused by the last statement in the WHERE clause (see code below). However, this seems like a valid satement and I added it into the SELELCT statement as a test where it works ok. i.e. if I remove the statement from the WHERE clause I get all the records returned but with a Yes/No in the final column generated from the same code (I hope that make sense ). What I don't understand is why it appears to be valid in the SELELCT statement but invalid when in the WHERE statement, I can't see it being a data or data type error because I think I would get an error in the SELECT statement as well.

    This it driving me nuts so any help would be greatly appreciated.

    ---------
    Code....


    SELECT
    Event.EvUID,
    IIf(EventPersonLink.Role="CMP","New","Updated") AS ModType,
    CDate([EventPersonLink.Notes]) AS DateUpdated,
    IIf((CDate(EventPersonLink.Notes)) Between CDate([EnterStartDate]) And CDate([EnterEndDate]),"Yes","No") AS WithinPeriod

    FROM

    Event
    INNER JOIN
    EventPersonLink
    ON Event.EvUID = EventPersonLink.EvUID

    WHERE

    (
    (EventPersonLink.Role)="CMP"
    OR
    (EventPersonLink.Role)="GLE"
    OR
    (EventPersonLink.Role)="REV"
    OR
    (EventPersonLink.Role)="ED"
    )

    AND

    (
    (EventPersonLink.PerUID) Is Not Null
    )

    AND

    (CDate(EventPersonLink.Notes)) BETWEEN CDate([EnterStartDate]) AND CDate([EnterEndDate])

    ;

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a quick tip;
    This:
    Code:
    WHERE 
    (
    (EventPersonLink.Role)="CMP" 
    OR 
    (EventPersonLink.Role)="GLE" 
    OR
    (EventPersonLink.Role)="REV" 
    OR
    (EventPersonLink.Role)="ED"
    )
    Can be summarised "better" (easier to read/understand, no performance hits - correct me if I'm wrong ) as:
    Code:
    WHERE EventPersonLink.Role IN ('CMP','GLE','REV','ED')
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is it the obscure/inconsistant parenthesising in the expression that is causing the problem?

    Try the following re-written statement
    Code:
    SELECT Event.EvUID
         , IIf(EventPersonLink.Role="CMP","New","Updated") AS ModType
         , CDate([EventPersonLink.Notes]) AS DateUpdated
         , IIf((CDate(EventPersonLink.Notes)) Between CDate([EnterStartDate]) And CDate([EnterEndDate]),"Yes","No") AS WithinPeriod
    FROM   Event
     INNER
      JOIN EventPersonLink 
        ON Event.EvUID = EventPersonLink.EvUID
    WHERE  EventPersonLink.Role IN ('CMP','GLE','REV','ED') 
    AND    EventPersonLink.PerUID IS NOT NULL
    AND    CDate(EventPersonLink.Notes) BETWEEN CDate([EnterStartDate]) AND CDate([EnterEndDate])
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2007
    Posts
    8
    That still produces the same error I'm afraid.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This does beg the question - why are you storing a date in a character/text/memo field?
    Also note that "Notes" is probably a reserved word - so you should avoid using it for naming
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2007
    Posts
    8
    unfortunatley it is a 3rd party database so I don't have the ability to add new fields, such as the date field that we would like, so we have used the 'notes' field instead. Not ideal but all we can do at this point.

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    My guess would be that it's related to you asking for Start and End dates once in the Select and once in the Where. Try temporarily eliminating the one in the select statement just to see if it works then.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Aug 2007
    Posts
    8
    I have already removed that statement and it doesn't make any difference. In fact I only added that statement so that I could check the logic was working correctly.

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Is it possible that the Notes field contains a datatype other than a Date or a Null. Cdate([Notes]) would be a problem if the field contained text.
    Inspiration Through Fermentation

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Damnit, that was my next suggestion!
    George
    Home | Blog

  11. #11
    Join Date
    Aug 2007
    Posts
    8
    Yes, I thought of this as well because there are some records with the table that have non-date values in the 'notes' field but these are filtered out by the other statement in the WHERE clause. And also would they not produce an error with the statement in the SELECT field not just when it was placed in the WHERE statement?
    I did worry about whether this would work, i.e. if the first statement was filter out the records with non-date data before the second statement was carried out. So I have rewritten the query using a subquery that does the first filter before the comparision but I still get the error. I have checked the results of the first query and the only values in the 'notes' field appear to be valid dates.

    Here is the updated code in case it helps...

    ----------------
    SELECT
    Event.EvUID,
    IIF(EventPersonLinkFilter.Role="CMP","New","Update d") AS ModType,
    CDATE(EventPersonLinkFilter.Notes) AS DateUpdated
    FROM
    Event
    INNER JOIN
    (SELECT
    EventPersonLink.EvUID,
    EventPersonLink.PerUID,
    EventPersonLink.OrgUID,
    EventPersonLink.Role,
    EventPersonLink.Notes
    FROM
    EventPersonLink
    WHERE
    EventPersonLink.Role IN ('CMP','GLE','REV','ED'))
    AS EventPersonLinkFilter
    ON
    Event.EvUID = EventPersonLinkFilter.EvUID
    WHERE

    (
    (EventPersonLinkFilter.PerUID) Is Not Null
    )

    AND

    (
    CDATE(EventPersonLinkFilter.Notes) BETWEEN CDATE([EnterStartDate]) AND CDATE([EnterEndDate])
    )
    ;

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does this start to get us somewhere?
    Code:
    '--Query1
    SELECT evUID, CDate(Notes) As 'theDate'
    FROM EventPersonLink
    WHERE Role IN ('CMP','GLE','REV','ED')
    
    '--Query2
    SELECT Event.evUID, Query1.theDate
    FROM Event
    INNER JOIN Query1
    ON Query1.evUID = Event.evUID
    WHERE Query1.theDate BETWEEN CDATE([EnterStartDate]) AND CDATE([EnterEndDate])
    George
    Home | Blog

  13. #13
    Join Date
    Aug 2007
    Posts
    8
    The access equivalent of the (see below) still gives the same error....

    --------------
    SELECT
    Event.EvUID,
    IIF(EventPersonLinkFilter.Role="CMP","New","Update d") AS ModType,
    EventPersonLinkFilter.DateUpdated
    FROM
    Event
    INNER JOIN
    [
    SELECT
    EventPersonLink.EvUID,
    EventPersonLink.PerUID,
    EventPersonLink.OrgUID,
    EventPersonLink.Role,
    CDATE(EventPersonLink.Notes) AS DateUpdated
    FROM
    EventPersonLink
    WHERE
    EventPersonLink.Role IN ('CMP','GLE','REV','ED')
    ]. AS EventPersonLinkFilter
    ON
    Event.EvUID = EventPersonLinkFilter.EvUID
    WHERE

    (
    (EventPersonLinkFilter.PerUID) Is Not Null
    )

    AND

    (
    EventPersonLinkFilter.DateUpdated BETWEEN CDATE([EnterStartDate]) AND CDATE([EnterEndDate])
    );

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That was the access equivalent. Create a query (Query1) and save it. Then create a second query based on the first.
    George
    Home | Blog

  15. #15
    Join Date
    Aug 2007
    Posts
    8
    Sorry I misunderstood. Unfortunatley this still produces the same error.
    However, what I have found it that if I set-up the first query to create a table I can the run a second query aginst this and it works. Not really what I wanted I'm sure I can use if as a work around if the error can't be resolved.

Posting Permissions

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