Query Criteria being automatically changed when saved and reopened (1 Viewer)

[InstructionWhich7142]

I try to set the criteria of this field:

CurrentShipDate: (IIf([sndplan].[shipdate]>1,IIf([sndplan].[shipdate]>[sndplan].[cldate] Or [sndplan].[shipearly],[sndplan].[shipdate],[sndplan].[cldate]),Null))

To be this statement

<>IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),Null)

This has the expected result when I enter it in the SQL view and Run the query, however when I save, close and reopen the satement changes to this which doesn't work:

<>(IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or ((IIf([sndplan].[shipdate]>1,IIf([sndplan].[shipdate]>[sndplan].[cldate] Or [sndplan].[shipearly],[sndplan].[shipdate],[sndplan].[cldate]),Null)))=[sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),Null))

It returns results that shouldn't be there and aren't in the version I enter in SQL view

 

[dynamictiger]

When I get this type of outcome I break the criteria down and make separate columns for each. I resolve each column and then combine one at a time till I find the issue.

 

[Pat Hartman]

I'm not sure why you are doing this with nested IIF()'s so I'll rewrite it for you.

Where [sndplanvactual].[shipdate]>[sndplanvactual].[cldate]
OR ([sndplanvactual].[shipearly] = True

I can't even begin to figure out what your actual IIf() is doing as the where clause since it is returning a date in two cases and null in the third. I think that ends up with the non-null values being evaluated as "true" and the null value being evaluated as "false". When you use an IIf() in the where clause, be certain that it ONLY returns True or False to make the criteria clear.

 

[InstructionWhich7142]

dynamictiger, thanks but the logic works, it's how Access is interpreting it into SQL and back

Pat Thanks for the breakdown but the logic is not as simple as the Where you've written, also the logic works with the IIF when I set the SQL and press "RUN" but when I save access screws the logic up for some reason, I've just tested with a 0 in place of the NULL and I get the same behaviour, ACCESS seems to incorrectly interpret the SQL when it creates the Design View?

The "logic" is comparing dates between sndplan and sndplanVactual,

the logic on both is the same, hence the field and the criteria have the same IIF, logic as follows:

If the shipdate field is not > 1 then always fail (hence null)

Otherwise, if the shipdate is greater then cldate, or if shipearly is true then compare the shipdate otherwise use the cldate

edit: anyway the logic is correct, the issue is Access is screwing up saving the statement, I'm trying to understand this bug

 

[isladogs]

Access does alter query syntax when it believes that it can do it better for query optimisation.

If that process changes the query criteria then I'm sorry to say that you will need to modify your syntax to prevent that happening. It may be that additional bracketing would help here - if not then you may have to rewrite it completely.

You say it works but have you thoroughly tested with all possible combinations?
What about if you have null values?

However, I'm not going to attempt that for you as I'm also unclear what the overall outcomes are meant to be. If its difficult for outsiders to read/edit then its unsurprising that Access will modify it!

 

[InstructionWhich7142]

We've had one occurrence before where a totals query GroupBy on a field caleld DueDate gets changed to:

DueDate as an "Expression" total

With a groupby of the IIF with "Show" unticked along to the right

The icing on the cake? it then errors when you try to save saying "Syntax Error"

So anyway, it's a bug, great, is there a way to fix it or disable the optimisation process?

The logic is correct, it's just a nested IIF that we use to output the correct date as a field, I've just copy-pasted it into the where statement to compare old data to current data,

The data I was testing had a NULL and that works perfectly split into two queries with the names of the two outputted dates compared, as one query with the "Optimised" output I get a value where both dates are the same which violates the <> bit

Out of interest i'm currently trying to refactor it without IIFs, it's just unnecessary work I wanted to avoid

 

[isladogs]

Microsoft would say its a feature, not a bug!
The only way to avoid it is to run it using SQL instead of running a saved query.

CurrentDB.Execute "paste your query sql here", dbFailOnError

You are unlikely to notice any difference in the time needed

This is my preferred method of working. I only save queries where they are used as part of a more complex sql statement

If you can get rid of one or more IIf statements, it should run faster

 

[InstructionWhich7142]

I'm looking at converting the IIFs into multiple query criteria but it's fairly tricky,

the whole statement, logically is:

(IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),Null)) <> (IIf([sndplan].[shipdate]>1,IIf([sndplan].[shipdate]>[sndplan].[cldate] Or [sndplan].[shipearly],[sndplan].[shipdate],[sndplan].[cldate]),Null))

Do you have a process you'd use to expand this out?

 

[isladogs]

Hi Chris
I try to avoid multiple or nested IIfs as they are very hard to read and its so easy to make errors.

With complex SQL I tend to break it down into sections such as
strSQL =strSelect & strFrom & strWhere & strOrderBy
Each section can then be worked on separately.
In your case, I might further break down the select part to manage each case where you currently use IIfs
Then write Select Case statements to handle each case separately so the IIFs can be eliminated completely

Select Case shipdate

Case Is >1
StrSelect= …
Case Else
StrSelect=…

End Select

If necessary you can place a second select case statement inside that one to handle 'sub cases' if that makes sense.

Also in case its any help, I have a utility that helps convert query sql to vba or vice versa. You can find it at https://www.access-programmers.co.uk/forums/showthread.php?t=293372

Hope the above helps you make progress

 

[Pat Hartman]

the logic is not as simple as the Where you've written

What condition did I miss?

Please modify the IIf() so that it returns true or false. Having an IIf() in a where clause that returns anything other than true or false is confusing at best.

 

[InstructionWhich7142]

