I have had a lot of problems trying to use a date to select a range of records from my database. Despite numerous tries at formatting the date to use in the SQL query, it has not worked, and the US date format has often selected records when I don't want them. For example when I ask for records after the 01/10/2018, the query returns everything after the 10th January 2018.
My solution is as follows:
I ask for the date I want to report from (NewMemberDate)
NewMemberDate = InputBox("Enter start of month to check for new members.", "New Members", "01/XX/2018")
'Then check to see if the UK month is less than 12
If Mid(NewMemberDate, 4, 2) < 13 Then
' convert the date to US date format
NewMemberDate = Mid(NewMemberDate, 4, 2) & "/" & Left(NewMemberDate, 3) & Right(NewMemberDate, 4)
End If
The NewMemberDate can then be passed into the SQL string by:
WHERE ([Members].[Commence] >= #' & NewMemberDate & '#)
If anyone knows of a more effective way of doing this then please let me know.
My solution is as follows:
I ask for the date I want to report from (NewMemberDate)
NewMemberDate = InputBox("Enter start of month to check for new members.", "New Members", "01/XX/2018")
'Then check to see if the UK month is less than 12
If Mid(NewMemberDate, 4, 2) < 13 Then
' convert the date to US date format
NewMemberDate = Mid(NewMemberDate, 4, 2) & "/" & Left(NewMemberDate, 3) & Right(NewMemberDate, 4)
End If
The NewMemberDate can then be passed into the SQL string by:
WHERE ([Members].[Commence] >= #' & NewMemberDate & '#)
If anyone knows of a more effective way of doing this then please let me know.