brute force change dates because nothing else works (1 Viewer)

[joe789]

Hi Folks,

I am trying to slap a few on this issue rather than figuring it out, as I tried to figure it out for hours and hours on end and just need a bit of code to 'patch' it right now.

I have a huge Microsoft access database front-end tied to a massive sql server back end. Tons of fields, forms, relationships, queries, reports, macros, modules ... it is loaded with everything.

The problem is that for a certain set of dates on this form, for whatever reason, the format that is being displayed is YYYY-MM-DD. Changing the formatting and other options in the field properties does not help. In an attempt to figure out what is causing this, this is what I found:

If I link the SQL tables manually the aforementioned data fields are properly displayed as MM/DD/YYYY. However, if I use some tried and true code that has proven to work flawlessly over the years to link the SQL tables using the exact same account as the manual link, the fields are displayed as YYYY-MM-DD. Apparently, this is some deep and hidden issue here that I am not going to be able to fix because I tried everything and this is what is causing it. The code that links the tables does ONLY that and does not act like this in any other database ... I tried it all folks ... this seems to be a glitch with something that I am not going to be able to fix in my lifetime deep inside Microsoft software :banghead:

I am wondering if I can try to BRUTE FORCE these dates to MM/DD/YYYY via some code that can be ran in a module on the form open or on current. This would be my last ditch effort as again, changing anything in an attempt to get these dates to be in the format I need them to be will not work whatsoever for these particular dates ...

How would I attempt to override this in vba using brute force?

I tried the following to no avail:

Me.[DateField].Format, "MM/DD/YYYY" along with a few others and they don't render error or change the format.

Thank you very much for any help,

Joe

 

[mdlueck]

Yes, SQL Server uses YYYYMMDD date format. I have cooked up a helper function to convert from YYYYMMDD to MM/DD/YYYY, as follows:

Public Function datetimeutils_ConvStandardToUSA(ByVal strInputDate As String) As String
  On Error GoTo Err_datetimeutils_ConvStandardToUSA

  datetimeutils_ConvStandardToUSA = Format(strInputDate, "mm/dd/yyyy")

Exit_datetimeutils_ConvStandardToUSA:
  Exit Function

Err_datetimeutils_ConvStandardToUSA:
  Call errorhandler_MsgBox("Module: modshared_fsutils, Function: datetimeutils_ConvStandardToUSA()")
  datetimeutils_ConvStandardToUSA = vbNullString
  Resume Exit_datetimeutils_ConvStandardToUSA

End Function

So I download records into a FE temp table, then prior to displaying the records, use an adoRS object to whip through the FE temp table and flip the date format using the above function.

 

[Pat Hartman]

Internally, SQL Server stores dates as double precision numbers just as Jet/ACE do. Formatting a field defined as a date data type does NOT alter what is stored or how it is stored. If the field is stored as a string, then formatting does matter.

Are you saying that "SalesDate" is formatted as YYYY-MM-DD on one row and the row below has the field formatted as MM/DD/YYYY? Or are you saying that "SalesDate" is formatted as YYYY-MM-DD on FormA but as MM/DD/YYYY on FormB?

 

[joe789]

Hi Folks,

Basically what is happening is that if I manually created the link for the SQL Server tables in question, the dates on the forms are properly displayed as MM-DD-YYYY. However, if I via tried and true code that has worked for years automatically link the SQL Server tables in question, those same forms display the dates in question as YYYY-MM-DD. Now, when I automatically link the tables, nothing whatsoever can change the dates in the form back to MM-DD-YYYY ... not anything in the properties or anything else whatsoever. The only way I can go back to MM-DD-YYYY is to re-create the entire database and relink the SQL table manually. The code that links the tables is just that, it is very simple and has worked a million times before so I think this is a glitch buried deep inside between SQL and Access and the best I can hope for is brute force formatting if that is possible to try with this.

Thanks,

Joe

 

[joe789]

Folks,

What I am basically looking on trying to do is perhaps on the load current aspect of the form use something like this:

Format(Me.Needs_Assessed_by_Instrument___Employment__Date.value, "mm/dd/yyyy")


So that I attempt to alter the formatting, when all else failed, at the very last step when the form itself loads the record ... changing the formatting any other way, including on the form and field properties will not work, so I would like to try this as the last resort.

The above line for some reasons gives me an error, so I cannot try to see if it works ... something about a 'compile error - expected ='

Any help would be appreciated.

Thanks,

Joe

 

[joe789]

This seems to work:

Dim FormatEmploymentDate As Date
If IsNull(Me.Needs_Assessed_by_Instrument___Employment__Date) Then
Else
FormatEmploymentDate = Format([Needs_Assessed_by_Instrument___Employment__Date], "mm/dd/yyyy")
Me.Needs_Assessed_by_Instrument___Employment__Date = FormatEmploymentDate
End If

And it even maintains the masking on the form ... what it does not do thou is validation does not work at all ... I wonder if I can hard code VBA validation into the on current form as well since it won't work using form/field properties. >#1/1/2011# and <= Now() ... this works with all other date fields on the form except the ones that are affected by the above code that forces them to be MM-DD-YYYY instead of YYYY-MM-DD

Thanks

 

[Pat Hartman]

Are ALL dates affected?
ALL dates from one table?
ALL dates on ALL forms?
Some dates on Some forms?
One date on ALL forms?
Does the problem affect Tables? Queries? Reports?
Does the problem occur in a newly created database?
Are the date fields defined as date data types or strings?