Formatting a Filter on a DATA Field

[#1]

HI,

I put this Marco in the report to select a Date Range(7 days,7 week, 7month ,7 year before the TDate variable based ion a variable PERIODDESC selected)

Sub FilterData

    set i = ActiveDocument.Fields("PERIODDESC").GetSelectedValues

    set v2 = ActiveDocument.Variables("TDate")

    if i.count >0 then

        if i(0).Text ="DAY" then

            ActiveDocument.Fields("DATEPERS").Clear

            ActiveDocument.Fields("DATEPERS").select  cstr(">=" & cstr(day(dateadd("d",-7,CDATE(v2.Getcontent.String)))  & "/" & month(dateadd("d",-7,CDATE(v2.Getcontent.String))) & "/" & year(dateadd("d",-7,CDATE(v2.Getcontent.String))))  & " <=" & cstr(day(CDATE(v2.Getcontent.String))  & "/" & month(CDATE(v2.Getcontent.String)) & "/" & year(CDATE(v2.Getcontent.String))))

        elseif i(0).Text ="WEEK" then

            ActiveDocument.Fields("DATEPERS").Clear

            ActiveDocument.Fields("DATEPERS").select  cstr(">=" & cstr(day(dateadd("d",-56,CDATE(v2.Getcontent.String)))  & "/" & month(dateadd("d",-56,CDATE(v2.Getcontent.String))) & "/" & year(dateadd("d",-56,CDATE(v2.Getcontent.String))))  & " <=" & cstr(day(CDATE(v2.Getcontent.String))  & "/" & month(CDATE(v2.Getcontent.String)) & "/" & year(CDATE(v2.Getcontent.String))))

        elseif i(0).Text="MONTH" then

            ActiveDocument.Fields("DATEPERS").Clear

            ActiveDocument.Fields("DATEPERS").select  cstr(">=" & cstr(day(dateadd("m",-7,CDATE(v2.Getcontent.String)))  & "/" & month(dateadd("m",-7,CDATE(v2.Getcontent.String))) & "/" & year(dateadd("m",-7,CDATE(v2.Getcontent.String))))  & " <=" & cstr(day(CDATE(v2.Getcontent.String))  & "/" & month(CDATE(v2.Getcontent.String)) & "/" & year(CDATE(v2.Getcontent.String))))

        else

            ActiveDocument.Fields("DATEPERS").Clear

            ActiveDocument.Fields("DATEPERS").select  cstr(">=" & cstr(day(dateadd("yyyy",-7,CDATE(v2.Getcontent.String)))  & "/" & day(dateadd("yyyy",-7,CDATE(v2.Getcontent.String))) & "/" & year(dateadd("yyyy",-7,CDATE(v2.Getcontent.String))))  & " <=" & cstr(day(CDATE(v2.Getcontent.String))  & "/" & month(CDATE(v2.Getcontent.String)) & "/" & year(CDATE(v2.Getcontent.String))))

        end if

    end if   

end Sub

The TDate Variable is in DD/MM/YYYY but when i use a function DAY() in my PC wiith regional setting MM/DD/YYYY the functioon return the month:

for ex:

Day(12/03/2012) return 03.

how I can do this?

Many thanks

[er_mohit]

hey you can try this code might be helpful

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07