Results 1 to 7 of 7
  1. #1
    Join Date
    May 2007
    Posts
    3

    Unanswered: Invalid Identifier

    Guys,

    I have been asked to roll back the years and do some development work on botched attempt by one of my previous colleagues to create a Project Management Dashboard...

    Anyway I have hit an error today and I can't get past it!

    I am submitting a query from VB in Excel to an Oracle Database as below;

    rs.Open "SELECT CUSTOMER_NAME, TO_CHAR(START_DATE), TO_CHAR(END_DATE), PROJECT_MANAGER, HEAD_OF_PROJECTS, PRODUCT, CLARITY_ID, PROJECT_SCOPE, OVERALL_STATUS, CONTRACT, CURRENT_PHASE, PERCENT_COMPLETE, EST_PERCENT_TO_COMPLETE FROM PROJECT, PROJECT_LAYER WHERE PROJECT.PROJECT_ID = PROJECT_LAYER.PROJECT_ID and PROJECT.PROJECT_ID = " + strProject + " and TO_CHAR(LAYER_DATE, 'DD-MON-YY') = " + strReportDate, con

    and I am getting the following error;

    Run-time error '-214721900 (80040e14)':

    [Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: "MAY": invalid identifier

    strReportDate is a string and contains '15-MAY-07'

    I have tried the code directly against the database via Toad, hardcoding the variables as they are and it works.

    Any ideas?

    cheers

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    and TO_CHAR(LAYER_DATE, 'DD-MON-YY') = " + strReportDate, con
    Do the lack of a closing quote plus a dangling ", con" have anything to do with it?
    EDIT: Being foolish, nevermind about th quote

    And why do you convert your LAYER_DATE to char instead of converting strReportDate to date?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh, oh oh!
    Actually...
    Code:
    and TO_CHAR(LAYER_DATE, 'DD-MON-YY') = '" + strReportDate + "'", con
    Might cover it....
    Remeber that strings need to be treatead as strings (wrapped in singles)
    George
    Home | Blog

  4. #4
    Join Date
    May 2007
    Posts
    3
    excellent that works perfectly now, thank you very much.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It was the quotes, wasn't it?
    And you are more than welcome
    George
    Home | Blog

  6. #6
    Join Date
    May 2007
    Posts
    3
    It most definitely was the quotes......

    thankfully the day is almost over so don't have to hang my head in shame too much longer!

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Luky for some!
    May work day has only began 2 hours and 15 minutes ago *sigh*
    George
    Home | Blog

Posting Permissions

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