Programatically add time to existing date

[BLFOSTER]

I am having Access enter a "check-in" date based on a planned start date entered by user. The check-in date is 30 days after the planned start date and on different days of the week and at different times depending on the location of the check-in.

This is on the before update event of a form. The function returns a Tuesday, which is correct, and the date returned is correct. I cannot figure out how to get the time in!

If Me.Referring_Branch = "East" Then
Me.CheckIn = dhSecondDayInWeek([Activity - Planned Start] + 30 ????????? - 3:00:00 PM
End If



Thanks for any help you can provide!

[June7]

Concatenate.

Me.CheckIn = dhSecondDayInWeek([Activity - Planned Start] + 30) & " 3:00:00 PM"

Shouldn't need but if that has issue, maybe use date conversion function.

Me.CheckIn = CDate(dhSecondDayInWeek([Activity - Planned Start] + 30) & " 3:00:00 PM")

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[BLFOSTER]

Thank you! Getting runtime error 13 with both of the above solutions......type mismatch. The format of the field 'checkin' is general date so I am not understanding why I am getting this error.

And yes, the hyphen in my field names has caused me some trouble for sure! The data came out of Quickbase and Quickbase assigns that type of name to everything (Table Name - Field Name).

[June7]

The CheckIn field in table is a date/time type?

[BLFOSTER]

Yes, the field in table is Date/Time with general date format

[June7]

Then I don't understand why the mismatch error.

Debug your code. Use breakpoint and step through code - does the constructed date value really look like a date? Use Debug.Print.

Otherwise, I would have to analyze your db.

[BLFOSTER]

I am thinking it is a formatting issue, though I have tried every format trick I know. The Activity - Planned Start is 1/12/2016 1:30:00 PM, so the CheckIn date is coming out like this - 2/11/2016 1:30:00 PM 3:00:00 PM - which really isn't valid for the field and really IS a type mismatch. I have been trying to re-format the Activity -Planned Start to vbShortDate within the Before Update event procedure, but I am not having any luck.......

[BLFOSTER]

I figured it out......see below. Thank you for all your help June7!

If Me.Referring_Branch = "East" Then
Me.CheckIn = dhSecondDayInWeek(FormatDateTime(Forms!Form21!Form 3New.Form.[Activity - Planned Start] + 30, vbShortDate) & " 3:00:00 PM")
End If