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

(0) Share

Report

Posted on by Shu Foster

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?

All responses (11)

Answers (1)

Verified answer

Shu Foster 25 on at

Like (0)

Report

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 21,081 Super User 2024 Season 1 on at

Like (0)

Report

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

Please check this.
Suggested answer

TeddyH 12,864 Super User 2024 Season 1 on at

Like (0)

Report

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 25 on at

Like (0)

Report

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 25 on at

Like (0)

Report

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 25 on at

Like (1)

Report

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 75,561 Super User 2024 Season 2 on at

Like (0)

Report

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

Hope this also helps.

Thanks

ZHU
Suggested answer

TeddyH 12,864 Super User 2024 Season 1 on at

Like (1)

Report

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 21,081 Super User 2024 Season 1 on at

Like (0)

Report

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

Please share your code or filters here
Shu Foster 25 on at

Like (0)

Report

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".