Report / Query : Multiple Dates

[Irfan]

Hi All,

I am trying to create a report but having problem with the critiera
selection logic, please help.

I have the following fields

date1
date2
date3
date4
date5

When the supplier acknowlegee and order the intial supplier order ready date
goes into date1 and balance 4 dates remians blank

Later If the order is late or brought forward the date2 colum is filled in

Later if the order is further late the date3 colum is filled in and date4
and date5 remains blank.

At the end hte order gets shipped.

What I am trying to do is generate a report which check the current date vs
the date1 (if date2 to date5 are blank)

or Date2 (assuming date2 is filled in)

Or date3 if 3rd date is entered

and genrate a report of order which were due to ship today or before today's
date.

Please help.

Regards, Irfan.

[Allen Browne]

If you just want to report any record that has any of the dates after today,
create a query, and in the Criteria row under Date1, enter:

Date() Then on the *next* criteria row under Date2 (i.e. the next line down, below
the first Critiera row), enter: Date() Repeat for the other date fields, using a separate criteria row for each
one.
In SQL View, the query will include this clause:
WHERE (([Date1] > Date())
OR ([Date2] > Date())
OR ([Date3] > Date())
OR ([Date4] > Date())
OR ([Date5] > Date()))

If you need to select which of several dates is the most recent one, grab
the MaxOfList() function from this page:
http://members.iinet.net.au/~allenbrowne/func-09.html
To use it type something like this into the Field row of your query:
MaxOfList([date1], [date2], [date3], [date4], [date5])
Then in the Criteria row under this, enter: Date()
The best solution might be to create a related table with fields for:
ID foreign key (which record this relates to)
DelayTypeID which kind of delay
DelayDate the now predicted delay.
Now you only have one date field to search to get the latest date. Typically
that would involve a subquery. More info:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Irfan" <ir**@emirates.net.ae> wrote in message
news:cb**************************@posting.google.c om... Hi All,

I am trying to create a report but having problem with the critiera
selection logic, please help.

I have the following fields

date1
date2
date3
date4
date5

When the supplier acknowlegee and order the intial supplier order ready
date
goes into date1 and balance 4 dates remians blank

Later If the order is late or brought forward the date2 colum is filled in

Later if the order is further late the date3 colum is filled in and date4
and date5 remains blank.

At the end hte order gets shipped.

What I am trying to do is generate a report which check the current date
vs
the date1 (if date2 to date5 are blank)

or Date2 (assuming date2 is filled in)

Or date3 if 3rd date is entered

and genrate a report of order which were due to ship today or before
today's
date.

Please help.

Regards, Irfan.

[PC Datasheet]

You need to redesign your table into a normalized form such as:
TblShipDate
ShipDateID
OrderID
DateShipDateChanged
ShipDate

Then all you need is a totals query that finds the max ShipDate for an
OrderID. Or if you want to know how many times it has been chnaged, you need
a totals query that finds the Count of ShipDates for an OederID.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Irfan" <ir**@emirates.net.ae> wrote in message
news:cb**************************@posting.google.c om...

Hi All,

I am trying to create a report but having problem with the critiera
selection logic, please help.

I have the following fields

date1
date2
date3
date4
date5

When the supplier acknowlegee and order the intial supplier order ready date goes into date1 and balance 4 dates remians blank

Later If the order is late or brought forward the date2 colum is filled in

Later if the order is further late the date3 colum is filled in and date4
and date5 remains blank.

At the end hte order gets shipped.

What I am trying to do is generate a report which check the current date vs the date1 (if date2 to date5 are blank)

or Date2 (assuming date2 is filled in)

Or date3 if 3rd date is entered

and genrate a report of order which were due to ship today or before today's date.

Please help.

Regards, Irfan.

[David Seeto via AccessMonster.com]

Interesting table structure - what happens if an order is delayed more than 4 times? Will you just overrwite the value in date5? You'd probably have been better off with a structure like:

[Date/Time of Estimation], [Estimated Date of Delivery]

that you could then just add records to as required.

Be that as it may, in answer to your specific question, you are going to need to set up a query with a column that looks like this:

LatestDate: iif([Date5] Is Null, iif([Date4] Is Null, iif([Date3] Is Null, iif([Date2 Is Null], [Date1], [Date2]), [Date3]), [Date4]), [Date5])

You can then use this query to select records where [LatestDate] <= Now, although I can never remember whether you need to explicitly strip out the Time bits of Now to turn it into a Date/Time as at midnight...

--
Message posted via http://www.accessmonster.com

[Irfan ullah]

Hi, Thanks all for a quick response, at this stage I am using David
Seeto's purposed function of latest date and it is working fine execpt a
small problem.

If I have inputted only first date, the latest date shows blank.

I have listed the code below

LatestDate: IIf([5thDelivery] Is Null,IIf([4thDelivery] Is
Null,IIf([3rdDelivery] Is Null,IIf([EHOUPDelDate Is
Null],[EHODelDate],[EHOUPDelDate]),[3rdDelivery]),[4thDelivery]),[5thDel
ivery])

Please help.

Thanks, Irfan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Seeto via AccessMonster.com]

>If I have inputted only first date, the latest date shows blank.

...
LatestDate: IIf([5thDelivery] Is Null,IIf([4thDelivery] Is Null,
IIf([3rdDelivery] Is Null,IIf([EHOUPDelDate Is Null],[EHODelDate],
[EHOUPDelDate]),[3rdDelivery]),[4thDelivery]),[5thDelivery])

Odd - I didn't see those other replies to your question when I first posted... maybe it's because I read these forums through AccessMonster.com?

Anyway, I can't see anything wrong with your formula, assuming that you date fields are:
1. EHODelDate
2. EHOUPDelDate
3. 3rdDelivery
4. 4thDelivery
5. 5thDelivery

Can you post a few records from your table, including some that work and some that don't?

--
Message posted via http://www.accessmonster.com