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 In Query, Access 2016    
 
   
mike60smart
post May 26 2020, 08:05 AM
Post#1


UtterAccess VIP
Posts: 14,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am trying to use the following Expression in a Query but it displays #Error

Any help in pointing out where I am going wrong appreciated.

CODE
DaysToComplete: IIf([Date_Completed] Is Null,fNetWorkdays([DATERECEIVEDINQUEUE],[DATE_COMPLETED])+1,"")

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
nuclear_nick
post May 26 2020, 08:43 AM
Post#2



Posts: 1,873
Joined: 5-February 06
From: Ohio, USA


Instead of …
CODE
DaysToComplete: IIf([Date_Completed] Is Null,fNetWorkdays([DATERECEIVEDINQUEUE],[DATE_COMPLETED])+1,"")

… try …
CODE
DaysToComplete: IIf(IsNull([Date_Completed]),fNetWorkdays([DATERECEIVEDINQUEUE],[DATE_COMPLETED])+1,"")

… and let me know the results.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
mike60smart
post May 26 2020, 08:54 AM
Post#3


UtterAccess VIP
Posts: 14,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi Nick

Still the same result


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
GroverParkGeorge
post May 26 2020, 09:10 AM
Post#4


UA Admin
Posts: 37,513
Joined: 20-June 02
From: Newcastle, WA


When trying to use a complicated parameter in a query, I often find it helpful to break it down into smaller chunks for testing. I.e. put the components of the IiF() into columns in the query, removing the parameter, and see what each component returns.

Here, it looks like you trying to test for null value in the [Date_Completed] field, then if it IS null, you are also trying to use it as part of the fNetworkdays calculation, which, of course, should fail.

--------------------
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
 
mike60smart
post May 26 2020, 09:26 AM
Post#5


UtterAccess VIP
Posts: 14,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi George

Yes I was trying to not show the #Error which shows when I used the following:-

CODE
DaysToComplete: fNetWorkdays([DATERECEIVEDINQUEUE],[DATE_COMPLETED])+1


Is there a method to not show the #Error when there is no Date_Completed Value?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
GroverParkGeorge
post May 26 2020, 09:32 AM
Post#6


UA Admin
Posts: 37,513
Joined: 20-June 02
From: Newcastle, WA


Your Iif() tests for [Date_Completed] Is Null

If that is TRUE, i.e., if [Date_Completed] Is Null, then you go ahead and try to calculate using it.

Reverse the logic and either test for [Date_Completed] Is Not Null, and then try to calculate using it if it is not null, or simply change the position of the two arguments so that " " is returned when [Date_Completed] Is Null

The pattern for Iif() is

IIf(expr, truepart, falsepart)

--------------------
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
 
mike60smart
post May 26 2020, 09:43 AM
Post#7


UtterAccess VIP
Posts: 14,034
Joined: 6-June 05
From: Dunbar,Scotland


Hi George

I put it down to age. This now works

As you say "The Expr is"

CODE
DaysToComplete: IIf([Date_Completed] Is Null,"",fNetWorkdays([DATERECEIVEDINQUEUE],[DATE_COMPLETED])+1)

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
GroverParkGeorge
post May 26 2020, 09:59 AM
Post#8


UA Admin
Posts: 37,513
Joined: 20-June 02
From: Newcastle, WA


Sometimes we can't see the forest for the trees.

--------------------
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
 
tina t
post May 26 2020, 02:30 PM
Post#9



Posts: 6,692
Joined: 11-November 10
From: SoCal, USA


QUOTE
Sometimes we can't see the forest for the trees.

You and I travel to the beat of a diff'rent drum
Oh, can't you tell by the way I run
Every time you make eyes at me Wo oh
You cry and you moan and say it will work out
But honey child I've got my doubts
You can't see the forest for the trees

So, don't get me wrong It's not that I'm knockin'
It's just that I'm not in the market
For a boy who wants to love only me
Yes, and I ain't sayin' you ain't pretty
All I'm sayin's I'm not ready for any person
Place or thing to try and pull the reins in on me
So Goodbye, I'll be leavin'
I see no sense in the cryin' and grievin'
We'll both live a lot longer if you live without me

Oh, don't get me wrong It's not that I'm knockin'
It's just that I'm not in the market
For a boy who wants to love only me
Yes, and I ain't sayin' you ain't pretty
All I'm sayin's I'm not ready for any person
Place or thing to try and pull the reins in on me
So Goodbye, I'll be leavin'
I see no sense in the cryin' and grievin'
We'll both live a lot longer if you live without me

Different Drum, Linda Ronstadt and the Stone Poneys. many artists have recorded this song, of course, but i thought y'all would like the drive down memory lane... ;) tina
This post has been edited by tina t: May 26 2020, 02:31 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 05:43 AM