Update record via VBA fails

[Charlie Cartmel]

Since the windows 11 and office updates 11/11/2021 my VBA application which extracts data from an external XML file has simply stopped working.
I have debugged the code and traced the variables and SQL creation and all is as was previously, data collected correctly, Update statement correct and when run no error is shown however the updates do not reach the table.
Manual updates are working OK
Out of ideas.
Incidentally I know the SQL is valid, and the update code has been working for months prior to the latest update from Microsoft.
I may well roll back the Office updates after perhaps a reinstall.

UPDATE: I have removed any relevant updates - no change
now rolled back (x2) to 3 days ago and still not working. Also took old access app from backup and not working.

UPDATE 2 SOLVED:
Somewhere along the line the Date function has changed and extraction of the date from the NOW() timestamp seems to include the time element even after extracting via DateValue command as worked perviously.
I now used "datefrom = DateValue(DateAdd("d", -1, Now()))" to modify the date so the date relevant update can now proceed as previous.

[#2]

@Charlie Cartmel 

Just to be sure I understand, can you post both the "before" and "after" versions of this expression?

DateValue() has ALWAYS extracted just the date from any date/time value.

Now() has ALWAYS returned the date and time.

If something changed, it's likely to be in your newest data sets, not in the VBA.  You are subtracting one day from the current date to get a result, and that makes me think one possibility is that the "date" field in your data is now including times when they didn't before.

That's why I'd be curious to see exactly what the code was and exactly what it is currently. Also how it is used in context.

Also, it would be helpful to examine those fields for the presence or absence of times with the dates.

[#3]

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.

[#4]

As I suggested, and as I always go to as a first check, when a process has worked properly for an extended period of time, and then stops working, look FIRST at the data. The answer is almost always in the data.

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.

[#5]

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

[#6]

@Charlie Cartmel 

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