My table has a Date field format Short Date. Record is added to the table without a date.
SQL is run to update the null date records with a prompt in the query of [Enter the date].
I am in Australia, and the date format is dd/mm/yyyy.
When using VB to develop the sql using Input Box to offer a default date, this works, but the date loaded against the record is in US format of mm/dd/yyyy.
Example, it is 6th December.
I enter 6/12/2014 with the SQL prompt query. Access posts it as 6/12/2014 which in long date is Dec 6 (correct)
Using VB, Access offers 6/12/2014, but Access posts it as 12/6/2014, which in long date is June 6 (incorrect).
The question is really using VB, Access turns the around to US format.
Dim Message, Title, Default, MyDate, sql
Message = "Enter the date (yesterday's is given)"
Title = "Date" '
Default = Format(Date - 1, "dd/mm/yyyy")
MyDate = InputBox(Message, Title, Default, 100, 100)
sql = "UPDATE Results SET Results.MeetingDate = #" & MyDate & "#, Results.Course = [enter name of race course] WHERE (((Results.MeetingDate) Is Null) AND ((Results.Course) Is Null));"
Debug.Print sql
DoCmd.RunSQL sql