Forum Discussion
Update record via VBA fails
I agree that your description of the date manipulators is what i would expect, which is how it was programmed initially. The confusion was why it suddenly stopped processing as it had done successfully for many months.
The input data was always a full timestamp generated internally, and my update statement was designed to only update records added that day. Just flummoxed as to why it stopped processing.
That solution of mine was false as the same problem occurred today although the 'fix' had been done.
i found a further article that stated that the data comparisons are done using the american format, mm/dd/yy. The 11/11/21 format worked by coincidence as the american and the international formats are identical.
I have now converted both date fields into ' Double' (Cdbl(Now()) to eliminate formatting issues and the comparisons are now valid, all works again.
For reference here is the code that failed after the last Windows 11 update:
Dim datefrom As Date
datefrom = DateValue(Now())
'DoCmd.SetWarnings True
Me.COL_CALL.SetFocus
addr2value = StrConv(addr2value, 3)
fnamevalue = StrConv(fnamevalue, 3)
sqlquery = "Update TABLE_HRD_CONTACTS_V01" & _
" set COL_DXCC = '" & dxccvalue & "',COL_NAME= '" & fnamevalue & _
"',COL_QTH = '" & addr2value & "',COL_STATE = '" & statevalue & _
"',COL_GRIDSQUARE= '" & gridvalue & "',COL_QSL_VIA = '" & qslmgrvalue & _
"' where COL_CALL = '" & Me.COL_CALL.text & "' AND COL_TIME_ON > #" & datefrom & "#"
COL_TIME_ON is a full timestamp held in a local database in a 3rd party tool which loads the data initially. I am expanding this entry with extra data extracted from an XML file sourced from a web based database.
Removing the date comparison at the end allowed the update to take place, but it also updated other records for the same callsign which may not be valid. (I am a radio ham by the way)
Thanks for your input.
Here it's the fact that one day's data reveals a problem with a basic assumption. Date formats differ, as we know, and it's a problematic thing that Office defaults to US format when one is not anticipating that.
One solution is to explicitly format your dates into an unambiguous structure, e.g. "YYYY/MM/DD". That's going to be interpreted correctly in all cases (assuming of course that it is actually the correct date passed into it).
It's one manifestation of what I've termed "The Iron Law of Ambiguity" (irony intended).
Ambiguity is required in poetry, humor and politics. Ambiguity is anathema is relational database applications.
Where a "date" can be interpreted in two different ways, it is ambiguous and using it is risky.
In the US format "7/3/2021" is July 3rd, 2021, but in other formats it is March 7th, 2021. Ambiguity not resolved leads to bad data and bad decisions based on that bad data. Resolve it in an unambiguous way before relying on it.
- So true. The data was fine and unchanged, just the processing 'methodology'. By converting to it's base value ambiguity is eliminated.
I checked the data previously when it was working and the mm/dd format was still valid in the comparison although the date 'created' was earlier than now thus allowing later rows to update.
11/11 was the last as 12/11 translated US as 12/11 was in the future so failed.
I will nor assume with VBA that date comparisons will be accurate.
Cheers"I checked the data previously when it was working and the mm/dd format was still valid in the comparison although the date 'created' was earlier than now thus allowing later rows to update."
This is the problem I was anticipating. You wouldn't necessarily even know that something had gone wrong because there are no error messages and you might not (probably wouldn't) be looking at those other rows and therefore not notice the update.
After 25+ years of working with Access and participating in forums like this, I've come to the conclusion that ambiguity is the biggest single factor in "anomalous" results.
The good news is two-fold. Problem solved. Deeper insight into the inner workings of Access.