Question re. coding for dates in a form 2

[spherey]

Hi everyone,

I have a series of check boxes all linked to date fields in a form. The check boxes have an 'after update' event that automatically places the date in the date field when the box is checked. There are also a second set of date fields which are dependent upon the first set - they're supposed to show a date two weeks after the date in the first field. And that's the part I'm stuck on. In the check boxes' 'after update' event procedures, I have this code:

If [check box] Then
[Datefield] = Date
Else
[Datefield] = Null
End If

and that works great. I tried to build the second code off the first, but so far, although I'm not getting any errors, I'm not getting my dates, either. For the second code, I wrote this in the 'after update' event procedure for the field named 'datefield' in the above example:

If [datefield] Then
[Date of next action] = Date + 14
Else
[Date of next action] = Null
End If

And that didn't work. The other bit I tried, with precisely the same results, was this:

If [datefield] = Date Then
[Date of next action] = Date + 14
Else
[Date of next action] = Null
End If

Can anyone spot what I'm doing wrong, and/or have any insights into a bit of code that would put into a field a date 14 days after the date entered into another field?

Thanks,

Spherey

 

[jdegeorge]

Is [datefield] and [Date of next action] set to DATE/TIME in your table or are they text fields? If not, they need to be. If they are, maybe your code doesn't work.

Also, Michael Red posted this code about finding business days between 2 dates:

Public Function basMtgDate(Optional NumDays As Integer = 1, Optional startdate As Variant) As Date

'Michael Red 6/27/2002
'Return the Next Business Day (Date) Available
'The Optional NumDays defaults to 1 (Next Buss. Day), but can be set to any value
'The optional StartDate defaults to the Current (System) DAta, but also may be set by the user
'Requires a table (tblHolidays) with a Date field "HoliDate" as Date

'Sample usage
'? basMtgDate (PC Date = 6/27/02)
'6/28/02 (6/28/02 is a Friday)

'? BasMtgDate(1, #7/3/02#)
'7/5/02 (7/3/02 is a Wednesday, 7/4/02 is a HoliDate, 7/5/02 is a Friday)

'? BasMtgDate(1, #6/29/02#)
'7/1/02 (6/29/02 is a Friday, 7/1/02 is Monday)

'? BasMtgDate(7)
'7/9/02 (PC Date is Thursday, 6/27/02.
' 6/29, 6/30, 7/6 & 7/7 are Weekends
' 7/4 is a holiday.


Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset

Dim MyDate As Date
Dim MyDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

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

NumSgn = Chr(35)

If (IsMissing(startdate)) Then
startdate = Date
End If

MyDate = Format(startdate + 1, "Short Date&quot

Do While MyDays < NumDays
Select Case (WeekDay(MyDate))
Case Is = vbSunday 'Sunday
'Do Nothing, it is NOT a Workday

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

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

End Select

If (MyDays >= NumDays) Then
Exit Do
End If

MyDate = DateAdd(&quot;d&quot;, 1, MyDate)

Loop

basMtgDate = MyDate

End Function

You need a table called tblHolidays with the following fields: Date (Date/Time), Holiday (Text), and ID (AutoNum).

This is posted somewhere here in a FAQ but I can't find it right now.
Hope this helps.

Jim DeGeorge

 

[Eupher]

Access sees a date as a complete date/time, so just adding 14 doesn't work in this context. Access doesn't know which part of the date you're adding to. It's probably adding 14 milliseconds to your date, which obviously won't display if you have your date fields formatted to just display the date. Try the &quot;DateAdd&quot; function, i.e.:

If [check box] Then
     [Datefield] = Date
     [Date of next action] = DateAdd("d", 14, [Datefield])
     Else
          [Datefield] = Null
          [Date of next action] = Null
End If

Ken S.

 

[spherey]

Jim and Ken,

Thanks so much for your help!

Jim, the fields in the table were set to Date/Time, and I changed them. It still didn't work, but that was because I was using the wrong code, like I thought -

Ken, that was the right code. Once I plugged that in instead, everything did exactly what I wanted it to do. You're right, adding 14 milliseconds to the date isn't especially useful!

Thanks again,

Spherey

 

[Eupher]

spherey, I was all wet about what I said earlier. I should test a little more thoroughly before I open my mouth. The Date function actually does return a date, not a date/time, and Date + 14 should indeed give you the date you want. Looks like the mistake you made wasn't the code, it was *where* you put the code. Writing a value to a field with VBA doesn't trigger the AfterUpdate event for that field, so your Date + 14 code was never running. Date + 14 should work just fine as long as you put it in the After Update event of the checkbox:

If [check box] Then
     [Datefield] = Date
     [Date of next action] = Date + 14
     Else
          [Datefield] = Null
          [Date of next action] = Null
End If

There are times when manipulating date/time values you *will* need to use the DateAdd function to get the results you want, but apparently simply adding an integer to a date value defaults to adding days. Sorry for any confusion, and glad you got your code working.

Ken S.

 

[spherey]

Ken -

You didn't add any confusion - it was your suggestions, in fact, that got my code up and running! Jim suggested that I change the format in the underlying table to Text from Date/Time, and you suggested the change in code (and yeah, I saw from your example that in that case, I'd have to move it to the After Update event procedure for the check box). I didn't check to see if my original code would work there; I suspect not, as I'm using one field to trigger two others in that case. But in any event, with your changes, it's working perfectly. Thanks for following up, and thanks for your time and energy.

Spherey