Problem with Critieria

[pcdaveh]

I have the following criteria in a query.

>=[Forms]![frm_TransActions]![txtDateBegin] And <=[Forms]![frm_TransActions]![txtDateEnd]

Sometimes it works and sometimes it doesn't. Sometimes the query ignores the criteria and returns all records. Sometimes it will accept the criteria input. The query is very complex: All the SQL is listed below. But before you even look at all that... What I'm wondering is how to put my criteria in code / module and have the sql statement call it. I've had a similiar problem to this in the past and the way I over came the problem was by creating a user defined function and called it. It wasn't in the criteria though...

SELECT tbl_InvestorTransactions.Account, tbl_Partnerships.PartIDN, tbl_InvestorTransactions.TransActionDescription, tbl_InvestorTransactions.TransActionTypeCode, tbl_InvestorTransactions.TransactionDate, tbl_InvestorTransactions.TransactionDate AS sDate, CDate(Getquarterend([sDate])) AS QtrEnd, tbl_InvestorTransactions.TransactionAmountDB, tbl_InvestorTransactions.TransactionAmountCR, tbl_InvestorTransactions.InvIDN, tbl_InvestorTransactions.DateTransActionEntered, tbl_InvestorTransactions.PartIDN, tbl_Investors.InvNameT, tbl_Investors.InvAddress1T, tbl_Investors.InvAddress2T, tbl_Investors.InvCityT, tbl_Investors.InvStateT, tbl_Investors.InvZipT, tbl_Partnerships.PartNameT, tbl_Partnerships.PartAddress1T, tbl_Partnerships.PartAddress2T, tbl_Partnerships.PartCityT, tbl_Partnerships.PartStateT, tbl_Partnerships.PartZipT, IIf([tbl_InvestorTransactions].[TransActionTypeCode]=1,IIf([TransactionAmountDB]=0,[TransactionAmountCR],[TransactionAmountDB]),0) AS [Capital In], IIf([tbl_InvestorTransactions].[TransActionTypeCode]=5,IIf([TransactionAmountDB]=0,[TransactionAmountCR],[TransactionAmountDB]),0) AS [Capital Returned], IIf([tbl_InvestorTransactions].[TransActionTypeCode]=3,IIf([TransactionAmountDB]=0,[TransactionAmountCR],[TransactionAmountDB]),0) AS [Profits Recognized], IIf([tbl_InvestorTransactions].[TransActionTypeCode]=2,IIf([TransactionAmountDB]=0,[TransactionAmountCR],[TransactionAmountDB]),0) AS [Profits Paid Out], IIf([tbl_InvestorTransactions].[TransActionTypeCode]=4,IIf([TransactionAmountDB]=0,[TransactionAmountCr],[TransactionAmountDB]),0) AS [Preferred Return], tbl_Investors.InvDomestic, IIf([TransactionAmountDB]=0,[TransactionamountCR],[TransActionAmountDB]) AS TransActionAmount, tbl_Investors.InvName1T, tbl_InvestorTransactions.PrintRecord, ([Capital In]+[Capital Returned]+[Profits Recognized]+[Profits Paid Out]+[preferred return]) AS AccountBalance, tbl_InvestorTransactions.ID
FROM tbl_Partnerships INNER JOIN (tbl_Investors INNER JOIN (tbl_InvestorTransactions INNER JOIN tbl_Transactiontypecodes ON tbl_InvestorTransactions.TransActionTypeCode = tbl_Transactiontypecodes.TransActionTypeCode) ON tbl_Investors.InvIDN = tbl_InvestorTransactions.InvIDN) ON tbl_Partnerships.PartIDN = tbl_InvestorTransactions.PartIDN
WHERE (((tbl_Partnerships.PartIDN) Like [Forms]![frm_TransActions]![txtPartnershipNumber]) AND ((tbl_InvestorTransactions.TransactionDate)>=[Forms]![frm_TransActions]![txtDateBegin] And (tbl_InvestorTransactions.TransactionDate)<=[Forms]![frm_TransActions]![txtDateEnd])) OR ((([Forms]![frm_TransActions]![txtPartnershipNumber]) Is Null))
ORDER BY tbl_InvestorTransactions.Account, tbl_InvestorTransactions.TransActionDescription, tbl_InvestorTransactions.TransactionDate;