Through some trial and error, I ended up with some repetition in where where clause where I had a less and more restrictive version of the same statement so reduced it down to the bare minimum and got:

SELECT IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),0) AS OldShipDate, (IIf([sndplan].[shipdate]>1,IIf([sndplan].[shipdate]>[sndplan].[cldate] Or [sndplan].[shipearly],[sndplan].[shipdate],[sndplan].[cldate]),0)) AS CurrentShipDate, sndplanvactual.ijn, sndplanvactual.requirement, sndplanvactual.snapshot, sndplan.ShipDate, sndplanvactual.ShipDate, sndplan.ShipEarly, sndplanvactual.ShipEarly, sndplanvactual.cldate, sndplan.cldate
FROM sndplanvactual LEFT JOIN sndplan ON (sndplanvactual.level = sndplan.level) AND (sndplanvactual.pstk = sndplan.pstk) AND (sndplanvactual.ijn = sndplan.ijn) AND (sndplanvactual.cldate = sndplan.cldate)
WHERE (((IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),0)) Between [forms]![sndplanvactual]![startdatetxt] And [Forms]![sndplanvactual]![enddatetxt]) AND ((sndplanvactual.requirement)<>0) AND ((sndplanvactual.snapshot)=[forms]![sndplanvactual]![snapshotdatecbo]) AND ((sndplan.ShipDate) Is Null) AND ((sndplan.cldate)>1)) OR (((IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),0)) Between [forms]![sndplanvactual]![startdatetxt] And [Forms]![sndplanvactual]![enddatetxt]) AND ((sndplanvactual.requirement)<>0) AND ((sndplanvactual.snapshot)=[forms]![sndplanvactual]![snapshotdatecbo]) AND ((sndplan.ShipDate)<>[sndplanvactual].[shipdate] And (sndplan.ShipDate)>[sndplan].[cldate]) AND ((sndplan.cldate)>1)) OR (((IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),0)) Between [forms]![sndplanvactual]![startdatetxt] And [Forms]![sndplanvactual]![enddatetxt]) AND ((sndplanvactual.requirement)<>0) AND ((sndplanvactual.snapshot)=[forms]![sndplanvactual]![snapshotdatecbo]) AND ((sndplan.ShipDate)<>[sndplanvactual].[shipdate]) AND ((sndplanvactual.ShipDate)>[sndplanvactual].[cldate]) AND ((sndplan.cldate)>1)) OR (((IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),0)) Between [forms]![sndplanvactual]![startdatetxt] And [Forms]![sndplanvactual]![enddatetxt]) AND ((sndplanvactual.requirement)<>0) AND ((sndplanvactual.snapshot)=[forms]![sndplanvactual]![snapshotdatecbo]) AND ((sndplan.ShipDate)<[sndplan].[cldate]) AND ((sndplanvactual.ShipDate)<[sndplanvactual].[cldate]) AND ((sndplanvactual.ShipEarly)=True) AND ((sndplan.cldate)>1)) OR (((IIf([sndplanvactual].[shipdate]>1,IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly],[sndplanvactual].[shipdate],[sndplanvactual].[cldate]),0)) Between [forms]![sndplanvactual]![startdatetxt] And [Forms]![sndplanvactual]![enddatetxt]) AND ((sndplanvactual.requirement)<>0) AND ((sndplanvactual.snapshot)=[forms]![sndplanvactual]![snapshotdatecbo]) AND ((sndplan.ShipDate)<[sndplan].[cldate]) AND ((sndplanvactual.ShipDate)<[sndplanvactual].[cldate]) AND ((sndplan.ShipEarly)=True) AND ((sndplan.cldate)>1));

I'm still not entirely sure why that worked so I'm going to keep looking into it, as you say, the nested IIF is hard to "understand" in as far as it's hard to expand out into separate statements but I'd like to better understand this process,

"logically" in my head it reads how I explain the different dates and boolean field to people:

if the shipdate is greater than the cldate, or if shipearly is ticked, use shipdate, otherwise use cldate,

I'm then just comparing the result now to the result at a previous snapshot in time so using the same IIF made sense to me,

I've realised the bit with NULL is actually unnecessary in this instance (it's relevant elsewhere I use the same IIF) because the join excludes results that are NULL on both sides

 

[isladogs]

if the shipdate is greater than the cldate, or if shipearly is ticked, use shipdate, otherwise use cldate

If that's the case, then Pat understood it perfectly.
So I believe the following should be all you need:

IIf([sndplanvactual].[shipdate]>[sndplanvactual].[cldate] Or [sndplanvactual].[shipearly]=True,[sndplanvactual].[shipdate],[sndplanvactual].[cldate])

 

[InstructionWhich7142]

I'm then just comparing the result now to the result at a previous snapshot in time so using the same IIF made sense to me,

Sorry that's the subtlety i'm talking about, it's not as simple as all the records that Pat's statement found, it's only where those records match records in a previous snapshot (copy of the same data with some changes) but where the matching records, using the above logic, result in a different date, e.g. it doesn't matter if shipdate changes but remains before cldate in both data sets , if shipearly isn't ticked, as cldate would be used as the date for both records and doesn't change

 

[isladogs]

Can't pretend to have followed all that.
Sorry I was no help

 

[InstructionWhich7142]

sorry, it's a little complicated,

basically the IIF using table sndplanvactual that is in the Where clause you're looking at is for a field on the query,

That field contains an identical IIF using table sndplan

and I only want the records where the result of one IIF <> the other IIF

 

[isladogs]

I'm still not following but don't spend time trying to explain it to me again as its probably perfectly clear to everyone else.
However I would still recommend using the approach I suggested in post #9 instead and scrapping all the IIFs