PDA

View Full Version : [SOLVED] Date format issues with date picker on form



mykal66
05-15-2014, 01:21 AM
Hey everyone

I have used a date picker provided by another user to try and combat format inconsitency when users add a date to a from and at first glance it works brialliantlly.

In testing however i found that depnding on the date that is selected by the user the date copied to the form is either in US or EU format wheich then skews the data when it is copied to the main datasheet.

I've attached a diluted version of the workbook to show what i mean but in essence the date (day) chosen is over 12 e.g. 13 May 2014 the date is copied across to the text box correctly, then over to the datasheet correctly once the form is completed / submitted. If the date (day) chosen by the user is 12 or less e.g. 7 November 2014 the day/months are transposed into the text box and subsequent datasheet.

To throw another spanner in the works which is why i've striggled to try and sort it is that it is random if and when it transposes the dates. I've just done 13 tests on my PC and everyone both dates over 12th and below have worked properly both transposing to th etext box and the datasheet.

THis is not priority as dates are being eneterd manually but if anyone can help that would be great

Thanks

Mykal11691

GTO
05-15-2014, 03:44 AM
Greetings Mykal,


...THis is not priority as dates are being eneterd manually but if anyone can help that would be great...

I did not spend much time reading through (or more accurately, thinking through) the Class, but from what I spotted, I am wondering whether a couple of changes would "cure all ills".


...In testing however i found that depnding on the date that is selected by the user the date copied to the form is either in US or EU format wheich then skews the data when it is copied to the main datasheet.

...what i mean .. in essence the date (day) chosen is over 12 e.g. 13 May 2014 the date is copied across to the text box correctly, then over to the datasheet correctly once the form is completed / submitted. If the date (day) chosen by the user is 12 or less e.g. 7 November 2014 the day/months are transposed into the text box and subsequent datasheet...


...To throw another spanner in the works which is why i've striggled to try and sort it is that it is random if and when it transposes the dates. I've just done 13 tests on my PC and everyone both dates over 12th and below have worked properly both transposing to th etext box and the datasheet...

Given my locale, I use U.S. settings (month, day, year) for Windows, so maybe I am missing some influence of U.S.-centric dates on your workbook. That said, my minimal testing did not yield any spotty results. More interesting to me at least, are your comments at results being inconsistent. That seems odd.

By chance, could the '12 or less, vs. more than 12' results be happening on only certain computers? I am curious if the Windows settings are different.

Anyways, very minimally tested, but in a junk copy of your WB, try using the textbox's .Tag property to store the date as an integer. I would think that CDate on its own would be susceptible to local settings, whereas storing the date's Long value would be more dependable?

In CaptureClick()

Case 3, 4, 5, 6, 7, 8
'select days...
mSelectedDate = mStartDate + (intRow - 3) * 7 + (intCol - 1)

mBoundControl.Value = SelectedDate

mBoundControl.Tag = CLng(SelectedDate)

mForm.FrameCalendar.Visible = True
Exit Sub
Case 9


In Private Sub CommandButton2_Click()


With ws
'.Cells(lRow, 1).Value = CDate(TextBoxDate.Value)
.Cells(lRow, 1).Value = CDate(CLng(TextBoxDate.Tag))

.Cells(lRow, 4).Value = Me.CBO1.Value
.Cells(lRow, 5).Value = Me.CBO2.Value
.Cells(lRow, 6).Value = Me.CBO3.Value
.Cells(lRow, 7).Value = Me.CBO4.Value
.Cells(lRow, 8).Value = Me.CBO5.Value
.Cells(lRow, 9).Value = Me.chk1.Value
.Cells(lRow, 10).Value = Me.TextBox1.Value
End With


Hope I didn't suggest something screwy...

Mark

mykal66
05-16-2014, 12:36 AM
Hi Mark

The issue does seem to happening on everyone'c coemputer apart from mine. We all have the same laptops in the office, all supplied preconfigured which makes the incosistancy stranger.

I will try what you have suggesetd above and see what happens - thank you very much for yourn time and help

Mykal