Nested IIf or module for DateCode formatting

[antonycat]

Hi All

I am trying to find the best way to work out the use by date codes for a number of products that we supply to a company. I am using Access XP.

The company has 2 divisions: TS and TM that are stored in a text field called chrCoDivision. We supply different products to each division.

At the moment both divisions have the same formatted date code in the format “dd mmm”. The date code is calculated in a single query [dtmDlyDate]+[dblMaxShelfLife] and then the appropriate format for the field properties is used. At present the multi-table query generates all the appropriate pricing data including the calculated date code for both divisions.

However now each division is requiring that the Date Code have a different format:
The format for TS: remains “dd mmm” but the format for TM is now “Wk 00 ddd”, where the week no is the companies week no for the use by date, which happens to be 9 weeks behind the Microsoft Week No.

A further complication is that certain products for the TM division have a Max and Min Shelf life, which affects the Date Code for the 7 days preceding their proposed final delivery date, ie the DateCode is the same for the last 7 days.

These are fields from the tables in the query that I would need to use:
dtmDlyDate from ‘Order’ table
chrCoDivision from Order Details’ table
dtmFinalDepotDly from ‘Product’ table
dblMaxShelfLife from ‘Product’ table
dblMinShelfLife from ‘Product’ table

Now in order to calculate the use by date codes, I am not sure whether to create a module or nested IIf statements in the calculated DateCode field in the query, which I would need some help with both to get them to work or should I split the query for each division ?.

Let me break up the logic as I have worked it out to be:

1) If the product is for division TS, only Max Shelf life & Dly Date is used for the date code as before ie. If chrCoDivision = TS, then DateCode = Format([dtmDlyDate]+[dblMaxShelfLife]) as ”dd mmm”) else

2) Now some products for division TM only have a MaxShelfLife and no FinalDepotDly ie. If chrCoDivision = TM and dtmFinalDepotDly is null, then DateCode = Format(([dtmDlyDate]+[dblMaxShelfLife])-63) as ”Wk 00 ddd”) else

3) Now for the products for division TM, which have both a Max and Min Shelf life, FinalDepotDly is not null and DateDiff([dtmDlyDate],[dtmFinalDepotDly]) > ([dblMaxShelfLife]-[dblMinShelfLife]) then DateCode = Format(([dtmDlyDate]+[dblMaxShelfLife])-63) as ”Wk 00 ddd”) else

4) Now for the products for division TM, which have both a Max and Min Shelf life, FinalDepotDly is not null and DateDiff([dtmDlyDate],[dtmFinalDepotDly]) < or = ([dblMaxShelfLife]-[dblMinShelfLife]) then DateCode = Format(([dtmFinalDepotDly]+[dblMinShelfLife])-63) as ”Wk 00 ddd”).

Thanking you in advance
Regards
Antony

 

[dhookom]

Do yourself and everyone else a favor and don't create nested IIf() statements. This complex of a calculation belongs in a module of standard calculations. That way, you can comment your code, use the same functions anywhere in your application, easily find the code, change it the next time they require a change,...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[antonycat]

I thought that would be the answer.

I will give the module a go but I have never done any programming at all so it could get interesting.

However what is the code required to format a calculated date field to read as "Wk 00 ddd” , where the week number is actually 9 weeks behind the microsoft week no and must be in the format 00.

Thanks
Regards
Antony

 

[dhookom]

The expression might be something like:
Format(DatePart("ww",DateAdd("ww",-9,[DateField])),"00")
Use "ww" for week of the year since "w" is weekday.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]