Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    16

    Unanswered: Help with sql query

    Hi

    I have a query which should do the following..

    Bring up all records from customer table that

    datelastvisited field is not during the last 3 months
    or is null
    AND has a region matching a user input value

    OR

    in a customercalls table with a one to many relationship on customerid

    if nextcalldate is within the lst 3 months
    or is null

    i thought i had it working but its not..

    below is the query that i thought was working before I tried adding the null criteria..


    SELECT customers.*, customers.CustomerLastVisitDate, CustomerCalls.CustomerCallDateNext FROM customers INNER JOIN CustomerCalls ON customers.CustomerID = CustomerCalls.CustomerID WHERE ((([customers.customerregion])='" & Me.cboRegion & "') AND ([customers.CustomerLastVisitDate] Not Between Date() And DateAdd('m',-3,Date()))) AND ((CustomerCalls.CustomerCallDateNext) Between Date() And DateAdd('m',-3,Date())) OR (([customercalls.customercalldatenext])=Date());

    can anyone help me?

    I seem to have lsot the plot..

    thanks
    mat

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select customers.*
      from customers 
     where customerregion = '" & Me.cboRegion & "'
       and (
           CustomerLastVisitDate is null
        or CustomerLastVisitDate 
              Not Between Date() 
                      and DateAdd('m',-3,Date())
           )
    union
    select customers.*
      from customers 
    inner 
      join CustomerCalls 
        on customers.CustomerID 
         = CustomerCalls.CustomerID 
     where CustomerCalls.CustomerCallDateNext is null
        or CustomerCalls.CustomerCallDateNext 
                  Between Date() 
                      and DateAdd('m',-3,Date())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    16

    Ta

    Thanks very much..
    Ur a star!

    mat

Posting Permissions

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