Can anyone tell me where the issue is with this? I am missing something and cant stare at it any longer. It is supposed to "check to see If H9 is empty, if so then check to see if the ACTUALNETWEIGHT is there and use it if it is, if its not there then it needs to check cell P9, if there is data in it, use it and if not use ESTIMATEDWEIGHT.
=IF($H9="",IF(NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)>0,NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)),IF($P9>0,$P9,NL("First","shpContents","EstimatedNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)))
Date
Votes
13 comments
-
Jet Reports Historic Posts hi,
do you get an error-message?
regards
jetsetter -
Jet Reports Historic Posts btw…you choose a data_source, but no company…is that correct ?
and i would set the "datasource" at the end of each nl-function, after the filter on "shipmentID"…but i am not really sure if that makes any difference…. ;) -
Jet Reports Historic Posts I'd say it's about the closing brackets… I cut the second ) at the end of NL that checks the ActualNetWeight and pasted it at the very end of the IF function.
=IF($H9="",IF(NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)>0,NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9),IF($P9>0,$P9,NL("First","shpContents","EstimatedNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9))))
Does this work? -
Jet Reports Historic Posts Nope, Hansfousert that didn't do it either. I only get results if the first condition is true (if actual weights exist, use them), the other two still do not work. I had tried that as well. There has to be a syntax error somewhere?
Jetsetter, I didn't choose a company as there is no need for this in this query. It is using a universal connector to SQL. -
Jet Reports Historic Posts Do you get an error message, or how do you know, that the function works wrong?
-
Jet Reports Historic Posts I do not get an error but I only get data if the first condition is met. If either of the other are met I get a blank field and can confirm from the DB that there is data that should display under this criteria.
-
Jet Reports Historic Posts …ok…Did you ceck cell P9? is the value really "0" or "<>0", or is it maybe blank…?
-
Jet Reports Historic Posts I agree with Hansfousert - looks like a brackets error. Breaking down your formula, here's what I'm seeing….
FIRST IF: =IF($H9="",
IF 1st IF TRUE - then 2nd IF Statement: IF(NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)>0,
IF 2nd IF TRUE: NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)
IF 2nd IF FALSE: close 2nd IF ),
IF 1st IF FALSE - then 3rd IF Statement: IF($P9>0,
IF 3rd IF TRUE: $P9,
IF 3rd IF FALSE: NL("First","shpContents","EstimatedNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)))
You aren't telling the formula to do anything if the ActualNetWeight is not greater than 0 - you're telling it to stop that IF statement.
Generally, when nesting IFs, you need to truely nest them, in other words, don't close down the 2nd IF, you need to let it lead into the 3rd IF
so instead of:
=IF($H9="",IF(NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)>0,
NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)),
IF($P9>0,$P9,NL("First","shpContents","EstimatedNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)))
it looks like you need:
=IF($H9="",IF(NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)>0,
NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9),
IF($P9>0,$P9,NL("First","shpContents","EstimatedNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)))) -
Jet Reports Historic Posts To answer Jetcity; it is either blank or has a number in it.. So >0 give me the number in P9 else give me the other number (EstimatedNetWeight).
-
Jet Reports Historic Posts I agree with Hansfousert - looks like a brackets error. Breaking down your formula, here's what I'm seeing….
FIRST IF: =IF($H9="",
IF 1st IF TRUE - then 2nd IF Statement: IF(NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)>0,
IF 2nd IF TRUE: NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)
IF 2nd IF FALSE: close 2nd IF ),
IF 1st IF FALSE - then 3rd IF Statement: IF($P9>0,
IF 3rd IF TRUE: $P9,
IF 3rd IF FALSE: NL("First","shpContents","EstimatedNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)))
You aren't telling the formula to do anything if the ActualNetWeight is not greater than 0 - you're telling it to stop that IF statement.
Generally, when nesting IFs, you need to truely nest them, in other words, don't close down the 2nd IF, you need to let it lead into the 3rd IF
so instead of:
=IF($H9="",IF(NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)>0,
NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)),
IF($P9>0,$P9,NL("First","shpContents","EstimatedNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)))
it looks like you need:
=IF($H9="",IF(NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9)>0,
NL("First","shpContents","ActualNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9),
IF($P9>0,$P9,NL("First","shpContents","EstimatedNetWeight","DataSource=","Traffic_Data","ShipmentID","@@"&$F9))))
I agree with what you are saying here and understand it but it still does not work. I just don't understand why? -
Jet Reports Historic Posts At this stage, I can only suggest uploading a version of the report so that someone can look at the formula in context, or test it piece by piece to see if you can find a portion of it that is not working.
-
Jet Reports Historic Posts Unfortunately it wouldn't work if I posted it here as it needs the data connection to work. I have tested each piece and each works as a standalone but not when in the If query. I will seek help elsewhere. Thank you for trying.
-
Jet Reports Historic Posts Hi again,
could it be, that the "universal connector" is the problem?
Maybe it can't handle these nested IF-Formulars?
Please let us know, if you find the error or the solution for your Problem, because i also use the universal connecot for aa database (but without nested Ifs)…
regards
jetsetter
Please sign in to leave a comment.