Dear Aunt Mabel,
I am getting to my wit's end with this.
I have a table with no entries, and two fields - DOB [Short Text] and DOBDate [Short Text]
There are no relationships, no primary key. All I want to do is convert DOB (short Text) to DOBDate (Date/Time format)
Stage 1: In the non-VBA environment, I can append one record to the field DOB and the entry as text is "15012001" - via query - no problem - just as I expected.
Stage 2: I can then update field DOBDate to store the value "15/01/2001" as text - by running a query - no problem - just as I expected.
Stage 3: I then have to manually alter the table design, and change field DOBDate from Short Text to Date/Time, and hey presto the value is a valid date of 15 January 2001, displaying in table view as 15/01/2001 - just as I wanted in the first instance.
When I try and apply that in VBA it falls over on Stage 2, with a syntax error???? The code for the VBA routine is an exact copy of the SQL from the query. It just fails. Why???
Here's the two instances of coding for stage 2, the first instance using the query works, the second instance (copying the sql code from the query) doesn't...
Code:
DoCmd.OpenQuery "QryTmpDateCreator1" ' works fine
Code:
DoCmd.RunSQL "UPDATE ICDOBCheck SET ICDOBCheck.DOBDate = CDate(Format([DOB],"00-00-0000"));" ' fails
frustrated from Australia