How do I use the syntax for blank date? I have data in a NAV table that could be blank. I want the date even if it is a blank. If I use "''@@''"&<cell>, I get the #value result.
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Pierce
I ran into the same problem. I have tried every possible wildcard but nothing worked out.
Eventually I have tackled this with a bypass. I have made a filter that uses the sorting features of Jet. The sortingresult can be limited to just one item. In this case the one with no date. Depending on the sortingorder the blank is at the top or the bottom of the list.
This is the trick that did for me:
=NL(…;"+Ending Date";"<dd/mm/yy";"LIMIT=";1)
where … stands for the rest of your NL formula
I hope this works as well for you.
Take care
Manu -
Jet Reports Historic Posts I ran into this same problem this week making a report that creates price lists.
The solution I have seems smoother.
I was using a datefilter that created "..10/1/08" and another that would do "10/01/08.." or whatever the target date is and getting the #Value error.
Instead, I used this for the before and after dates:
For Before Target Date
=CONCATENATE("''|",NP("datefilter",,$G$2))
That is: quotation mark apostrophe apostrophe | quotation mark
For After Target Date
=CONCATENATE("''|",NP("datefilter",$G$2,))
My NL
=NL("rows","Sales Price","Unit Price","+unit price",">0","Sales Type",G$6,"Sales Code","@@"&G$7,"Item No.",$E16,"Starting Date",$G$3,"Ending Date",$G$4,"limit=",1)
The unit price filter is there to pick the lowest price in the event that there are two price list entries with valid dates.
Please sign in to leave a comment.