Multiple IIF Statement...

[Guest]

G'Day,

I would like to place a version date (based on the current date) on reports
that are printed for each day of the week. For example if today is "Thursday"
14th and I printed the report for "Saturday" it will show a version date of
the 16th.

The version date would only be one week in advance at the most. I have been
testing a query with an IIF statement but it ends up being too complex.

Today: Date()
Day: Format([Today],"dddd")

Version: IIf([Day]="Monday" And
[txtDespatchSheetDay]="Monday",Date()+0,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Tuesday",Date()+1,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Wednesday",Date()+2,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Thursday",Date()+3,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Friday",Date()+4,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Saturday",Date()+5,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Sunday",Date()+6,If([Day]="Tuesday" And
[txtDespatchSheetDay]="Monday",Date()+6,IIf([Day]="Tuesday" etc

Can anyone please help me in how I should be doing this?

Thank you,

Anthony

 

[Guest]

You could certainly make good use of the DateAdd functions and the Weekday
function, but I think you'll have trouble if you're comparing days with the
text in your textbox. Could you display the date instead?

Barry

 

[Guest]

Thanks for replying... Yes the intension is to show a date.
The user selects the report for the particular day (day name) from a
combobox (shows the seven days of the week).
The idea is that if today is Thursday (14th) and they choose the Saturday
report, it will show the 16th September 2006 as the Version date. So if they
printed the entire week it will show on the report what the actual date will
for the day selected.

Hope that makes sense...

You could certainly make good use of the DateAdd functions and the Weekday
function, but I think you'll have trouble if you're comparing days with the
text in your textbox. Could you display the date instead?

Barry

G'Day,

I would like to place a version date (based on the current date) on reports
that are printed for each day of the week. For example if today is "Thursday"
14th and I printed the report for "Saturday" it will show a version date of
the 16th.

The version date would only be one week in advance at the most. I have been
testing a query with an IIF statement but it ends up being too complex.

Today: Date()
Day: Format([Today],"dddd")

Version: IIf([Day]="Monday" And
[txtDespatchSheetDay]="Monday",Date()+0,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Tuesday",Date()+1,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Wednesday",Date()+2,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Thursday",Date()+3,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Friday",Date()+4,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Saturday",Date()+5,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Sunday",Date()+6,If([Day]="Tuesday" And
[txtDespatchSheetDay]="Monday",Date()+6,IIf([Day]="Tuesday" etc

Can anyone please help me in how I should be doing this?

Thank you,

Anthony

 

[Baz]

G'Day,

I would like to place a version date (based on the current date) on reports
that are printed for each day of the week. For example if today is "Thursday"
14th and I printed the report for "Saturday" it will show a version date of
the 16th.

The version date would only be one week in advance at the most. I have been
testing a query with an IIF statement but it ends up being too complex.

Today: Date()
Day: Format([Today],"dddd")

Version: IIf([Day]="Monday" And
[txtDespatchSheetDay]="Monday",Date()+0,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Tuesday",Date()+1,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Wednesday",Date()+2,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Thursday",Date()+3,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Friday",Date()+4,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Saturday",Date()+5,IIf([Day]="Monday" And
[txtDespatchSheetDay]="Sunday",Date()+6,If([Day]="Tuesday" And
[txtDespatchSheetDay]="Monday",Date()+6,IIf([Day]="Tuesday" etc

Can anyone please help me in how I should be doing this?

Thank you,

Anthony

I assume that txtDespatchSheetDay is your combo box. I think that the first
thing you should do is to modify the combo box as follows:

Set it's Record Source property to this:

1;Sunday;2;Monday;3;Tuesday... and so on.

I'm starting with Sunday because that's the default first day of the week
for the Weekday function, which we'll use later. If you want Monday to
appear at the top, simply rearrange it like this:

2;Monday;3;Tuesday; ... and so on until ... 7;Saturday;1;Sunday

i.e. it's the number associated with each day that's important, not the
order you put them in.

Set the Bound Column property to 1. Set the Column Count property to 2.
Set the Column Widths property to 0.

You now have a combo box whose value is an index number to the day of the
week, but which displays the day name.

Then, you could do something like this (warning: not tested!):

Version: Date() + [txtDespatchSheetDay] - Weekday(Date())

 

[Guest]

Thank you Baz...I'll try that tomorrow as it looks simpler to what I have
done. But I have already solved it a different way! =)

I have made a make table query to add the next seven days dates to a table
using the Weekday Function and then using this table, I have used a DLookup
on the report with the combo box.

It appears to be working but need to do couple more checks...

Thanks to Barry for suggesting the Weekday Function.

Anthony