auto update txtbox with end of the month date

[gstylianou]

Hello to everyone
I have a date field named "date1" and one undound textbox named "txt1".

I need each time when i change the value (dates) on "date1" field the txtbox "txt1" to take automatically the end of the current month date.

Ex.
Date1 value is 22/09/14 the txt1 should be 31/09/14

Is that possible to be


Thanks in advanced

[June7]

Expression in query or textbox ControlSource. Review http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

[gstylianou]

Hi June7,

Thank you very much for information. i will have a look on this.

Thanks again

[tonez90]

Try these:
'================================================= ===============================================
Public Function ReturnLastDateOfMonth()
ReturnLastDateOfMonth = Format(DateSerial(Year(Date), Month(Date) + 1, 0), "dd-mmm-yyyy")
End Function
'================================================= ===============================================
Public Function LastDateOfMonth(olddate As Date) As Date
LastDateOfMonth = Format(DateSerial(Year(olddate), Month(olddate) + 1, 0), "dd-mmm-yyyy")
End Function
'================================================= ===============================================

Simply use the on change, before update or after update part on "date1" (choice is yours depending on what you want to do). Dont forget to add some error checking on your date (usually using before update)

Regards
Tonez90

[gstylianou]

tonez90 Good Morning from Cyprus,

Thanks for your help. I will try your options and i will let you know later.

Thanks again

[tonez90]

How did you go with the routines. Here is another few routines I use for date checking etc.
Note these are ones that I have picked up over the years and modified for my use. They work well for my purposes.
Enjoy
Tonez90


Public Function IsDateFormat(varDate As Variant, Optional WithMessage As Boolean = False) As Integer
'================================================= ===================
'Graham R Seach gseach@accessmvp.com 06/04/2006
'================================================= ===================
'Verifies that the supplied value is in a valid date format
'================================================= ===================
Dim objRegEx As Object

If Len(Trim("" & varDate)) = 0 Then varDate = 0
If IsDate(varDate) Then
If CDbl(CDate(varDate)) <> 0 Then
If Len(Trim("" & Nz(varDate, ""))) > 0 Then
Set objRegEx = CreateObject("VBScript.RegExp")

'For 2 and 4-digit years
objRegEx.Pattern = "^(0{0,1}[1-9]|[12][0-9]|3[01])[-/](0{0,1}[1-9]|1[012])[-/](\d{2}|\d{4})$"
'For 4-digit years only
'objRegEx.Pattern = "^(0{0,1}[1-9]|[12][0-9]|3[01])[-/](0{0,1}[1-9]|1[012])[-/](\d{4})$"
objRegEx.Global = True
IsDateFormat = objRegEx.Test(CStr(varDate))

Set objRegEx = Nothing
Else
IsDateFormat = 1 'No date supplied
End If
Else
IsDateFormat = 1 'No date supplied
End If
Else
IsDateFormat = 2 'Wrong format
End If

If (WithMessage = True) And (IsDateFormat > 0) Then
DoCmd.Beep
MsgBox "You must supply a valid date in the format 'dd/mm/yyyy'," & _
vbCrLf & "using dashes (-) or slashes (/) only as separators." & _
vbCrLf & vbCrLf & "The day and month may be 1 or 2 digits, and the year may" & _
vbCrLf & "be 2 or 4 digits." & _
vbCrLf & vbCrLf & "Examples: " & Format(Date, "d/m/yyyy") & ", " & _
Format(Date, "d-m-yyyy") & ", " & _
Format(Date, "d/m/yyyy") & ", " & _
Format(Date, "d-m-yyyy"), _
vbOKOnly + vbExclamation, "Invalid date format"
End If
End Function
'================================================= ===========
'================================================= ===============================================
Public Function GetDTS(dteMyDate As Date, Optional dte2 As Date, Optional MyInt As Integer) As DTS
'*******************************************
'Purpose: Returns common date calculations
'Coded by: raskew
'Inputs: from debug window:
' (1) ? getDTS(#6/5/03#).FirstDayOfPreviousQuarter
' (2) ? getDTS(#6/6/03#).NextWorkDay
' (3) ? getDTS(#4/25/03#,, 2).WeekStartDate
'
'Output: (1) 1/1/2003
' (2) 6/9/2003
' (3) 4/21/03
'
'NOTE: Inspiration for this came from
' Allen Browne's site.
'*******************************************
With GetDTS
'DAYS
.NextWorkDay = [dteMyDate] + IIf(Weekday([dteMyDate]) > 5, 9 - Weekday([dteMyDate]), 1)
.PreviousWorkDay = [dteMyDate] - IIf(Weekday([dteMyDate]) < 3, 1 + Weekday([dteMyDate]), 1)
.IsWeekDay = IIf(Weekday([dteMyDate], 1) > 6 Or Weekday([dteMyDate], 1) = 1, False, True)
.NextNDay = [dteMyDate] - Weekday([dteMyDate]) + MyInt + IIf(Weekday([dteMyDate]) >= MyInt, 7, 0)
.LastNDay = [dteMyDate] - (Weekday([dteMyDate]) + IIf(Weekday([dteMyDate]) <= MyInt, 7, 0) - MyInt)
.WeekStartDate = IIf(Weekday(dteMyDate) = MyInt, dteMyDate, dteMyDate - (Weekday(dteMyDate) + IIf(Weekday(dteMyDate) <= MyInt, 7, 0) - MyInt))
.DayOfYear = val(Format(dteMyDate - DateSerial(Year(dteMyDate) - 1, 12, 31), "000"))
.DateDiffDays = DateDiff("d", dteMyDate, dte2) + (Hour(dteMyDate) > Hour(dte2))
.DateDiffHours = DateDiff("h", dteMyDate, dte2) + (Minute(dteMyDate) > Minute(dte2))
.DateDiffMins = DateDiff("n", dteMyDate, dte2) + (Second(dteMyDate) > Second(dte2))
.DateDiffSecs = DateDiff("s", dteMyDate, dte2)
.FindSaturday = FormatDateTime(dteMyDate + (7 - Weekday(dteMyDate)))

