Query returns inconsistent results (Office 2000 Sr1a)

[davidhit]

Another strange thing in Access.
I have developed (an admittedly longish) query in Access and used the SQL in a Function in VBA.
But the function fails to return the correct results because the main SQL query in the function never returns any records. But if I do a DEBUG.PRINT and copy the SQL generated to create a query in the Query window a strange thing happens. At first no records are returned. But if I switch to the Query design grid
then delete and replace (indentically) any parameter
(eg >0 - delete the '0' and relace it with '0' again) then run the query results are returned.
In checking what changes are made by this action to the SQL in SQL design view I can see no alterations except seeminly unimportant repositioning of the SQL code. As far as I can see it is the same SQL statement.
Any ideas what to do here?
If Access is so fussy about the positioning of SQL code
then how can I make the VBA function speak to Access iin the dialect it prefers?

[WendellB]

I would need to see your SQL statement to make any useful suggestions, but I can offer some general guidance. One possibility is to actually save your query as a compiled query, and then use code to set the parameter. That assumes of course that you need to have a query where the criteria is dynamic. The advantage of that approach is that the query is always optimized and the SQL string is fixed.

Another approach is similar to the lines you are following, but you take the SQL string and display it in the query grid and get it to work. Then you switch back to the SQL view, copy the string, and paste it into your VBA. That way you should get the correct result. If you continue to have problems post the code and we'll see if we can sort it out.

[davidhit]

Hmmm. Thanks. Good suggestions.
The saved query/parameter approach might be the way to go but I have avoided this as it means that if your saved query is accidently deleted then the function won't run. So I try to make the code self-contained. I'm not sure how you use code to set a paramter either.
Here is the SQL string, by the way.
I tried your second suggestion but the lines are long and you can't just paste it back without using line continuation characters. Then it still fails even though the code is based on a working query.
Sorry about the length...
"SELECT CALC.Date AS Expr1, Sum(CALC.AUD) AS SumOfAUD" & _
" FROM (SELECT Transactions.Date, PersonsorEntities.PersonorEntityID, PersonsorEntities_3.PersonorEntityID," & _
" NZ([Transactions]![Amount AUD]*[AllocationofOwnership.Allocation]*[AllocationofOwnership_1.Allocation]*IIf([PersonorEntityInstances]![CommencementDate]<=[Transactions]![Date]" & _
" And PersonorEntityInstances_1!CommencementDate<=[Transactions]![Date] And PersonorEntityInstances_2!CommencementDate<=[Transactions]![Date]" & _
" And PersonorEntityInstances_3!CommencementDate<=[Transactions]![Date] And [PersonorEntityInstances]![CessationDate]>=[Transactions]![Date]" & _
" And PersonorEntityInstances_1!CessationDate>=[Transactions]![Date] And PersonorEntityInstances_2!CessationDate>=[Transactions]![Date]" & _
" And PersonorEntityInstances_3!CessationDate>=[Transactions]![Date],1,0),0) AS AUD" & _
" FROM (PersonsorEntities INNER JOIN PersonorEntityInstances ON PersonsorEntities.PersonorEntityID = PersonorEntityInstances.PersonorEntityID)" & _
" INNER JOIN ((((AllocationofOwnership INNER JOIN PersonorEntityInstances AS PersonorEntityInstances_1 ON AllocationofOwnership.OwnedID =" & _
" PersonorEntityInstances_1.PersonorEntityInstanceID ) INNER JOIN PersonsorEntities AS PersonsorEntities_1 ON PersonorEntityInstances_1.PersonorEntityID =" & _
" PersonsorEntities_1.PersonorEntityID) INNER JOIN Accounts ON PersonsorEntities_1.PersonorEntityID = Accounts.AccountOwnerID)" & _
" INNER JOIN ((((((Transactions INNER JOIN Accounts AS Accounts_1 ON Transactions.TargetAccountID = Accounts_1.AccountID) INNER JOIN PersonsorEntities AS PersonsorEntities_2" & _
" ON Accounts_1.AccountOwnerID = PersonsorEntities_2.PersonorEntityID) INNER JOIN PersonorEntityInstances AS PersonorEntityInstances_2 ON PersonsorEntities_2.PersonorEntityID =" & _
" PersonorEntityInstances_2.PersonorEntityID) INNER JOIN AllocationofOwnership AS AllocationofOwnership_1 ON PersonorEntityInstances_2.PersonorEntityInstanceID = AllocationofOwnership_1.OwnedID)" & _
" INNER JOIN PersonorEntityInstances AS PersonorEntityInstances_3 ON AllocationofOwnership_1.OwnerID = PersonorEntityInstances_3.PersonorEntityInstanceID ) INNER JOIN PersonsorEntities AS PersonsorEntities_3" & _
" ON PersonorEntityInstances_3.PersonorEntityID = PersonsorEntities_3.PersonorEntityID) ON Accounts.AccountID = Transactions.SourceAccountID) ON PersonorEntityInstances.PersonorEntityInstanceID = AllocationofOwnership.OwnerID" & _
" WHERE (((Transactions.Date)= " & dte & ") AND ((PersonsorEntities.PersonorEntityID)=" & PF & ") AND ((PersonsorEntities_3.PersonorEntityID)=" & PT & ")" & _
" AND ((NZ([Transactions]![Amount AUD]*[AllocationofOwnership.Allocation]*[AllocationofOwnership_1.Allocation]*IIf([PersonorEntityInstances]![CommencementDate]<=[Transactions]![Date]" & _
" And [PersonorEntityInstances_1]![CommencementDate]<=[Transactions]![Date] And [PersonorEntityInstances_2]![CommencementDate]<=[Transactions]![Date] And [PersonorEntityInstances_3]![CommencementDate]<=[Transactions]![Date]" & _
" And [PersonorEntityInstances]![CessationDate]>=[Transactions]![Date] And [PersonorEntityInstances_1]![CessationDate]>=[Transactions]![Date] And [PersonorEntityInstances_2]![CessationDate]>=[Transactions]![Date]" & _
" And [PersonorEntityInstances_3]![CessationDate]>=[Transactions]![Date],1,0),0))>0) AND ((PersonsorEntities_3.GeneralTypesID)=1) AND ((PersonsorEntities.GeneralTypesID)=1)" & _
" AND ((Accounts.AccountOwnerID)<>[Accounts_1.AccountOwnerID]))) AS CALC GROUP BY CALC.Date"

