Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231

    Exclamation Unanswered: Big stumper...what's wrong with my SQL?

    Getting an error message once in a while in a database. Worked fine for 6+ months, and has now had this problem twice in the past 2 weeks. Would seem that it depends on the data the query gives back for that day. Gives me "ODBC--call failed." Further examination shows that it gives this error - "[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the substring function. (#536)." - and doesn't like the following line of code:
    Code:
    Set rs=CurrentDb.OpenRecordset("SELECT Count(*) FROM [qryLT/ST/OP] WHERE 
    ([Starts With]='(OP' AND CritNum='300-004-05')")
    But it WILL do the following:
    Code:
    Set rs=CurrentDb.OpenRecordset("SELECT Count(*) FROM [qryLT/ST/OP] WHERE 
    ([Starts With]='(OP' AND CritNum='300-004-05' AND (CompletedDt<>Null))")
    here's an example of the data:
    CritNum CritTitle Starts With EstCmpltnDt CompletedDt
    300-004-05 TITLE THING1 (OP 2/18/2005 3/30/2005
    300-004-05 TITLE THING2 (OP 2/18/2005 2/25/2005
    300-004-05 TITLE THING3 (ST 2/16/2005 2/16/2005


    To further explain, the problem comes along with the CritNum='300-004-05' part. If I remove that from the SQL statement, it works fine. The CritNum field is indeed a substring: Left$([DfcncyTitle],InStr([DfcncyTitle],",")-1) AS CritNum

    Any ideas? This thing is killing me. I don't know if I've given enough information. I'm thinking it has to do with the Left$(,,-1), but can't figure out why. Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Does [DfcncyTitle] always have a comma in it? Is this field ever null or empty? All three of those situations will give you an error. I would filter your table for "*,*" and see if you get all the records returned. If not, that is your problem.

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Thanks for the response. I've got it fixed now.

Posting Permissions

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