Anyone having success with DateTimePicker Control and Access 2007?

[mdlueck]

Greetings,

I have bumped into the first occurrence of needing to work with user input date values in the application I am developing.

Having been warned about Access and date nonsense:
“International Dates in Access” \ “1. Misinterpretation in the User Interface”
http://allenbrowne.com/ser-36.html#Interface

I thought to find some sort of "helper control" to make date validation easier. I came across the Microsoft DateTimePicker ActiveX control that is found in the MSCOMCT2.OCX file.
http://msdn.microsoft.com/en-us/library/aa231249(v=vs.60).aspx

At first glance yesterday afternoon it looked bullet-proof enough. However today in further testing, cracks/limitations seem to be appearing.

I wanted to arrive at dates in USA MM/DD/YYYY format. So I have told that control I want to use format "3" (Custom) and filled in CustomFormat mask:

MM'/'dd'/'yyy

The control will not obey my two digit Month / Day request in the dates it displays in the control. As well, when I extract the value from the control and sent it to an unbound field control, the "0" padding characters for Month / Day are missing.

For formatting I switched to the other slash "\" char in the CustomFormat field and the control updates its view to use the other slash, however still returns the standard "/" character as the slash when reading the control's value.

So, I am thinking that all of the fancy formatting which is said to be possible with the control formats the display of the control only, and that reading the control's value returns a more standardized date string. (But then what about my two digit Month / Day spec?)

Finally, time keeps slipping back through the floorboard cracks. I keep whacking the time part in the Value property of the control, and it holds that way for a little while, and then time comes right back shortly. So on its own, the Value property is back to:

10/13/2011 9:43:52 AM

as I type this message. Clearly the time is not specified in my CustomFormat mask, and like I said the Format is set to "3" which should mean custom, based on CustomFormat.

Suggestions on how to avoid such aggravations? Thanks!

 

[mdlueck]

As an update on this issue... I closed the Form and Database, re-opened, and the behavior has changed.

Now the control respects the date format specification. I am seeing "0" padded dates in the control.

Also the Value Property also indicates the date only.

However that property will not respect the "0" padding characters specified in the CustomFormat. Sending the control's value to an unbound text control shows the same non "0" padded date.

I suppose I could do some date string manipulation. (shrug)

 

[mdlueck]

The SQL Server BE DB seems to want to store dates in YYYY-MM-DD format.

I issued direct SQL via the Management Studio to input dates in MM/DD/YYYY format and it correctly interpreted the dates and displays them in its YYYY-MM-DD format.

To avoid date math delimas would it be advisable to:
1) Use the DateTimePicker Control
2) In the FE DB store the dates in a String field, in YYYY-MM-DD format
3) The DateTimePicker Control seems to interpret in strings of YYYY-MM-DD format correctly
4) When reading the control to store in the BE DB, then interpret the M/D/YYYY format dates back into YYYY-MM-DD with VBA code and store that to the FE DB String column / ultimately to the SQL Server BE DB Date column. (So the Access / VBA string in YYYY-MM-DD format gets stored to SQL Server, and that datatype happens to be a SQL Server Date.)

Any gottchas with that scenario? TIA!

 

[mdlueck]

The above scenerio ALMOST works...

If I try to blank the date, I can trace the variables and a "" string is sent to the SQL Server stored procedure, which results in "1900-01-01" getting stored to the BE table.

Anyone know what I should do from Access / VBA in order to blank out a previously set SQL Server date field?

 

[mdlueck]

FYI - ADO objects seem to flip the date back to USA format (we are in the USA after all) so I abandoned thoughts to translate dates to the SQL Server format (YYYY-MM-DD) as passing through ADO objects would just flip it back anyway.

To correctly save NULL values, I must pass Null into the ADO Parameters object.

Also I must update the datatype the .Parameters.Refresh auto-guesses
to adDBTimeStamp and not adDate as I first guessed was the correct
datatype to manually update it to.

"INFO: "Optional Feature Not Implemented" Error Message"
http://support.microsoft.com/kb/214459

Thus...

    .Parameters("@ecoreleasedate").Type = adDBTimeStamp
    If Me.ecoreleasedate = "" Then
      .Parameters("@ecoreleasedate").Value = Null
    Else
      .Parameters("@ecoreleasedate").Value = Me.ecoreleasedate
    End If

Ffffeeewwww!!!! Glad that one is solved!

 

[gemma-the-husky]

i don't understand the issues about "date funnies"

just to confirm.

dates are STORED as real numbers.

The integer part of the number represents the number of days since a given date. The decimal part represents the time element fraction of the day. therefore a date value with no decimal portion is a date only, with no time element.

access provides stacks of functions to manipulate dates. Presentation of the date is up to the application.

the thing about SQl is that it tries to treat a text date as a US date - so 4/11/2010 will be treated as april 11th - if you are in a locale where dates are treated diferently (eg UK - where the date would be 4th November) then you have to be aware of this in order to manage the potential for error

i would try using explicit formats

format(somedate,"short date") = 04/11/10
format(somedate,"long date") = April 11 2010

or use char strings
format(somedate,"ddd mm/dd/yyyy") = Sun 04/11/2010

 

[mdlueck]

i don't understand the issues about "date funnies"

To quote again:

Having been warned about Access and date nonsense:
“International Dates in Access” \ “1. Misinterpretation in the User Interface”
http://allenbrowne.com/ser-36.html#Interface

I would like to avoid such nonsense, thus sought after some "training wheels" to prevent nonsense dates from getting entered into the software.

I came across the DatePicker suggestion which for the most part seems to be working well, (At least better than a free-form text field where someone could type in anything they desired to.)

 

[mdlueck]

One additioanl finding about the DateTimePicker control... how to preset it to today's date when it is in disbaled / NULL state:

  If ObjPartsTbl.ecoreleasedate = "" Then
    MePointer.fldecoreleasedate.Value = Date
    MePointer.fldecoreleasedate.Value = ""
  Else
    MePointer.fldecoreleasedate.Value = ObjPartsTbl.ecoreleasedate
  End If

Basically quickly set the control to today's date, then NULL it again. The Form opens to the control check box being unchecked and when checked it defaults to today's date.