'WEEKS
.FirstDayOfWeek = dteMyDate - Weekday(dteMyDate, vbMonday) + 1
.LastDayOfWeek = dteMyDate + (7 - Weekday(dteMyDate, vbMonday))
.FirstdayofNextWeek = dteMyDate + (7 - Weekday(dteMyDate, vbMonday)) + 1
.LastdayofNextWeek = dteMyDate + (7 - Weekday(dteMyDate, vbMonday)) + 7
.FirstdayofPreviousWeek = (dteMyDate - Weekday(dteMyDate, vbMonday) + 1) - 7
.LastdayofPreviousWeek = dteMyDate - Weekday(dteMyDate, vbMonday)
.NumberOfWeeks = DatePart("ww", dteMyDate)
'MONTHS
.FirstDayOfMonth = DateSerial(Year([dteMyDate]), Month([dteMyDate]), 1)
.LastDayOfMonth = DateSerial(Year([dteMyDate]), Month([dteMyDate]) + 1, 0)
.FirstDayOfPreviousMonth = DateSerial(Year([dteMyDate]), Month([dteMyDate]) - 1, 1)
.LastDayOfPreviousMonth = DateSerial(Year([dteMyDate]), Month([dteMyDate]), 0)
.FirstDayOfNextMonth = DateSerial(Year([dteMyDate]), Month([dteMyDate]) + 1, 1)
.LastDayOfNextMonth = DateSerial(Year([dteMyDate]), Month([dteMyDate]) + 2, 0)
.DateDiffMonths = DateDiff("m", dteMyDate, dte2) + (Day(dteMyDate) > Day(dte2))

'QUARTERS
.FirstDayOfQuarter = DateSerial(Year([dteMyDate]), 3 * Int(((Month([dteMyDate])) - 1) / 3) + 1, 1)
.LastDayOfQuarter = DateSerial(Year([dteMyDate]), 3 * Int((Month([dteMyDate]) - 1) / 3) + 4, 0)
.FirstDayOfPreviousQuarter = DateSerial(Year([dteMyDate]), 3 * Int(((Month([dteMyDate])) - 1) / 3) - 2, 1)
.LastDayOfPreviousQuarter = DateSerial(Year([dteMyDate]), 3 * Int(((Month([dteMyDate])) - 1) / 3) + 1, 1) - 1
.FirstDayOfNextQuarter = DateSerial(Year([dteMyDate]), 3 * Int(((Month([dteMyDate])) - 1) / 3) + 4, 1)
.LastDayOfNextQuarter = DateSerial(Year([dteMyDate]), 3 * Int(((Month([dteMyDate])) - 1) / 3) + 7, 1) - 1

'YEARS
.FirstDayOfCurrentYear = DateSerial(Year([dteMyDate]), 1, 1)
.LastDayOfCurrentYear = DateSerial(Year([dteMyDate]), 12, 31)
.FirstDayOfPreviousYear = DateSerial(Year([dteMyDate]) - 1, 1, 1)
.LastDayOfPreviousYear = DateSerial(Year([dteMyDate]) - 1, 12, 31)
.FirstDayOfNextYear = DateSerial(Year([dteMyDate]) + 1, 1, 1)
.LastDayOfNextYear = DateSerial(Year([dteMyDate]) + 1, 12, 31)
.IsLeapYear = IIf(Year([dteMyDate]) Mod 100 = 0, IIf(Year([dteMyDate]) Mod 400 = 0, True, False), IIf(Year([dteMyDate]) Mod 4 = 0, True, False))
.DateDiffYears = DateDiff("yyyy", dteMyDate, dte2) + (dte2 < DateSerial(Year(dte2), Month(dteMyDate), Day(dteMyDate)))

End With
End Function

'================================================= ============