Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    14

    Red face Unanswered: SELECT statement doesnt work

    hi

    the following SELECT statement doesnt work. a error msg : - Query input must contain at least one table or query. (Error 3067)
    is promted . can u explain or suggest a way around


    For N = 1 To N = 255 Step 1
    CQ = " SELECT * FROM TELEPHONE_DIRECTORY" & _
    " WHERE LEFT(LAST_NAME,N)LIKE [something1] " & _
    " ORDER BY LAST_NAME; "
    Next N

  2. #2
    Join Date
    Jan 2004
    Location
    BC - It's the place to be.
    Posts
    13

    Re: SELECT statement doesnt work

    Originally posted by ONIL
    the following SELECT statement doesnt work. a error msg : - Query input must contain at least one table or query. (Error 3067)

    For N = 1 To N = 255 Step 1
    CQ = " SELECT * FROM TELEPHONE_DIRECTORY" & _
    " WHERE LEFT(LAST_NAME,N)LIKE [something1] " & _
    " ORDER BY LAST_NAME; "
    Next N
    I ran that query in a sample database and it worked fine as it is, however I'm not too sure about your N index as you did not explain much what you are trying to do.

    But basically, make sure that the table TELEPHONE_DIRECTORY does exist or that the spelling is correct and that it can be "seen" through your connection.

    Other than that, we need to know more details if you can spare them.

    Ref: select * from <table> where left(<field>,index) like (<condition>) order by <field>

    is ok in itself.

    I hope others can help you more.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    As "N" has been declared a LOOP variable, the following code is required,

    "WHERE LEFT(LAST_NAME,"& N &")"

    If the code "LEFT(LAST_NAME, N)" is passed to Access as an SQL Query, the user will be prompted for the value of "N" providing there is no column named "N".
    Last edited by r123456; 02-08-04 at 08:10.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Feb 2004
    Posts
    14

    Re: SELECT statement doesnt work

    hi

    thanks for helping. when the for N= 1 to N= 255 is removed and in the syntax N is replaced with a numerical the select statement works prefectly

    other details are as follows: -

    For N = 1 To N = 255 Step 1
    CQ.SQL = "PARAMETERS something1 STRING; SELECT * FROM TELEPHONE_DIRECTORY" & _
    " WHERE LEFT(LAST_NAME," & N & ")LIKE [something1] " & _
    " ORDER BY LAST_NAME; "
    Next N

    ' THE FOLLOWING ALSO DOES NOT WORK !?!?!?!?!?
    ' IT POPULATES THE LIST BOX WITH ALL RECORDS
    ' CQ.SQL = "PARAMETERS something1 STRING; SELECT * FROM TELEPHONE_DIRECTORY" & _
    ' " WHERE LAST_NAME LIKE '[something1]' " & _
    ' " ORDER BY EXTENSION_No; "

    Originally posted by stadian
    I ran that query in a sample database and it worked fine as it is, however I'm not too sure about your N index as you did not explain much what you are trying to do.

    But basically, make sure that the table TELEPHONE_DIRECTORY does exist or that the spelling is correct and that it can be "seen" through your connection.

    Other than that, we need to know more details if you can spare them.

    Ref: select * from <table> where left(<field>,index) like (<condition>) order by <field>

    is ok in itself.

    I hope others can help you more.

  5. #5
    Join Date
    Feb 2004
    Posts
    14
    hi thanks for the help but it did not work


    also kindly look at this issue too
    ' THE FOLLOWING ALSO DOES NOT WORK !?!?!?!?!?
    ' IT POPULATES THE LIST BOX WITH ALL RECORDS
    ' CQ.SQL = "PARAMETERS something1 STRING; SELECT * FROM TELEPHONE_DIRECTORY" & _
    ' " WHERE LAST_NAME LIKE '[something1]' " & _
    ' " ORDER BY EXTENSION_No; " ' inserting * or % also doesn't help

    Originally posted by r123456
    As "N" has been declared a LOOP variable, the following code is required,

    "WHERE LEFT(LAST_NAME,"& N &")"

    If the code "LEFT(LAST_NAME, N)" is passed to Access as an SQL Query, the user will be prompted for the value of "N" providing there is no column named "N".

Posting Permissions

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