Really - just how hard is this to code???

[DubCap01]

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...

        DoCmd.OpenQuery "QryTmpDateCreator1"     ' works fine

        DoCmd.RunSQL "UPDATE ICDOBCheck SET ICDOBCheck.DOBDate = CDate(Format([DOB],"00-00-0000"));"     ' fails

frustrated from Australia

[CJ_London]

why not use the dateserial function in an update query

ensure your dobdate field is a datetime

then the query would be something like

UPDATE myTable SET DOBDate= DateSerial(right(DOB,4), Mid(DOB,3,2), Left(DOB,2))

[ssanfu]

Aunt Mabel is on vacation, but I will take a swing at this.

I don't know why you don't just use a DateTime field instead of a text type field, but that is for you to know and I shouldn't really care.
The reason your SQL doesn't work is that the date is in Australia (European) format in a text field.
When you apply the format, the text value becomes "dd-mm-yyyy". But, when working with dates, you usually have to work in American format: "mm-dd-yyyy".

So, using "15012001", the format results in "15-01-2001". It should be "01-15-2001".
Since there is not a month of "15", "15-01-2001" in the CDate() conversion function causes the error.

Ajax suggests using the DateSerial function (I agree), which allows you to specify which is the year, month and day to create a valid date.

[CJ_London]

Been thinking about this a bit more and think we are answering the wrong question

I can then update field DOBDate to store the value "15/01/2001" as text

Just to get this out the way, to be clear, datetime fields are numbers, not text. It is a decimal number, the value to the left of the dp is the date counting up from 31/12/1899 (today is 42772). The value to the right is the time expressed as the number of seconds so far today divided by 86400 (the number of seconds in a day).

That is why, when you sort dates 14/01/2017 appears correctly after 15/01/2016 - if it was text, it would appear before the 15th.

So what you are trying to do is convert text to numbers. The number itself can be formatted to appear as pretty much any form of date style

As Steve says, the standard interpretation of a date is the US style - mm/dd/yyyy and this is what Access (and other db systems) will assume the format is when converting a string to a date. However it does have a bit of 'common sense' - if presented with 15-01-2017 it knows there are only 12 months in the year and will try and convert it on the basis of mm/dd/yyyy. However if the date is 12-01-2017, it will interpret this as 1st December - So anyone not using the US format has to convert the text to be in the correct format.

However, the CDate function will use the windows settings to determine the date format - so cdate("12-01-2001") should produce the right date. So I revisited your question and can see the problem

DoCmd.RunSQL "UPDATE ICDOBCheck SET ICDOBCheck.DOBDate = CDate(Format([DOB],"00-00-0000"));" ' fails


The bit in red is in double quotes - but you are already using double quotes to define the string, so you need to use single quotes

DoCmd.RunSQL "UPDATE ICDOBCheck SET ICDOBCheck.DOBDate = CDate(Format([DOB],'00-00-0000'));" ' fails

Just as tip for the future - when building queries that you expect to convert to VBA, use single quotes. I know access will default to double quotes, but get in the habit of using single ones.

[ssanfu]

@Ajax,
The "' fails" shouldn't be after the 2nd Docmd example (with single quotes)..... right??

[DubCap01]

Thanks Steve and Ajax, the single quotes around the text worked fine!
Problem solved

[DubCap01]

@Ajax,
The "' fails" shouldn't be after the 2nd Docmd example (with single quotes)..... right??

Steve, that's funny haha

[CJ_London]

Oops - just copy and pasted, forgot to remove it

[roaftech]

If I can submit my two (Euro)cents worth ...
I would prefer to assemble my SQL command into a variable ahead of the RunSQL command, eg

dim strSQL as string
strSQL = "SELECT whatever"
strSQL = strSQL & " FROM wherever"       'remember the space
strSQL = strSQL & " AS whoever"            'etc
'print debug strSQL
DoCmd RunSQL strSQL

I think that this makes for easier troubleshooting - what do you guys think?

Also, if you are confused by a string of single and double quotes then you can use CHR(34) as a third option.

Succes!

[CJ_London]

don't disagree with building the string first and as you say, you can then use debug.print to display in the immediate window and copy paste to the sql window to check syntax.

[ssanfu]

If I can submit my two (Euro)cents worth ...
I would prefer to assemble my SQL command into a variable ahead of the RunSQL command

I think that this makes for easier troubleshooting - what do you guys think?

Agreed. The only way I do it.. I always check the SQL string created...

[orange]

Using a variable and building the SQL and using Debug.Print strSQL is a very good practice.
Especially if you are running Action queries and Update the wrong data, or worse, Delete mass quantities unexpectedly.