TheInsider
Programmer
Hi,<br> I have stumbled accross a bug in Access 2000. Access 2000 allows you to use derived or nested tables, however I have found an interesting phenomenon: If you enter the query below into the SQL view, Access 2000 will accept this as it is and will allow you to save the query, view it in design view, and will even run the query perfectly. However, upon closing the query and then re-entering it, Access changes some of the syntax. For example it changes "LEFT JOIN(SELECT ) AS" to "LEFT JOIN [SELECT ]. AS" . The problem is that this syntax causes a sytax error!!! If you take the "[]." out and replace them with "()" again, you can save the query, view it in design view, and run it. Once again, if you close the query, the syntax is changed back to the incorrect syntax. If you open/run the query with the invalid syntax, it will run fine, but if you choose to open it in design view, it will go to SQL view and won't let you save it, view it in design view, or run it. Crystal Reports won't touch this query with a ten foot pole while it is in this state. Both the unchanged and changed queries are listed below, but you can see this phenomenon with a much simpler query. If someone knows what the heck Access is doing, please let me know!<br><br>------------------------------------------------------------<br>Unchanged Query:<br>------------------------------------------------------------<br>PARAMETERS LastName Text ( 255 ), ContractNumber Text ( 255 ), FundName Text ( 255 ), ContractStartDate DateTime, ContractEndDate DateTime, TransactionStartDate DateTime, TransactionEndDate DateTime;<br>SELECT [fldLastName] & ", " & [fldFirstName] & " " & [fldMiddleInitial] AS fldContractOwner, tbdClientContracts.fldContractNumber, qryContractStartDate.fldContractStartDate, tbdFunds.fldFundName, tbdClientFunds.fldInitialCommission, qryInitialCommission.fldCommissionEarned, tbdClientFunds.fldTrailerCommission, qryTrailerCommission.fldCommissionEarned<br>FROM tbdClients RIGHT JOIN (((tbdClientContracts LEFT JOIN (SELECT tbdClientContracts.fldContractNumber, Min(tbdDeposits.fldDateOfDeposit) AS fldContractStartDate<br>FROM tbdClientContracts LEFT JOIN tbdDeposits ON tbdClientContracts.fldContractNumber = tbdDeposits.fldContractNumber<br>GROUP BY tbdClientContracts.fldContractNumber) AS qryContractStartDate ON tbdClientContracts.fldContractNumber = qryContractStartDate.fldContractNumber) LEFT JOIN ((tbdClientFunds LEFT JOIN (SELECT tbdClientFunds.fldContractNumber, tbdClientFunds.fldFundID, ([SumOffldDepositValue]-IIf(IsNull([SumOffldWithdrawValue]),0,[SumOffldWithdrawValue]))*[fldInitialCommission] AS fldCommissionEarned<br>FROM (tbdClientFunds LEFT JOIN (SELECT tbdDeposits.fldContractNumber, tbdDeposits.fldFundID, Sum(tbdDeposits.fldDepositValue) AS SumOffldDepositValue<br>FROM tbdDeposits<br>WHERE (((DateDiff("d",[fldDateOfDeposit],Date()))<365) AND ((tbdDeposits.fldDateOfDeposit)>=[TransactionStartDate] And (tbdDeposits.fldDateOfDeposit)<=[TransactionEndDate]))<br>GROUP BY tbdDeposits.fldContractNumber, tbdDeposits.fldFundID) AS qryInitialDeposits ON (tbdClientFunds.fldFundID = qryInitialDeposits.fldFundID) AND (tbdClientFunds.fldContractNumber = qryInitialDeposits.fldContractNumber)) LEFT JOIN (SELECT tbdWithdraws.fldContractNumber, tbdWithdraws.fldFundID, Sum(tbdWithdraws.fldWithdrawValue) AS SumOffldWithdrawValue<br>FROM tbdWithdraws<br>WHERE (((DateDiff("d",[fldDateOfWithdraw],Date()))<365) AND ((tbdWithdraws.fldDateOfWithdraw)>=[TransactionStartDate] And (tbdWithdraws.fldDateOfWithdraw)<=[TransactionEndDate]))<br>GROUP BY tbdWithdraws.fldContractNumber, tbdWithdraws.fldFundID) AS qryInitialWithdraws ON (tbdClientFunds.fldFundID = qryInitialWithdraws.fldFundID) AND (tbdClientFunds.fldContractNumber = qryInitialWithdraws.fldContractNumber)) AS qryInitialCommission ON (tbdClientFunds.fldContractNumber = qryInitialCommission.fldContractNumber) AND (tbdClientFunds.fldFundID = qryInitialCommission.fldFundID)) LEFT JOIN (SELECT tbdClientFunds.fldContractNumber, tbdClientFunds.fldFundID, ([SumOffldDepositValue]-IIf(IsNull([SumOffldWithdrawValue]),0,[SumOffldWithdrawValue]))*[fldTrailerCommission] AS fldCommissionEarned<br>FROM (tbdClientFunds LEFT JOIN (SELECT tbdDeposits.fldContractNumber, tbdDeposits.fldFundID, Sum(tbdDeposits.fldDepositValue) AS SumOffldDepositValue<br>FROM tbdDeposits<br>WHERE (((DateDiff("d",[fldDateOfDeposit],Date()))>=365) AND ((tbdDeposits.fldDateOfDeposit)>=[TransactionStartDate] And (tbdDeposits.fldDateOfDeposit)<=[TransactionEndDate]))<br>GROUP BY tbdDeposits.fldContractNumber, tbdDeposits.fldFundID) AS qryTrailerDeposits ON tbdClientFunds.fldContractNumber = qryTrailerDeposits.fldContractNumber) LEFT JOIN (SELECT tbdWithdraws.fldContractNumber, tbdWithdraws.fldFundID, Sum(tbdWithdraws.fldWithdrawValue) AS SumOffldWithdrawValue<br>FROM tbdWithdraws<br>WHERE (((DateDiff("d",[fldDateOfWithdraw],Date()))>=365) AND ((tbdWithdraws.fldDateOfWithdraw)>=[TransactionStartDate] And (tbdWithdraws.fldDateOfWithdraw)<=[TransactionEndDate]))<br>GROUP BY tbdWithdraws.fldContractNumber, tbdWithdraws.fldFundID) AS qryTrailerWithdraws ON qryTrailerDeposits.fldContractNumber = qryTrailerWithdraws.fldContractNumber) AS qryTrailerCommission ON (tbdClientFunds.fldContractNumber = qryTrailerCommission.fldContractNumber) AND (tbdClientFunds.fldFundID = qryTrailerCommission.fldFundID)) ON tbdClientContracts.fldContractNumber = tbdClientFunds.fldContractNumber) LEFT JOIN tbdFunds ON tbdClientFunds.fldFundID = tbdFunds.fldFundID) ON tbdClients.fldClientID = tbdClientContracts.fldClientID<br>WHERE (((tbdClientContracts.fldContractNumber) Like [ContractNumber]) AND ((qryContractStartDate.fldContractStartDate)>=[ContractStartDate] And (qryContractStartDate.fldContractStartDate)<=[ContractEndDate]) AND ((tbdFunds.fldFundName) Like [FundName]) AND ((qryInitialCommission.fldCommissionEarned) Is Not Null) AND ((tbdClients.fldLastName) Like [LastName])) OR (((qryTrailerCommission.fldCommissionEarned) Is Not Null));<br>------------------------------------------------------------<br>Changed Query:<br>------------------------------------------------------------<br>PARAMETERS LastName Text ( 255 ), ContractNumber Text ( 255 ), FundName Text ( 255 ), ContractStartDate DateTime, ContractEndDate DateTime, TransactionStartDate DateTime, TransactionEndDate DateTime;<br>SELECT [fldLastName] & ", " & [fldFirstName] & " " & [fldMiddleInitial] AS fldContractOwner, tbdClientContracts.fldContractNumber, qryContractStartDate.fldContractStartDate, tbdFunds.fldFundName, tbdClientFunds.fldInitialCommission, qryInitialCommission.fldCommissionEarned, tbdClientFunds.fldTrailerCommission, qryTrailerCommission.fldCommissionEarned<br>FROM tbdClients RIGHT JOIN (((tbdClientContracts LEFT JOIN [SELECT tbdClientContracts.fldContractNumber, Min(tbdDeposits.fldDateOfDeposit) AS fldContractStartDate<br>FROM tbdClientContracts LEFT JOIN tbdDeposits ON tbdClientContracts.fldContractNumber = tbdDeposits.fldContractNumber<br>GROUP BY tbdClientContracts.fldContractNumber]. AS qryContractStartDate ON tbdClientContracts.fldContractNumber = qryContractStartDate.fldContractNumber) LEFT JOIN ((tbdClientFunds LEFT JOIN [SELECT tbdClientFunds.fldContractNumber, tbdClientFunds.fldFundID, ([SumOffldDepositValue]-IIf(IsNull([SumOffldWithdrawValue]),0,[SumOffldWithdrawValue]))*[fldInitialCommission] AS fldCommissionEarned<br>FROM (tbdClientFunds LEFT JOIN (SELECT tbdDeposits.fldContractNumber, tbdDeposits.fldFundID, Sum(tbdDeposits.fldDepositValue) AS SumOffldDepositValue<br>FROM tbdDeposits<br>WHERE (((DateDiff("d",[fldDateOfDeposit],Date()))<365) AND ((tbdDeposits.fldDateOfDeposit)>=[TransactionStartDate] And (tbdDeposits.fldDateOfDeposit)<=[TransactionEndDate]))<br>GROUP BY tbdDeposits.fldContractNumber, tbdDeposits.fldFundID) AS qryInitialDeposits ON (tbdClientFunds.fldFundID = qryInitialDeposits.fldFundID) AND (tbdClientFunds.fldContractNumber = qryInitialDeposits.fldContractNumber)) LEFT JOIN (SELECT tbdWithdraws.fldContractNumber, tbdWithdraws.fldFundID, Sum(tbdWithdraws.fldWithdrawValue) AS SumOffldWithdrawValue<br>FROM tbdWithdraws<br>WHERE (((DateDiff("d",[fldDateOfWithdraw],Date()))<365) AND ((tbdWithdraws.fldDateOfWithdraw)>=[TransactionStartDate] And (tbdWithdraws.fldDateOfWithdraw)<=[TransactionEndDate]))<br>GROUP BY tbdWithdraws.fldContractNumber, tbdWithdraws.fldFundID) AS qryInitialWithdraws ON (tbdClientFunds.fldFundID = qryInitialWithdraws.fldFundID) AND (tbdClientFunds.fldContractNumber = qryInitialWithdraws.fldContractNumber)]. AS qryInitialCommission ON (tbdClientFunds.fldFundID = qryInitialCommission.fldFundID) AND (tbdClientFunds.fldContractNumber = qryInitialCommission.fldContractNumber)) LEFT JOIN [SELECT tbdClientFunds.fldContractNumber, tbdClientFunds.fldFundID, ([SumOffldDepositValue]-IIf(IsNull([SumOffldWithdrawValue]),0,[SumOffldWithdrawValue]))*[fldTrailerCommission] AS fldCommissionEarned<br>FROM (tbdClientFunds LEFT JOIN (SELECT tbdDeposits.fldContractNumber, tbdDeposits.fldFundID, Sum(tbdDeposits.fldDepositValue) AS SumOffldDepositValue<br>FROM tbdDeposits<br>WHERE (((DateDiff("d",[fldDateOfDeposit],Date()))>=365) AND ((tbdDeposits.fldDateOfDeposit)>=[TransactionStartDate] And (tbdDeposits.fldDateOfDeposit)<=[TransactionEndDate]))<br>GROUP BY tbdDeposits.fldContractNumber, tbdDeposits.fldFundID) AS qryTrailerDeposits ON tbdClientFunds.fldContractNumber = qryTrailerDeposits.fldContractNumber) LEFT JOIN (SELECT tbdWithdraws.fldContractNumber, tbdWithdraws.fldFundID, Sum(tbdWithdraws.fldWithdrawValue) AS SumOffldWithdrawValue<br>FROM tbdWithdraws<br>WHERE (((DateDiff("d",[fldDateOfWithdraw],Date()))>=365) AND ((tbdWithdraws.fldDateOfWithdraw)>=[TransactionStartDate] And (tbdWithdraws.fldDateOfWithdraw)<=[TransactionEndDate]))<br>GROUP BY tbdWithdraws.fldContractNumber, tbdWithdraws.fldFundID) AS qryTrailerWithdraws ON qryTrailerDeposits.fldContractNumber = qryTrailerWithdraws.fldContractNumber]. AS qryTrailerCommission ON (tbdClientFunds.fldFundID = qryTrailerCommission.fldFundID) AND (tbdClientFunds.fldContractNumber = qryTrailerCommission.fldContractNumber)) ON tbdClientContracts.fldContractNumber = tbdClientFunds.fldContractNumber) LEFT JOIN tbdFunds ON tbdClientFunds.fldFundID = tbdFunds.fldFundID) ON tbdClients.fldClientID = tbdClientContracts.fldClientID<br>WHERE (((tbdClientContracts.fldContractNumber) Like [ContractNumber]) AND ((qryContractStartDate.fldContractStartDate)>=[ContractStartDate] And (qryContractStartDate.fldContractStartDate)<=[ContractEndDate]) AND ((tbdFunds.fldFundName) Like [FundName]) AND ((qryInitialCommission.fldCommissionEarned) Is Not Null) AND ((tbdClients.fldLastName) Like [LastName])) OR (((qryTrailerCommission.fldCommissionEarned) Is Not Null));<br>
