Received Date V.S Completion Date

[expresso_dl]

Access Newbie



I have been struggling to come up with a MS ACCESS final template to count the number of requests received and completed with Query. What I am trying to achieve with Access that to display a series of numbers under each field.

Received date = date()-1
Completion date =date()-1

e.g if received date is 20^th Nov, which could be completed in a future date of T+1, T+2, T+3....where a completion date is 20^th Nov, the requests might have been received on T-1, T-2, T-3. Therefore, in order to show the counts of both ‘Received date’ and Completion date’ while their dates cannot co-exist, can anyone please share a brilliant solution to over come? In order to show an entire picture of what I was assigned to do, I am also providing the entire list needs to go in to the same Access.

Recived Date	date()-1
Completion Date	date()-1
Over Due	due date (given field) < date()
Due Today	due date (given field) = date()
Due Later	due date (given field) > date()

The reason I am running this on Access instead of Excel is because I am processing for over 20 countries and the raw data can be fed through to Access with daily and timely update.

[ItsMe]

If you have a bound textbox named ReceivedDate and an unbound textbox named txtDue then you could use the following in your form's current event.

Dim dtReceived As Date
Dim dtNow As Date
Dim intDiff As Integer
dtReceived = Me.ReceivedDate.Value
dtNow = Date
intDiff = DateDiff("d", dtNow, dtReceived)
MsgBox intDiff
Select Case intDiff
Case Is < 0
Me.txtDue.Value = "Past Due " & intDiff & " Days"
Me.txtDue.ForeColor = 255
Case 0
Me.txtDue.Value = "Due Now!"
Me.txtDue.ForeColor = 255
Case 1 To 20
Me.txtDue.Value = "Due Later " & intDiff & " Days"
Me.txtDue.ForeColor = 0
Case Is > 20
Me.txtDue.Value = "Due Much Later " & intDiff & " Days"
Me.txtDue.ForeColor = 0
End Select

[expresso_dl]

this is brilliant. thank you