How to filter by year? HELP

Becka
Becka Member Posts: 178
edited 2008-02-12 in NAV/Navision Classic Client
Hi all! :mrgreen:

So... I need to filter information in table by year.. For example: there is a field in table Posting date and i need to filter it only by year>>
14/12/07 that's the value of it, and i wanna filter it by 2007..
If any ideas, just help.. :)

Thanks to all

:mrgreen:
MCSD
Attain Navision

Answers

  • kine
    kine Member Posts: 12,562
    You mean to set filter like "01/01/07..31/12/07"? 8) This will filter the year for you...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Becka
    Becka Member Posts: 178
    nope, you got it wrong..
    for example: information in table:
    Posting date:
    01/01/07
    05/09/07
    25/06/08

    and i wanna filter it only by year, for example i need to filter it by 2007.. you see?
    MCSD
    Attain Navision
  • tinoruijs
    tinoruijs Member Posts: 1,226
    I think you should try to apply what Kamil suggested.
    When you use "01/01/07..31/12/07" this will filter the year for you..

    Or explain the problem a bit more.
    Tino Ruijs
    Microsoft Dynamics NAV specialist
  • Becka
    Becka Member Posts: 178
    :-k

    okay.. i have table A, and there's field named as year(text type:2006,2007,2008 and s.o.).. And i need to take the meaning of that year field and set filter on the table B.. But the case is, i can set the filter on the B table's field "Posting Date", i have a problem, "Posting Date" is a date type field, and i wanna set the filter on it by A table's field year... So if any ideas, just go ahead..
    MCSD
    Attain Navision
  • tinoruijs
    tinoruijs Member Posts: 1,226
    Ok.
    You can use functions that you can find in the C/AL Symbol Menu.
    SYSTEM, DATE, DMY2DATE.

    SETRANGE(date, DMY2DATE(1, 1, year), DMY2DATE(31, 12, year);
    Tino Ruijs
    Microsoft Dynamics NAV specialist
  • tinoruijs
    tinoruijs Member Posts: 1,226
    Becka wrote:
    :-k

    okay.. i have table A, and there's field named as year(text type:2006,2007,2008 and s.o

    By the way. To use the function DMY2DATE, year needs to be an integer.
    So it's better to define year as integer.
    When that's not possible anymore use EVALUATE(intYear, txtYear); to turn your text into integer.
    Tino Ruijs
    Microsoft Dynamics NAV specialist
  • kine
    kine Member Posts: 12,562
    Becka wrote:
    :-k

    okay.. i have table A, and there's field named as year(text type:2006,2007,2008 and s.o.).. And i need to take the meaning of that year field and set filter on the table B.. But the case is, i can set the filter on the B table's field "Posting Date", i have a problem, "Posting Date" is a date type field, and i wanna set the filter on it by A table's field year... So if any ideas, just go ahead..

    Try to think about it. What is Year? Year is everything between 1st of January and 31st of December, isn't it? If yes, filtering for year 2007 means filtering everything with date 1/1/07 to 31/12/07. And it is what I wrote. Forgot about "text filtering". If the date is saved as text, you can filter like "*/07". If it is date, you need to filter the whole year period by "1/1/07..31/12/07". If you do not know how to calc the limit dates, you can use 
      StartOfTheYear := CALCDATE('<-CY>',AnyDateWithinYear);
  EndOfTheYear := CALCDATE('<+CY>',AnyDateWithinYear);
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Becka
    Becka Member Posts: 178
    int MyYear;
    ......

    DMY2DATE(1,1,MyYear)..DMY2DATE(31,12,MyYear);
    That's part of my code... It's really works..
    MCSD
    Attain Navision

Categories