X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Value Of Iif Statement, Access 2016    
post Oct 13 2019, 02:44 PM

Posts: 1
Joined: 13-October 19


I have a report that has an IIf statement (unbound field) as follows:
=IIf([First of Codecat]=2,[AccessTotalsFORWARDTO]*1)

=IIf([First of Codecat]=3,[AccessTotalsFORWARDTO]*2)

=IIf([First of Codecat]=0,[AccessTotalsFORWARDTO]*0)

the result came out perfectly, but when i want to use the resulted value in VBA or Query. The system says no such value even though I created a field to transfer the IIF value to it as follows:
me.catone.value = me.text22.value

catone is the new field, and text22 is the unbound field creating the IIF.

Why the system do not recognize the value resulted from IIF as a valid value.

Go to the top of the page
post Oct 13 2019, 04:40 PM

Posts: 1,010
Joined: 25-January 16

You say you have an unbound field (you mean textbox?) but you show 3 expressions.

What event are you using for setting catone field value? I very much doubt your code will work behind a report. Updating field in table would likely require an UPDATE action SQL.

Why save calculated value instead of just calculating when needed?

=[AccessTotalsFORWARDTO] * Choose([First of Codecat]+1, 0, 1, 1, 2)

This post has been edited by June7: Oct 13 2019, 05:13 PM

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post Oct 13 2019, 04:46 PM

UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA


I'm not sure about whether you are saying you have one control or three controls, so I will assume three? In either case, you have written the IIf statement incorrectly. It should be in the format IIf(Logic, Result if logic is True, Result if logic is False). You have left out the False part, and we do not know what you mean to do there.

While sometimes this statement is forgiving and will seem to work, in other places it simply isn't going to give you a correct answer.

Try first completing the IIf statement False part and see if that gives you the result you want.


Ah, and I see June7 posted while I was typing. And I agree, if the result can be calculated, it need not be used to update another field.

"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
post Oct 13 2019, 04:48 PM

UA Admin
Posts: 36,175
Joined: 20-June 02
From: Newcastle, WA

Welcome to UtterAccess.

First, the standard syntax for the immediate if is not quite what you are using.

IIf ( expr , truepart , falsepart )

All three are required.

IIf() requires THREE elements, not two. Yours has only two.

=IIf([First of Codecat]=2,[AccessTotalsFORWARDTO]*1) and it is missing the "false" part. I suspect it will actually work out as if it's were orrect because the "false" returned will simply be blank.

so I would, therefore, expect the formula in your unbound control to be something like this:

=IIf([First of Codecat]=2,[AccessTotalsFORWARDTO]*1, "Value to Return if [First of Codecat] <> 2")

So, even though it seems to work in the report, I'd want to be sure it's going to be correct consistently.

Second, when the formula is in the control on the report, it can reference the fields in the query that provides records to the report.

When it's in a query, not part of the report, that reference may or may not be able to identify the proper field in that query.

To know how to advise you further, we'll want to see the actual, complete SQL from that query with the IIf() in it.
This post has been edited by GroverParkGeorge: Oct 14 2019, 10:57 AM

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Oct 13 2019, 09:11 PM

Posts: 1,022
Joined: 12-November 03
From: Iowa Lot

Are 0, 2 and 3 the only possible values for [First of Codecat]?

Assuming you're actually attempting a nested if, a shorter construct is possible.

=Iif([First of Codecat]>0,[AccessTotalsFORWARDTO] * ([First of Codecat]-1),0)

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    9th December 2019 - 04:45 AM