Hi,

I am updating a table which each row presents a class that is held on a Tuesday/Thursday and intermittent Saturday's.  There are 60 rows in the table so the course runs over some 28 weeks.

I have created a form which has a unbound field where the first class date is entered.

I have a button which runs some VBA code to update the class date for each row in the table

To update the class date in the table I run a Docmd.RunSQL SQL with the following string which I have copied from my VBA Editor while trying to debug my error..

Watch :   : SQL : "UPDATE CourseScheduleT SET CourseScheduleT.UnitDate = 7/04/2022 WHERE (((CourseScheduleT.SequenceNumber)=1));" : String : Form_StartDateF

The String SQL in the code looks like this:

SQL : "UPDATE CourseScheduleT SET CourseScheduleT.UnitDate = " & "#" & [CurrentDateNw] & "#" & " WHERE (((CourseScheduleT.SequenceNumber)=" & TableRow & "));" :

Although you can see .UnitDate= 07/04/2022, what is written to the table is 30/12/1899.

It doesn't matter what date is in this sql string all rows have 30/12/1899.

I have also tried converting the date to a string and input the string to the the SQL string variable but for some reason if the day in the date is less than 12 the SQL converts the day to a month and the month into the day.

SQL : "UPDATE CourseScheduleT SET CourseScheduleT.UnitDate = " & "#" & "04/07/2022"  & "#" & " WHERE (((CourseScheduleT.SequenceNumber)=TableRow));" :


Eg 7th April 2022 should be written 04/07/2022 mm/dd/yyyy but is written as 07/04/2022.  Yet all days that are 13 through 31 are written correctly as 04/13/2022. mm/dd/yyyy

How do I run a Docmd.RunSQL SQL statement and have a date in a variable and have that date written to at the table correctly.

This is my 3rd MS Access project but the 1st where I am trying to run some VBA code which also run some SQL.

Please help

Ron de Boer