DLookup Returns First Value Specified (1 Viewer)

[J_Cat]

Hello,

I'm wrestling with finding a way to install a Value to a field when certain crieteria are met. For instance, in working with scheduling on a manufacturing day calendar, I would like to to take a schedule that falls on a holiday and replace the holiday date with the previous viable workday date.

Right now I'm getting the first available CalDate in the table, 8/25/12.

So for this small table

CalDate TypeDay
11/19/12 MDAY
11/20/12 MDAY
11/21/12 MDAY
11/22/12 HOLIDAY
11/23/12 HOLIDAY
11/24/12 WEEKEND
11/25/12 WEEKEND

I would like to find any schedules that fall on "HOLIDAY",11/22 or 11/23, and reassign them to 11/21/12. So that would mean, for the sake of my query, anything with Holday would automatically reference the last MDAY just prior to that holiday.

I'm ignoring the weekend because we, ideally, never have automated schedules for the weekend.

Here's what I have in myu query
IIf([TypeDay]='HOLIDAY',DLookUp('CalDate','Tbl_CalMDay','TypeDay'<>'HOLIDAY'),[CalDate])

Any ideas?

Thanks in advance.

 

[bob fitz]

Try:
SELECT Tbl_CalDay.CalDate, Tbl_CalDay.TypeDay, IIf([TypeDay]<>"HOL",[CalDate],DMax("[CalDate]","Tbl_CalDay","TypeDay <> 'HOL' AND CalDate < #" & [CalDate] & "#")) AS Expr2
FROM Tbl_CalDay;

 

[J_Cat]

Thanks Bob,
Headway has been made.

However, using

IIf([TypeDay]<>"HOL",[CalDate],DMax("[CalDate]","Tbl_CalDay","TypeDay <> 'HOL' AND CalDate < #" & [CalDate] & "#"))

Gets the message "#Error" on the Holiday, instead of the last valid MDay that I need.

I reversed it a bit and got it to provide the holday date, but all other days' dates erred out as above.

IIf([TypeDay]="HOL",[CalDate],DMax("[CalDate]","Tbl_CalDay","TypeDay = 'HOL' AND CalDate < #" & [CalDate] & "#"))

I know your solution is on the right track but for the life of me I'm struggling.

 

[bob fitz]

Where are you using the expression that you posted?

 

[J_Cat]

Haha! Alright - I see what happened, I dropped a letter from the name of my own table. The correction has been made and the SQL works like a charm.
Thank you very much Bob, this is awesome!

 

[bob fitz]

You're wlecome. Glad you got it sorted.