Solved IIf query stopped working (1 Viewer)

[mcalex]

Hi all,
A sanity check please? I have the following nested IIf query that populates a field depending on a couple conditions. It has worked for ages, but only gets run once a biennial. This morning I got a compile error. Has IIf syntax changed perhaps?

Query:

UPDATE Member
SET Member.TermExpired
= IIf( [Member.ExtraOrdinaryExpiry]<Date()
    ,    [Member.ExtraOrdinaryExpiry]
    ,     IIf( [Member.OrdinaryExpiry]<Date()
          ,    [Member.OrdinaryExpiry] ));

Do I need to include the final false condition (to do nothing)? I changed it to insert 'null' on false

          ,    [Member.OrdinaryExpiry]
          ,     Null ));

But either way when running I get a Compile error. in query expression

Member table:
TermExpired is not a required field.

What am I doing wrong?

 

[arnelgp]

Do I need to include the final false condition (to do nothing)? I changed it to insert 'null' on false

you need to.
but everytime you need to check this date field and update?
what you need is just a Query that will output a Calculated column meaning
you don't saved TermExpired to your table, you just calculate it in the query.

 

[mcalex]

Essentially, the query is only run every couple years, after a bulk external update to OrdinaryExpiry and ExtraOrdinaryExpiry.

It's an inherited system. The TermExpired field was originally manually input . Thinking about it, I may be able to update the field based on the min of 'OrdinaryExpiry' and 'ExtraOrdinaryExpiry', no, idiot, Access isn't Excel.

I'm still puzzled as to why the query stopped working and what the correct syntax should be.

 

[arnelgp]

you can try:

UPDATE Member
SET Member.TermExpired
= Switch([Member.ExtraOrdinaryExpiry]<Date()
    ,    [Member.ExtraOrdinaryExpiry]
    ,     [Member.OrdinaryExpiry]<Date()
          ,    [Member.OrdinaryExpiry]
    , True, [TermExpired])

 

[Pat Hartman]

I'm still puzzled as to why the query stopped working and what the correct syntax should be.

"Stopped Working" covers a lot of ground. Would you care to share the details of what "stopped working" actually means?

 

[CJ_London]

Do I need to include the final false condition (to do nothing)? I changed it to insert 'null' on false

you need to.

No - you don't if this is sql code. if the parameter is not included the iif will return null if false. You definitely do need the false parameter if it is vba code

run this sql to demonstrate you don't need it

SELECT TOP 1 IIf(1=2,True,False) AS [with false], IIf(1=2,True) AS [without false], [without false] Is Null AS [is without false null]
FROM MSysObjects

Query1 Query1

with false	without false	is without false null
0​	​	-1​

 

[mcalex]

>what "stopped working" actually means?

As mentioned in OP: Compile error. in query expression (including Access grammar)
Then it lists the query text. I double click the query from the object sidebar thing, it warns me about updating a large number of rows and immediately on hitting OK, I get the error dialog.

Investigation shows it might be related to Front End/Back End split (although it has been running like this in the past).

 

[CJ_London]

Just a guess but perhaps the query has become corrupted. Have you tried copying the sql to a new query - this would force the creation of a new query plan. Someone said that compacting will remove all query plans so they are recreated on next use. Not investigated myself but compacting is something else to try

Do I need to include the final false condition (to do nothing)?

it won't do nothing - it will assign null to the termexpired field

 

[Pat Hartman]

Did you try to refresh the link to the table? Is the field there or gone?

 

[Isaac]

Hi all,
A sanity check please? I have the following nested IIf query that populates a field depending on a couple conditions. It has worked for ages, but only gets run once a biennial. This morning I got a compile error. Has IIf syntax changed perhaps?

Query:

UPDATE Member
SET Member.TermExpired
= IIf( [Member.ExtraOrdinaryExpiry]<Date()
    ,    [Member.ExtraOrdinaryExpiry]
    ,     IIf( [Member.OrdinaryExpiry]<Date()
          ,    [Member.OrdinaryExpiry] ));

Do I need to include the final false condition (to do nothing)? I changed it to insert 'null' on false

          ,    [Member.OrdinaryExpiry]
          ,     Null ));

But either way when running I get a Compile error. in query expression

Member table:
TermExpired is not a required field.
View attachment 95429

What am I doing wrong?

Just for my own learning and growth, would you mind posting a screenshot that shows a "Compile" error related to a saved Query object?

 

[mcalex]

Thank you all for the o/night assistance

run this sql to demonstrate you don't need it

Yup, that works, with that result.

Did you try to refresh the link to the table? Is the field there or gone?

I have now refreshed all links to the back end. The table is there and all fields are present and accounted for. We have had machine upgrades about 18 months ago, but both FE and BE are on network drives. At this point I'm not sure if the BE split is a red herring or one of a couple issues resulting in the same error but now I've relinked it shouldn't be causing grief going forward.

posting a screenshot that shows a "Compile" error

That's weird. In the process of setting up the screenshot,, I found that clicking the 'Run' exclamation mark in the ribbon produces the error, but double-clicking the query in the shutter bar(?) produces a result - but only while that query is open in sql view. Double-clicking without it open causes the error.

Trying to recreate the query through design view works; with intellisense suggesting table and field names and helping with the Date function, but on losing focus tells me there's an error ...

and highlights the first Date() function in the 'Update To:' field of the QBE when I click OK.

Looks like I have to rummage through the garden shed to find my old VBA toolbox. Last time I used it is a very dim memory ...

 

[CJ_London]

check your vba references (Tools>References). As a minimum you should have ticked

Visual Basic for Applications
Microsoft Access xx.0 object library (xx is the version number relevant to your access version - 2010 is 14.0 for example)
OLE Automation

The other possibility is if your access version has been upgraded and you have a field named Date in your query recordset which has confused Access (Access has become less and less tolerant of poor naming in each new version release)

 

[mcalex]

Well there's a name I haven't seen in years. It appears a dev before me had included a module 'modJustify' written by Stphen Lebans

- back in the late 90s. It seems to have fallen over when we - finally - upgraded machines to 64bit. The module isn't actually needed anymore and removing it solved the issue.

Thanks all. - SOLVED