0

How to filter on empty date fields

Jet Reports Historic Posts

January 17, 2008 09:40

None

Follow

Hi

I want to make a filter that returns the items from a table with an empty datefield. But I do not manage to find an appropriate filter for this field. I have tried several possibilities:
0
00/00/00
=""
"="""
"@@&"""

I am at wit's end. Anyone an idea?

Best regards

Manu

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts January 17, 2008 12:55

  Hey Manu,
  
  Your close 8-)
  
  You can do this with either
  
  "''"
  which reads as double quote, single quote, single quote, double quote
  
  or you can use
  
  "@@"
  
  which reads as double quote, at, at, double quote.
  
  Be sure to take a minute and use our Introductions area to tell us a little about yourself.

• 0
  
  Jet Reports Historic Posts January 22, 2008 13:35

  Hey Shermang
  
  I have tried both solutions, but neither did work. It always reported a blank result where it should have been a value.
  
  I want to dig up the latest unit price of an item. Every unitprice has a starting and endingdate. Except the current unit price. In this case there is only a starting date. The ending date field is left empty. So I thought that I could use this empty field as a filter to collect the latest unit price.
  
  I have found a solution but it is a detour.
  
  =nl("Rows";"Sales Price";"Unit Price";"Sales Code";"1-HV";"Item No.";E42;"+Ending Date";"<17/01/08";"LIMIT=";1)
  
  Anyhow, it works.
  
  Cheers
  
  Manu
  
  Ps: we are using Office 2000, Jet 4.0.7 and Navision 4.0

• 0
  
  Jet Reports Historic Posts January 22, 2008 14:47

  Hey Manu,
  
  It looks like "@@" doesn't work the same way on 4.0.7. The following works fine on v7.
  
  =NL("First","Sales Price","Ending Date","Item No.",$D6,"Ending Date","@@")
  
  or to be a closer match to what your formula,
  
  =NL("Last","Sales Price","Unit Price","Item No.",$D5,"+Starting Date",NP("DateFilter",,$D$3))
  
  However, both of these use features that may not have existed in 4.0.7.

• 0
  
  Jet Reports Historic Posts January 23, 2008 16:25

  Cool :idea:
  
  I didn't know you could use "@@" to mean blank. That's a lot easier to read than "''" which is what I've been using in my reports.

• 0
  
  Jet Reports Historic Posts January 31, 2008 17:30

  Something to be aware of when using "@@": It doesn't work if the FilterField is a Navision FlowFilter, as FlowFilters are not allowed to contain any wildcard characters ('*','?','@'). In this case, you still must use "''".

• 0
  
  Jet Reports Historic Posts March 12, 2012 19:45

  I'm getting the error: Invalid Filter "@@", also "''"(double-quote single-quote single-quote double-quote), and also "@@"&"''".
  The Jet Report was created with v5.3.1, running on Win XP, Nav 4.0. There is a date field which could be blank. It used to work with '''(3 single-quote).
  
  Now the PC has been upgraded with Jet REport 2010, Win 7 64-bit, and Excel 2010.
  The report option for the date field with '''(3 single-quote) no longer work. I get the error: Invalid Filter '''.
  
  I checked the solution, and found this posting, tried "@@", "''", and the combination of those, none of them works.
  
  Does Jet 2010 or Excel 2010 change something?
  Please help.

• 0
  
  Jet Reports Historic Posts March 12, 2012 20:04

  I tried @@ without double-quote, and it worked!!
  Sorry for the confusion. Please discard my posting.
  
  Thanks.