Results 1 to 7 of 7

Thread: SQL Question

  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: SQL Question

    I have an sql statement that uses values from an aary. Here is the statement. The array is "arr"

    Forms!MissingItems!ContactName.RowSourceType = "Table/Query"
    Forms!MissingItems!ContactName.RowSource = "SELECT tbl_Contact.first_name, tbl_Contact.last_name " & _
    "FROM tbl_Contact " & _
    "WHERE ((tbl_contact.[" & arr(0) & "] Is Null) AND ((tbl_contact.[" & arr(1) & "] Is Null) " & _
    "AND ((tbl_contact.[" & arr(2) & "]) Is Null) AND ((tbl_contact.[" & arr(3) & "]) Is Null) " & _
    "AND ((tbl_contact.[" & arr(4) & "]) Is Null) " & _
    "ORDER BY tbl_Contact.first_name; "
    rien = MsgBox("Double Click on the contact name to add information.", vbInformation)

    A select statement is used to determine how many conditions go into the where statement. In this case six elements are in the array so six conditions are imposed. The problem is that the SQL works when two values of the array are filled and two conditions imposed but it deos'nt work when more then two values of the array are used in the SQL.

    Any ideas why this is so

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Question i'm not sure but.....

    I think your missing some close ')'

    or is this an example

  3. #3
    Join Date
    Nov 2003
    Posts
    16
    here is a more concrete example.

    This SQL statement works, with two of the array values filled.

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


    This SQL deos not work with three of the array values filled. The list box does not get filled

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

    i think the parenthese are right but im not sure

  4. #4
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    In the last where clause line:

    (tbl_contact.[" & arr(2) & "]) Is Null))" & _

    you're missing an open parenthesis.
    it should be

    ((tbl_contact.[" & arr(2) & "]) Is Null))" & _

    Remember to always count them.. the number of opening ones should be equal to the number of closing one.

  5. #5
    Join Date
    Nov 2003
    Posts
    16
    In the case of my first post do i have to restart counting the parenthese every time i change line?

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    in a select statement start counting from the first parenthese to the last one

    a trick i use is for example :

    ()(( )))()

    i start counting up and down so
    1 then minis 1 .plus 2. minus 3. plus 1 .minus 1. if its not 0 im missing some parenthese

    if 0 then OK
    if + then need more Close
    if - then need more Open

    in your case its +2 ---->

    "WHERE ((tbl_contact.[" & arr(0) & "] Is Null) AND ((tbl_contact.[" & arr(1) & "] Is Null) " & _
    "AND ((tbl_contact.[" & arr(2) & "]) Is Null) AND ((tbl_contact.[" & arr(3) & "]) Is Null) " & _
    "AND ((tbl_contact.[" & arr(4) & "]) Is Null) " & _
    "ORDER BY tbl_Contact.first_name; "

    this is wright ---->

    "WHERE ((tbl_contact.[" & arr(0) & "]) Is Null) AND ((tbl_contact.[" & arr(1) & "]) Is Null) " & _
    "AND ((tbl_contact.[" & arr(2) & "]) Is Null) AND ((tbl_contact.[" & arr(3) & "]) Is Null) " & _
    "AND ((tbl_contact.[" & arr(4) & "]) Is Null) " & _
    "ORDER BY tbl_Contact.first_name; "


    Solved ;-)
    or not ??? :-(

  7. #7
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    no, you count for whole WHERE clause
    SQL doesn't pay any attention to how many lines you have, because it all gets put together into one big 'line' anyway.

Posting Permissions

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