Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: SQL help !! thanks

    Can someone help me with my SQL..

    Im doing a search of unique records.. I can't get it to work after adding the Is null stmts..

    Code:
    SELECT *
    FROM tblInspections
    WHERE ((tblInspections.strDate) = Between [Forms]![Search]![open] OR IsNull([Forms]![Search]![open]) And [Forms]![Search]![close] OR IsNull([Forms]![Search]![close])) AND ((tblInspections.REJ=[Forms]![Search]![REJ]) OR IsNull([Forms]![Search]![REJ])) AND ((tblInspections.strProject=[Forms]![Search]![strProject]) OR IsNull([Forms]![Search]![strProject]));

    thanks

  2. #2
    Join Date
    Jul 2003
    Posts
    292
    This is my original Code:

    Code:
    SELECT *
    FROM tblInspections
    WHERE (((tblInspections.strDate) Between [Forms]![Search]![Open1] And [Forms]![Search]![Open2])) AND ((tblInspections.REJ=[Forms]![Search]![REJ]) OR IsNull([Forms]![Search]![REJ])) AND ((tblInspections.strProject=[Forms]![Search]![strProject]) OR IsNull([Forms]![Search]![strProject]));
    When I add the IsNull stmt in.. I get errors.. What I want is.. even if the user leaves the date field blank.. it'll search for the other fields if asked..


    thanks

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    I think the IsNull function is just to define the data to be true or false and not setting a blank data to your reference object.

  4. #4
    Join Date
    Jul 2003
    Posts
    292
    so is there another way i can leave that field blank on my form and search for others.. vice versa... ?

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    ok I changed it to...

    SQL:

    Code:
    SELECT *
    FROM tblInspections
    WHERE (((tblInspections.strDate) Between [Forms]![Search]![Open1] And [Forms]![Search]![Open2])) OR IsNull([Forms]![Search]![Open1])) OR IsNull([Forms]![Search]![Open2])) AND ((tblInspections.REJ=[Forms]![Search]![REJ]) OR IsNull([Forms]![Search]![REJ])) AND ((tblInspections.strProject=[Forms]![Search]![strProject]) OR IsNull([Forms]![Search]![strProject]));
    And I'm still getting errors.. did I miss something ?

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    The error is.. Extra ) in the query expression

  7. #7
    Join Date
    Jul 2003
    Posts
    292
    ok I tried something else..

    Code:
    SELECT *
    FROM tblInspections As T
    WHERE (T.strDate Between [Forms]![Search]![Open1] And [Forms]![Search]![Open2] 
           OR IsNull([Forms]![Search]![Open1] OR IsNull([Forms]![Search]![Open2])
    
          AND (T.REJ=[Forms]![Search]![REJ] OR IsNull([Forms]![Search]![REJ]))
    
          AND (T.strProject=[Forms]![Search]![strProject] OR IsNull([Forms]![Search]![strProject]))
    now i get

    Missing ),], or Item in query expression..

    any ideas?

  8. #8
    Join Date
    Apr 2003
    Posts
    280
    What are you using to let the user fill in the date field(textbox, listbox, combobox)?

  9. #9
    Join Date
    Apr 2003
    Posts
    280
    try this, it might work..

    Data:iif(IsNull([DateData]), [DateData], [DateData] = Forms!FormName!ObjectName)

Posting Permissions

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