UtterAccess.com
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
> Iif Function, Access 2016    
 
   
Makya
post Aug 22 2019, 03:48 AM
Post#1



Posts: 18
Joined: 11-July 19



I have a table tblLeaveType with 2 fields LeavetypeID(pk) and LeaveType. (AL (Annual Leave), SL (Sick Leave), CP (Compassionate Leave))

Table 2 is tblLeaveForm - With LFID (pk), LeaveTaken (number), a lookupfield for LeaveType.

Query: I want to assign a number to in the LeaveForm for eg AL must be "1" and rest rest "0".

Queryfield: Count_AL: IIf([tblLeaveType]![LeaveTypeID]=[1],1,0) - Does not work

Count_AL: IIf([LeaveType]="AL",1,0) - Does not work. (and various other combinations)

My question is how can I use an IIF Function if I want to have the "= part " as some of the data in the fields? (AL or SL or CL)
Go to the top of the page
 
RJD
post Aug 22 2019, 04:06 AM
Post#2


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


Hi: We don't know how you are using this, or if a combobox is used on a form to select leave type, but if the query is against Table2, you should try taking the square brackets away from the [1] and just use 1.

And if you are truly using a table lookup field, you should change that as well, and switch to a combo box on a form for entry. Table lookup fields can cause problems for you.

HTH
Joe

--------------------
"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
 
Makya
post Aug 26 2019, 02:04 AM
Post#3



Posts: 18
Joined: 11-July 19



Thank you, I use a lookup combo box in the table. will change. I can not take away the brackets. If I o and go back the brackets are back again.

Will change to form and test.

Go to the top of the page
 
RJD
post Aug 26 2019, 05:43 AM
Post#4


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


Let us know how your test goes. But if we need to assist further, then I think we will need to see your db. Include relevant objects and example data (no sensitive data) in a cutdown db, and zip, then attach to a post here. Someone could take a look and see how we can help.

HTH
Joe

--------------------
"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
 
theDBguy
post Aug 26 2019, 10:41 AM
Post#5


Access Wiki and Forums Moderator
Posts: 76,423
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (Makya)
I use a lookup combo box in the table.

You might reconsider that, here's why.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 04:18 PM