Skip to main content
Microsoft Dynamics NAV forum
Answered

How can I filter Date with No Value on Nav Query DataItemTableFilter

editSubscribe (0) ShareShare
ReportReport
Posted on by 25

Hello 

I am new to Nav Query

I am trying to creating a query through Nav Development Environment with filtering "Requested Receipt Date" is empty

I have tried "<>0" and " ", but it asks me to put a valid date

What is a valid date I should put in order to filter no date ones?

Categories:
  • Verified answer
    Shu Foster Profile Picture
    Shu Foster 25 on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    the answer, it is >01011900  -as SQL stores no date in the table as a date from the 1700’s rather than blank.

  • Suggested answer
    Nitin Verma Profile Picture
    Nitin Verma 20,984 Super User on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    Please check this.

    pastedimage1649379347293v1.png

  • Suggested answer
    TeddyH Profile Picture
    TeddyH 12,720 Super User on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    Don't remember exactly.

    On the replies, there should be a button or action to verify the answers. Only the original requester can see it.

  • Shu Foster Profile Picture
    Shu Foster 25 on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    Thank you Teddy, I have found the answer, how can I verify the answer?

  • Shu Foster Profile Picture
    Shu Foster 25 on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    Thank you, I have found the answer, it is >01011900  -as SQL stores no date in the table as a date from the 1700’s rather than blank.

  • Shu Foster Profile Picture
    Shu Foster 25 on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    Thank you, I have found the answer. it is >01011900 as SQL stores no date in the table as a date from the 1700’s rather than blank.

  • YUN ZHU Profile Picture
    YUN ZHU 59,978 Super User on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    Hi, just add others' reply.

    Blank date is specified bu 0D in NAV.

    More details: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/date/date-data-type

    pastedimage1649293574576v1.png

    Hope this also helps.

    Thanks

    ZHU

  • Suggested answer
    TeddyH Profile Picture
    TeddyH 12,720 Super User on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    SetFilter("Requested Receipt Date",'<>%1',0D); //not empty date

    SetRange("Requested Receipt Date",0D)l; //empty date

  • Nitin Verma Profile Picture
    Nitin Verma 20,984 Super User on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    Please share your code or filters here

  • Shu Foster Profile Picture
    Shu Foster 25 on at
    RE: How can I filter Date with No Value on Nav Query DataItemTableFilter

    Thank you but no, it does not work, it still shows "Not a valid Date".

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,068 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,591 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans