Help with sql query

funkkube · #1 (**permalink**) 10-14-04, 11:42

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

r937 · #2 (**permalink**) 10-14-04, 19:15

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())

funkkube · #3 (**permalink**) 10-14-04, 19:57

Thanks very much..
Ur a star!

mat

Internet Services

Web Development

Digital Marketing

Consumer Tech