Enter required date based on text field 3

[rookiedev]

I have a form that automatically enters the current date. I also have a field that is a list box that the user inputs a value: E=Emergency-complete in 24 hrs
U=Urgent-complete in 5 days
R=Routine-complete in 15 days
I need to automatically populate a field called:ReguiredDate based on the entry made in the (field name)-"EURD" field. If the user selects "E" I need the required date to add 24 hours to the DateReceived field and display that date in the RequiredDate field. If the user selects "U" I need it to add 5 days to the DateReceived field and display the date 5 days from that in the DateRequired field and so on...
I know an IFF statement is necessary but I'm not sure how to go about writing it or where to put it.

I am using Access 97

Thanks in advance to anyone that can help!

RookieDev

 

[pdldavis]

Hi,

Rather than using an 'iif' statement, you might try a 'select case' instead.
Go to design view and right click on your list box properties.
Double click in the 'After Update' row, which should select 'Event Procedure'
Click on the three dots at the end of the row, which displays the code builder and shows something like this:

Private Sub YourListBox_AfterUpdate()

End Sub

Insert the following under 'after update'

Select Case Me.YourListBox
Case Is = "E"
Me.ReguiredDate = Me.txtDate + 1 'Date +1
Case Is = "U"
Me.ReguiredDate = Me.txtDate + 5 'Date +5
Case Is = "R"
Me.ReguiredDate = Me.txtDate + 15 'Date + 15
End Select

You mentioned that the form automatically enters the current date. Bear in mind that if this date is not saved,
tomorrow your RequiredDate is going to advance by a day.

Hope that helps.

 

[rookiedev]

Okay this is what I tried to put on the After Update of my List box:

Private Sub EURD_AfterUpdate()
Select Case Me.EURD
Case Is = "E"
Me.RequiredDate = Me.txtDate + 1 'Date +1
Case Is = "U"
Me.RequiredDate = Me.txtDate + 5 'Date +5
Case Is = "R"
Me.RequiredDate = Me.txtDate + 15 'Date + 15
End Select
End Sub

I keep getting this error message though: Compile error-Method or data member not found. I even tried to replace Me.txtDate with the field name that automatically enters todays date like this: Me.DateReceived and it still won't work. Did I do something wrong? I assumed that I was to replace Your List box with the name of my list box,correct??

I think I'm making this harder than it has to be!

RookieDev

 

[MazeWorX]

check the names of your controls(list box, text boxes) also check the bound column in the list box ..the code should work fine Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[rookiedev]

YOU ARE SO RIGHT! MY FIELD NAME IS DATE RECEIVED! THANKS SO MUCH!THAT WAS A WONDERFUL BIT OF CODE! IT WILL SPEED UP MY DATA ENTRY A BIT MORE! THIS STAR'S FOR YOU!

ROOKIE DEV

 

[MazeWorX]

Actually pdldavis should have the star (-: Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[rookiedev]

Once again you are correct. Let me ask another question. I can get away with this calculation the way it is but the number of days before a routine or "R" is due is actually 15 workdays. I realize this is a whole nother ball of wax because I have researched the threads but is there any less complicated way to have it skip holidays and not include weekends?

Thread702-365428 was six pages long and dealt with functions. As my name would denote I am a rookie at this and even if I added the basSchedDate function in I have no idea how to call it in this instance. Any suggestions?

Much thanks!
RookieDev

 

[chrissie1]

I think you got to the point where you have to learn new things.
but its quite easy you just copy the code from the thread and then change your code like this

Private Sub EURD_AfterUpdate()
Select Case Me.EURD
Case Is = "E"
Me.RequiredDate = basSchedDate(Me.txtDate,1)'Date +1
Case Is = "U"
Me.RequiredDate = basSchedDate(Me.txtDate,5) 'Date +5
Case Is = "R"
Me.RequiredDate = basSchedDate(Me.txtDate,15) 'Date + 15
End Select
End Sub
"What a wonderfull world" - Louis armstrong

 

[rookiedev]

Thanks Chrissie1! You are right every day now I need to learn something new. I tried to set my event procedure up with the Mod but upon closer review found that it is to calculate the number of work dates between a start date and end date therefor I can't use it here. I created the Holidays table and then this Function

Option Compare Database
Option Explicit

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As Database
Dim rstHolidays As Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date&quot

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (WeekDay(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function

As you see I only need to calculate 15 working days from the DateReceived.If you know of the location of some code that I can use to simply add the days please let me know.
The only value that is affected is the "R" value. the emergency and urgent are 24 hours a day 7 days a week.

Thanks,
RookieDev

 

[pdldavis]

Hi, I've done a low-tech solution to what you are describing. Don't have it with me though.

Basically, I ran a years worth of dates down a column in an Excel spreadhseet. There was one other column that was a Yes / No field. I set the dates to yes or no.

The spreadsheet was imported to a table (you can link as well). You can then query the table for a date range and and count the number of days within that range that match your criteria.

 

[rookiedev]

So you marked the Date "yes" if you wanted it to be counted as a work day? How did you write the code to go to that spreadsheet and check the list? Sounds interesting.

This method will just get me by for right now and I thank everyone that has given thier input! To do this correctly though I do need the DateRequired value to be 15 workdays, not just 15 days.If I run a report that tells me how many days it took to complete a routine job it looks like we don't meet the requirement of within 15 days most of the time and we actually do.

If the DateRequired was correct it would help me a great deal if an Audit is done. I would love to hear more about this low-tech solution.

Much thanks!
Rookiedev