Access: Day of week query [Resolved]

[RobDog888]

I need to create a query where it will take a field and place that value
into some fields based upon were that related date field fits into
the next 4 dynamically calculated Mondays.

So basically I need to be able to calculate from the current day
the current weeks Monday and the next 4 Mondays after that
and alias those fields as below. Then compare the records date
to where it would be less than the aliased fields but not less than
the previous Mondays date.



Ex. convert hard coded dates to dynamic IIFs somehow.

Table1:
PO = Text; dDate = PO's Date

PO      dDate
----------------
222.01  11-05-04
333.05  11-12-04
111.01  11-21-04
444.01  11-23-04
555.02  11-30-04

Report:
PO       11-08-04     11-15-04     11-22-04     11-29-04     12-06-04
---------------------------------------------------------------------
222.01   100
333.05                150
111.01                             200
444.01                                          1000
555.02                                                       50

Thanks for any assistance on this.

[Ecniv]

SELECT 
 tblRobDog.PO, 
 tblRobDog.dDate, 
 Weekday([dDate],2) AS [TheWeekday], 
 [dDate]+(8-Weekday([dDate],2)) AS [NextMonday]
FROM tblRobDog;

Mighht give you an idea?


Vince

[RobDog888]

Thanks. I will try some variations from this.
Would creating
this query in SQL 2k as a view be better? Can the view be linked
to the Access db?

[Ecniv]

Never done it - but if its a query/view in theory it would appear in Access... Whether it does and whether it works dunno

Try n see? Post up if it does - it'd be useful for the future.


Vince

[RobDog888]

I got it finally. I used allot of nested IIF's to determine if the
dDate was a datediff of no more than 7 of the weekday of the
current date's Monday. Then 14, 21, 28, etc.

So I end up with a column of Nulls if they are out of the 5 week
range. The ones that are left are the Monday's date. I filter those
dates in 5 repeated columns in a report. Then I applied filtering
again to only show that weeks date's quantity. Messy query and
report, but its working now.

I didnt get the time to write the SQL View for it for testing, but
maybe at a later time. Need to stay on budget.

Thanks.