Solved: dateserial format

[paddysheeran]

Hi All

i have the following code which should enter last months date into a message box for the user to confirm whether it is correct or not:

Sub DateInput()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As Date
Dim ReportDate As Date

Request = "Please enter the Reporting Month"
ReportTitle = "Ros Monthly Customer Service Report"
DefValue = DateSerial(Year(Now()), Month(Now()) - 1, Day(Now()))
ReportDate = Format(CDate(InputBox(Request, ReportTitle, DefValue)), "mmm/yyyy")
End Sub

When I run the code the message box is populated with the full date but I just want it in a mmm/yyyy format. Can anyone show me where im going wrong?

[MaximS]

try that:


Sub DateInput()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As String
Dim ReportDate As String

Request = "Please enter the Reporting Month"
ReportTitle = "Ros Monthly Customer Service Report"
DefValue = MonthName(Month(Date) - 1, True) & "/" & Year(Date)
ReportDate = InputBox(Request, ReportTitle, DefValue)
End Sub


main reason why your code doesn't respect format is declaring DefValue and ReportDate as Date (this will always try to convert it to dd/mm/yyyy format).

[Bob Phillips]

Sub DateInput()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As Date
Dim ReportDate As Date

Request = "Please enter the Reporting Month"
ReportTitle = "Ros Monthly Customer Service Report"
DefValue = Date - Day(Date)
ReportDate = CDate(InputBox(Request, ReportTitle, Format(DefValue, "mmm/yyyy")))
End Sub