Whew!!

[WendellB]

Whew! is right! <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>
I'm surprised you can get this to behave in a query grid. Generally queries using subqueries either go over the wall, or you have a complex expression that nobody can understand as one of the fields or criteria. I usually avoid these kind of things because they are a bear to debug to start with, and if you ever make a design change in one of the underlying tables, it takes hours to figure out how to change it. I would probably change this to be a query on a query, and use the .Parameters of the QueryDef object to set the parameter values in code.

Never the less, your approach should be workable. Since I don't have the table structure, I will need to recreate it, so it will take a while to test it. Just out of curiosity, are you only running this in code to avoid having a stored query, or is it actually processing a recordset based on the results of the query?

[davidhit]

Oddly enough I first created this monster with the query grid on a query that
gives me my result in half a second from a table with 13,000 odd entries.
The idea is that given a date and two entities the total net transfer of funds
from entity one to entity two is calculated for that date (for each date there may be
from zero to several records that are relevant).
The VBA function then iterates through the underlying table and calculates a
running balance versus time ready to be exported to Excel to produce a graph.
So yes, I am simply avoiding the stored query approach

[WendellB]

Another crisis has descended on me, so it will likely be tomorrow before I try rebuilding your query, but I wanted to put in my two cents on stored queries versus SQL strings in VBA. In the testing we've done, stored queries nearly always run faster than SQL strings just because the strings have to be compiled and optimized before execution. On a large table returning many records, you don't see much difference. On the other hand a SQL string returning a selected record to a form, you may see a substantial difference, especially if the query is complex and the tables involved are large. As to the fragileness of stored queries, corruption of them is no more likely than corruption of your VBA modules. Both are stored in a structure similar to memo fields in a table. I don't actually ever recall seeing a query go bad - databases go bad, frequently on one of the forms, but queries don't seem to cause much trouble. They are of course visible to the user if you expose the container window, but you can control design access with security.

[davidhit]

Thanks, a very helpful answer.
Hope your crisis is solved!