I am trying to pull only information when a field is not null from Microsoft CRM.
Here is the function:
=NL("Count","pixelopt_jobshiprecord",,"DataSource=","CRM","pixelopt_ShipDatetoECP",$G5,"pixelopt_ProcessingShipRecordId","<>''")
It returns #VALUE though I know there are records that do not have a NULL value.
Thank you for your help in advance.
Date
Votes
6 comments
-
Jet Reports Historic Posts Official comment Hi,
So first, your filter doesn't really make sense; if you are filtering for a specific posting date of 2/28/2012, then you should never get records with a blank posting date anyway, so this part of the filter is unnecessary.
Anyway, the problem with your filter for blanks is that you mistook 2 single quotes (') for a double quote character ("). In Jet filtering, if you want to filter out blanks, you use 2 single quote characters like this: <>''. If you copy that text into notepad or Excel it may be more obvious that this is 2 single quotes, NOT a double quote character. So your filter should work correctly if you just change your double quote character to 2 single quote characters like this:=NL("Rows","Cust. Ledger Entry",,"Posting Date","<>''")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts When you select the cell and go to Jet -> Debug, what information does it tell you? This is a useful tool for diagnosing problems when Jet functions return #VALUE.
Regards,
Hughes -
Jet Reports Historic Posts Sql returned the following errors: Conversion failed when converting from a character string to uniqueidentifier
-
Jet Reports Historic Posts Hmmm, I've reproduced your issue. This looks like an issue, so I'm going to file it so it can be fixed in a future build of Jet. In the meantime, it seems that you could work around the problem with something like this:
=NL("Count","pixelopt_jobshiprecord",,"DataSource=","CRM","pixelopt_ShipDatetoECP",$G5,"pixelopt_ProcessingShipRecordId","<>00000000-0000-0000-0000-000000000000")
While this isn't quite identical to your previous filter, it will filter out the NULL values correctly, assuming you don't have a Guid in your system with all 0's (which I think is fairly likely). Does that work for you? If it still returns #VALUE, what is the new error message?
Regards,
Hughes -
Jet Reports Historic Posts Hugh,
I am having a similar issue but in a Date field. I need to filter out null values in the "Cust. Ledger Entry" table within the "Closed at Date" field.
When I try using <>", Jet would not accept it, and wanted to auto correct to this string:
=NL("Rows","Cust. Ledger Entry",,"Posting Date","02/28/2012&<>""")
which uses three " characters and returned a debug message of Invalid Filter of "02/28/2012&<>"". I tried using <>0 and >01/0/1900 without results.
Any other suggestions to filter out the Null Closed at Date fields? -
Jet Reports Historic Posts Hugh,
You have found the exact issue- I mistook two single quote marks for one double quote mark. The report is running and is exluding the Null fields as I needed!
Again, you've done an excellent job with very little (and even wrong) information. My sample was a rush job of simply illustrating the issue (and I blew it) :oops: .
Thanks again! 8-)
-John
Please sign in to leave a comment.