Results 1 to 5 of 5

Thread: SQL Question

  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: SQL Question

    Hi,

    i have an array filled with strings. I want to use the values of this array in an SQL statement. This is the SQL:

    Forms!MissingItems!ContactName.RowSourceType = "Table/Query"
    Forms!MissingItems!ContactName.RowSource = "SELECT tbl_Contact.first_name, tbl_Contact.last_name " & _
    "FROM tbl_Contact " & _
    "WHERE IsNull(tbl_contact.arr(0))=TRUE " & _
    "ORDER BY tbl_Contact.first_name; "
    rien = MsgBox("Double Click on the contact name to add information.", vbInformation)

    arr(0) is an element of the array containing a string that is a field in the table(tbl_Contact). However this SQL statement doenst work. Why can' I do this? im just replacing one string for another.

    thanks for your help

  2. #2
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    Shouldn't the one line be...

    "WHERE IsNull(tbl_contact.[" & arr(0) & "])=TRUE " ...?

    I mean, don't you need to end the sql quote before you reference your array?

  3. #3
    Join Date
    Nov 2003
    Posts
    16
    OK,

    the string is now successfully put after the object name, but the SQL still doent work, it gives me a pop up window asking me to fill the tbl_contact.arr(0) parameter from the SQL statement.

    Any suggestions?

  4. #4
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    I assume your array is already filled in VB?

    On second look, you're also better off with..

    "WHERE tbl_contact.[" & arr(0) & "] IS NULL "

    (i.e. using SQL 'IS NULL' rather than the IsNull.. = true idea)

    As long as your array element is there, the SQL should work.

    Try running the SQL with an actual field name, like..

    "WHERE tbl_contact.[first_name] IS NULL "

    Just to make sure the SQL is valid.

    IF that works, then try again with the array, using a breakpoint in your code, just before the line in question. THen hit ctl-G to get the immediate window, and type:

    ?arr(0)

    If your array is filled, then the field name should show.

    Running these 'tests' might show you where the problem is.

  5. #5
    Join Date
    Nov 2003
    Posts
    16
    alright it seems to work now thanks for your help, if something else goes wrong..ill be back

Posting Permissions

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