help Query Where date between and agent code

[starclassic]

I have this code right now but is not working properly.
If I query for the date between and particular agent code, it display all of date between and the agentcode which i did not want to display.
It works fine if I want to search for agent code alone and blank the date and vice versa. If all field are blank would display everything which is okay. But query just the agent code for a particular date not quite right. Thanks for the help.

WHERE (((tblCreditDetails.dteDateTransaction) Between Forms!frmCreditCardIncompleteList!SDate And Forms!frmCreditCardIncompleteList!EDate) Or (((Forms!frmCreditCardIncompleteList!SDate) Is Null) And ((Forms!frmCreditCardIncompleteList!EDate) Is Null)) And ((tblCreditDetails.strAgentCode)=Forms!frmCreditCardIncompleteList!txtAgentCode1 Or Forms!frmCreditCardIncompleteList!txtAgentCode1 Is Null));

 

[PHV]

double check the parentheses when mixing AND and OR operators:

WHERE ((tblCreditDetails.dteDateTransaction Between Forms!frmCreditCardIncompleteList!SDate And Forms!frmCreditCardIncompleteList!EDate)
    OR (Forms!frmCreditCardIncompleteList!SDate Is Null AND Forms!frmCreditCardIncompleteList!EDate Is Null))
  AND (tblCreditDetails.strAgentCode=Forms!frmCreditCardIncompleteList!txtAgentCode1
    OR Forms!frmCreditCardIncompleteList!txtAgentCode1 Is Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886