DTPicker misbehavin - badly.

[jag108]

I am trying to run SQL from a user form. I have the SQL running prefectly, but when I try to use a form with two DTPickers I start running into trouble.

I want to format the dates in the DTPickers as,
DTPicker1.customformat = Format(now(), "yyyymmdd")
I need this date format because it is the format in the Oracle DB we use.
Once the Form initialises, it runs the code above. Then the problem starts if you want to adjust the date using the DTPicker. Quite simply it will not let you, it will drop down showing dates etc, but no matter what you do it will only use the initialised Now() date!!!!

Is this a bug or is there another way of me formatting a DTPicker at start up?

Thanks in advance.
Jag

 

[pfarmer]

I am trying to run SQL from a user form. I have the SQL running prefectly, but when I try to use a form with two DTPickers I start running into trouble.

I want to format the dates in the DTPickers as,
DTPicker1.customformat = Format(now(), "yyyymmdd")
I need this date format because it is the format in the Oracle DB we use.
Once the Form initialises, it runs the code above. Then the problem starts if you want to adjust the date using the DTPicker. Quite simply it will not let you, it will drop down showing dates etc, but no matter what you do it will only use the initialised Now() date!!!!

Is this a bug or is there another way of me formatting a DTPicker at start up?

Thanks in advance.
Jag

Why not format the value the date picker provides you rather then trying to format the datepicker itself?

Perry

 

[pfarmer]

I am trying to run SQL from a user form. I have the SQL running prefectly, but when I try to use a form with two DTPickers I start running into trouble.

I want to format the dates in the DTPickers as,
DTPicker1.customformat = Format(now(), "yyyymmdd")
I need this date format because it is the format in the Oracle DB we use.
Once the Form initialises, it runs the code above. Then the problem starts if you want to adjust the date using the DTPicker. Quite simply it will not let you, it will drop down showing dates etc, but no matter what you do it will only use the initialised Now() date!!!!

Is this a bug or is there another way of me formatting a DTPicker at start up?

Thanks in advance.
Jag

Jag,

I use four pickers to pick start and stop dates and times. The basic format I use is:

DTPicker1.Value = Now()

DTPicker2.Value = Now()

DTPicker3.CustomFormat = "HHmm"
DTPicker3.Hour = 7
DTPicker4.CustomFormat = "HHmm"
DTPicker4.Hour = 15

DTPicker3.Minute = 0
DTPicker4.Minute = 30

For pickers one and two I set the properties to dtpshortdate to yield something like 8/24/2005. Pickers three and four are used to pick the hours and I format those as indicated above. In the properites they indicate HHmm without the quotes. They default to a start of 0700 and a stop time of 1530.

Now I also have an easter egg of sorts, if you click a lable on the form it will place the current date in picker one and the current time in picker three.

It will also adjust the ending time to be 10 minutes from the start time as a default if later then 1530 using the following code:

Private Sub Label16_Click()

Dim DaysDiff
Dim HoursDiff
Dim MinutesDiff
Dim JobLengthHours
Dim x
Dim y

On Error GoTo none

DTPicker1.Value = Now()
DTPicker2.MinDate = DTPicker1.Value
DTPicker3.Value = Now()

DaysDiff = DateDiff("d", DTPicker1.Value, DTPicker2.Value)
HoursDiff = DTPicker4.Hour - DTPicker3.Hour

MinutesDiff = DTPicker4.Minute - DTPicker3.Minute
MinutesDiff = MinutesDiff / 60
MinutesDiff = Format(MinutesDiff, "##0.##")

Label62.Caption = DaysDiff * 24 + HoursDiff + MinutesDiff & " Hours"


If DaysDiff * 24 + HoursDiff + MinutesDiff <= 0.17 Then
x = DTPicker3.Minute + 10
y = DTPicker3.Hour
If x > 59 Then
x = x - 60

y = DTPicker3.Hour + 1
End If
If y > 23 Then
y = y - 24

DTPicker2.Value = DTPicker1.Value + 1

End If
DTPicker4.Minute = x
DTPicker4.Hour = y
End If

DaysDiff = DateDiff("d", DTPicker1.Value, DTPicker2.Value)
HoursDiff = DTPicker4.Hour - DTPicker3.Hour

MinutesDiff = DTPicker4.Minute - DTPicker3.Minute
MinutesDiff = MinutesDiff / 60
MinutesDiff = Format(MinutesDiff, "##0.##")

Label62.Caption = DaysDiff * 24 + HoursDiff + MinutesDiff & " Hours"

none:

End Sub

Note that it adjust for going past midnight.

I know that this code doesn't really pertain to you but I included it to show the various options you can use via VBA to input values into a picker. Also I am sure it can be improved upon but heh, it works as is.

Perry