Hello,

Please help me with this problem, its urgent

I have a table with Start date and End date for each record and other fields.
Have a form with Start and End date as text field, the problem is when the user enter these dates i to have calculate which date have more occurences between given start and end date.

example table :
Start End
01.09.2008 02.09.2008
02.09.2008 04.09.2008
03.09.2008 04.09.2008
04.09.2008 04.09.2008

example of form which user enters start and end:
Start - 01.09.08 End - 04.09.09

the answer should be " Peek Processed Date : 04.09.2008 " and if possible Day too.

thanks..

Any one please

If your definition of occurrences is the quantity of days in between the start and end then ....

Me.ControlNameofOccurrences = DateDiff("d", Me.ControlNameofStartDate, _
Me.ControlNameofEndDateDate) & ", " & Format(Me.ControlNameofEndDateDate"dddd")




-dK

Hello Dkinley,

Thanks for your reply. But i need the count how many times each date appears between two given dates(by user in form) from the table (start and end fields)

Oh, I think I get it now.

Like count the number of entries in the table for that specific run?

-dK

Hi, Thanks for reply.

I have attached a sample db of my actual one with a table and form.
Table_PD has Start and End date.
In form User will enter Sart and End date.

Now i have to calculate in which date we have processed max customer

if user enters 01.09.08 till 12.09.08. then i have to calculate which of the dates between 01.09.08 till 12.09.08 appeared most time in the table.

if you have any further requirements please let me know
thanks

I am not sure if this is what you want or not because I am somewhat confused by the logic of what you are asking for.

I would think that it would look for falling dates in another table, not this particular table because the count of dates will change everytime you enter a new record in this table. I say that because this table looks like the table at the top.

What this does is look at all the dates in the start and the end and counts them up if they fall between the start and the end of the current record start and end. It then subtracts 1 so the current record is not included.

Example, if on the current record, if start = 1/1/2008 and end = 12/31/08 then it will count up the number of records who has a start date or end date in this range. At least,I think it should. I didn't test it thoroughly because I wasn't sure if this is what you wanted or not.

Anyhow, if this is not it, let me know - will do what I can.

-dK

Can I just clarify what you want. Please post If I have got it wrong.

You want to display the date that is used most often between the start and end date on your form. Is this correct?

How should the records with no end date be handled?

Thanks for reply guys,

yes i want to count which is most occured date, between the Start and End date given by the user.
As an example i have posted a db ..
when you open the form "Formular_Report", enter start date and End date and press click. what i need is it should populate the peek date, which mean the the date that has occured many times.

when you click the button it activated the Query1 there you can see the only records show which satisfies the user given start and end date from the table Table_PD.

the problem here is .. in Table_PD the Start and End dates are not same and sometimes there is no end date (for this take the present date as end date only for calculation) so i have to consider the dates that fall between these dates in order to find the peek date.

suppose when i give the 01.09.08 till 12.09.08 in the form it show there are 5 records so i have calculate from these 5 records(include the dates that fall between each records start and end date) which date has occured many time which is the peek date to be shown on form.

(include the dates that fall between each records start and end date)

This is a key statement, because it means that you need to check for start dates before the first date if
a there is no end date
b the enddate is between the dates
c the enddate is after the second date

Basically it comes down to this
No end date or enddate after 2nd date then is start date between the dates or before the 1st date

or
enddate is between the dates then is start date between the dates or before the 1st date.


Brian

Hello Brian,

Thanks for your reply..

I have posted a sample DB as attachement, where you can see that the start date is always there but sometimes there is open end date in the table.

when you enter the start and end date in the form and press click, then imediately open the query1 where you can see all the records which fall between given dates. from these table i have to find out which date has occured most and if there is no end date the consider the present date as end date. this would be the peek date ... which i need

for your reference here is the db in attachement

thanks ..

Any one help ...

I looked at your 1st attachment and all of the rows contained start and end dates that were equal, which conflicted with statements made in the posts, however you have not commented on my observations.

You must respond to people before you can expect them to put in much time and effort, and if I am correct this will be no easy coding job.

And what happens if more than 1 date is max number?

Brian

Hello Brian,

Sorry for the delay replys, As i had read your post i made some changes to my DB ( reposted ) as according to my previous statements.

Yes i know it will be a serious coding as iam trying to do it.

If we have two Max dates then i have to show both.

Thanks Brian for your replys...

I think i solved my Problem..

Thanks for all your concern, here is the code for refernece ( this may not be the best but still working for me )

Public Function WorkDays1(ByVal dtBegin As Date, ByVal dtEnd As Date, Num As Integer) As Variant
' dtBegin = Start date entered by user dtEnd is End date and Num is number of records within this criteria ( start and end dates )

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strInfo As String
Dim stdocquery As String
Dim myarray() As Variant
Dim countarray() As Variant
Dim st, ed, count, i, j, k, l, m, n, max, countd1, countd2 As Variant

i = DateDiff("d", [txtFirstDate], [txtSecondDate]) + 1
Const conJetDate = "\#mm\/dd\/yyyy\#"
stdocquery = "sql query "
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(stdocquery, dbOpenForwardOnly, dbReadOnly)
myarray() = rst.GetRows(Num) ' Num is the number of records after the given start and end date
For m = 0 To i
If (dtBegin <= dtEnd) Then
count = 0
For j = 0 To Num - 1
st = myarray(0, j)
ed = myarray(1, j)
If (ed = "") Then
ed = Date
Else
End If
k = DateDiff("d", st, ed) + 1
For l = 0 To k - 1
If (st = dtBegin And st <= ed) Then
count = count + 1
countd1 = st
Else
End If
st = st + 1
Next l
If (max < count) Then
max = count
countd2 = countd1
Else
End If
Next j
dtBegin = dtBegin + 1
Else
End If
Next m
WorkDays1 = countd2
Text88 = WeekdayName(Weekday(countd2)) ' gives the day name