After DateAdd function values do not sort as dates

[kstearns04]

I have an Access DB which stores a Week Ending value as a number. I have created a report which needs the actual Week Ending Date which calculates fine (WeekEndingDt: IIf(([WEEKENDING]=0),"",DateAdd("ww",[WEEKENDING],"12/31/89&quot), however when I sort, I get January, October, November, December, February, and so on. It appears that the calculation is seen as a text format, not date format. I am actually trying to use a Between...And to pull specific date ranges but I get incorrect data unless the date range is for February through September! Is there a way to force a date data type within the recordsource of my report or some other solution to this problem? Thank you for any help possible!

 

[MichaelRed]

Many / most / all of the "dat" functions return either string or (more commonly) variant data types. to get back to date type , "Wrap" the date add in a CDate:

IIf(([WEEKENDING]=0),"",CDate(DateAdd("ww",[WEEKENDING],"12/31/89&quot))

or thereabouts



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[kstearns04]

Thanks for the info! I ended up using a format command on the field

WEEKENDINGDT: IIf(([WEEKENDING]=0),"",Format(DateAdd("ww",[WEEKENDING],"12/31/89&quot,"yyyy/mm/dd&quot

and the Between...And statement

Between Format([Forms]![frmBeginEndDates]![BeginningDate],"yyyy/mm/dd&quot And Format([Forms]![frmBeginEndDates]![EndingDate],"yyyy/mm/dd&quot

And it worked also. CDate will be easier in the future I think!