extract last 8 weeks from data field with yearweek

[jos1972]

hello,


I'm new to the forum and pretty new to access, so I hope someone can help me with this question.

I have a column in my database with the year and week in the following format:20141 for week 1 in 2014 and 201452 for week 52.
Now I would like to build an if then functions that returns the value of another column only if the week falls in the region "last 8 weeks".
As an example: for week 1 2015 the iff then function should return week 46-2014 - week 1 2015.
So far the below if then worked, however now that we are in the first week of the new year in only returns week 1 2015.

CW_LST8WK_QTY: IIf([tblData]![Week]>=([Forms]![frmImport]![txtWeek]-7) And [tblData]![Week]<=[Forms]![frmImport]![txtWeek],[tblData]![LAST_WEEK],Null)

Thank you for your help.
With best regards,
Jos

[orange]

Jos,


Just a comment related to design. Relational databases are easiest to use and maintain when you design with atomic data. That is, 1 fact, 1 field.
Difficulties almost always arise when trying to codify various facts into a single field.

don database principles

[amrut]

I have a column in my database with the year and week in the following format:20141 for week 1 in 2014 and 201452 for week 52.

What field type have you used to store data number/text ?

To get what you want, you need to separate the week number from the year and then compare. One more way is to convert your field to a date and use DateAdd function to retrieve the data for last 8 weeks. See here http://www.techonthenet.com/access/f...te/dateadd.php .

[jos1972]

Jos,


Just a comment related to design. Relational databases are easiest to use and maintain when you design with atomic data. That is, 1 fact, 1 field.
Difficulties almost always arise when trying to codify various facts into a single field.

don database principles

hello orange,
thank you for your reply. Actually the dataset has a separate column for weeks and years. Does that make it easier?
thnx

[lfpm062010]

This might work for you.

Using

SELECT TOP 8 *
FROM TABLENAME
ORDER BY YEAR DESC, WEEKNO DESC;

For more deatil,
http://www.blueclaw-db.com/accessque...select_top.htm