Date/Time Picker (AccessXP)

Topic

New Reply

Is there something magical I need to do in order to get a query to read the date from the DTPicker???

The criteria from the query reads: “forms!formname.DTPicker.Value“. I have checked the spelling of the formname, which is correct. I have also registered the control.

The form where the DTPicker is located does not show up in the object browser (which I find interesting), but the subform located on this form does show up. Would this have anything to do with that?

Reply | Quote

Replies

Hi,
Try forms![formname]![dtpicker]. I use dtpickers quite often and this seems to work for me.

Thanks,
Mark

Reply | Quote

Same result. I still get 0 records in the recordset. I know the syntax is correct, otherwise it would interpret it as a parameter. It is like the date format of the DTPicker is different than that of the table data. If I manually enter in a date, I get the records I expect. Whatsup with that?

The problem with getting a result set based on the DTPicker still exists (as described in my earlier post), but the end result of what I am trying to do is to display records in a subform based on a date chosen by the DTPicker. Well, as far as I can tell, the records displayed in a subform have to be related in a one-to-many fashion to a field in another table or query–upon which the main form must be based. Is it possible to even use the DTPicker in this manner??? Any suggestions around this dilemma will be appreciated.

Reply | Quote

I haven’t tried it that way, but the query engine balks at things unexpectedly in A2k and AXP, so you may have to try a workaround. I generally write the result of a DTPicker to an unbound textbox so I can reference it. YOu might try that and see if it makes a difference. Another thing to consider is that if your date fields in the table are general dates and include the time, then there is never going to be a match because the date formats will be different. You may need to cast the query field and the value from the DTPicker both as short dates using the format function before you get any matches.

Reply | Quote

Should this form I’m working on that includes the DTPicker be showing up in the Project Explorer? all my other forms are there, but this form I’m working on is not listed. Neither is it’s subform. Yes, I’ve saved the forms.

Reply | Quote

Does the form have a code module? If not, it won’t show up in the project explorer.

Reply | Quote

As noted if form does not have code module will not show up in Project Explorer unless you set Has Module property to Yes, since Project Explorer only lists “Class Objects”. A form does not have to be listed in PE to be referenced in a query, it only has to be open when query runs. As test, add an unbound textbox to your form and set its Control Source to =[DTPicker2] (or whatever the control name is) and add this event procedure:

Private Sub DTPicker2_Updated(Code As Integer)
Me.Text1.Requery
End Sub

Text1 is the unbound text box. When form runs and DTPicker updated, you will note the value displayed in textbox is likely something like 10/31/2002 3:54:55 AM. IE, date and time. To make sure DTPicker returns date value only, open DTPicker Custom Properties and set Format to 1 -dtpShortDate and Time to 12:00:00 AM, as illustrated. When you run form again the textbox should now display date only. When used as query criteria, records that match date should be returned (assuming date only is being saved in date field). Example of query criteria: [Forms]![frmDTPicker]![DTPicker2]. NOTE: Date field does not have to be in same format as used for DTPicker. If date field does have time saved with date, use DateValue function in calculated query field to return date portion only and use criteria in this field.

HTH

Reply | Quote

OK, I’m starting to feel very inept with this control. It still is not working. Mark and Charlotte–thanks for your suggestions. I created the textbox as you recommended. The textbox appears to update correctly, except that when I bring the focus to the textbox itself, the text change from short date to date and time. It returns to just the short date when the focus is moved elsewhere. The date is the date of the Picker, but the time is that of the current system time. Is this normal?

I have double checked and the format property for the query field, DTPicker and the new textbox: they are all set to Short Date. And I have double-check the spelling and syntax of the query criteria. Everything is the way all of you have suggested–that way I thought it was suppose to be all along. I even tried referencing the new textbox instead of the DTPicker in the query. I still get an empty recordset when I run the query. I know the rest of the query works because if I change the query criteria to a hard-coded date, I get the data I expected.

I have Ver. 6 © 1997. Could there be an incompatibility with this version and AXP.

Unless someone sees something I’m missing, I’ve got to move on and do something different.

Reply | Quote

What happens if you set the criteria to Fix([Forms]![frmACDSummary]![ocxDTPicker]) or Fix([Forms]![frmACDSummary]![Box14]) ? You can use Int instead of Fix too.

Reply | Quote

Actually, I would use the events of the DTPicker itself to force a date into the textbox rather than setting the controlsource of the textbox to point to the DTPicker. AS it is, you need to requery the textbox when you make a selection from the DTPicker, so why not just stick a value in there instead? That would allow you to control what gets put in there and restrict it to a short date if that is the format in your table or query. Controls showing date and time when they get the focus is entirely typical of Access 97 when the underlying field contains a general date. The display format gets overridden when the control gets the focus. The format you set for the DTPicker is a display format, but if you notice, there is also a time there, midnight, which is the time associated with all short dates.

One thing you might try is putting the control reference in the query inside a CDate function to be sure you are getting a date for comparison. We just went through this in another thread where someone was trying to reference a subform control in an Access XP query and not getting anything back. I discovered in my own testing that I could retrieve a date value from a subform control by wrapping it in a CDate function.

Reply | Quote

Regarding the hidden time in the DTPicker when it is formatted for shortdate: When my DTPicker gets the focus and I can see the time, it does not read 12:00:00 AM. The time is always the current system time. YES, I have checked to make sure that all of the controls are formated with the shortdate format.

I personally think that the version of the DTPicker I have does not work correctly with AXP. I think the current version is Ver 6 SP4. I only have ver 6. I need to find the more up-to-date version so that I can test this.

Thank you for all your help. We have all spent too much time on this problem.

Reply | Quote

Well certainly the version of DTPicker I have on my machine is from MS Common Controls 2-6.0 (SP4) and I don’t see that behavior, regardless of the version of Access is run it in.

Reply | Quote

I would definitely agree with your comment that way too much time has been spent on the Date/Time Picker and its flaky behavior. Experimenting further I found it necessary to add line to Form Load event to ensure DTPicker value reflected date only & not system time when form opens:

Me.DTPicker1.Value = Date

If this is done then works OK; textbox set to DTPicker value displays only date, not time. If DTPicker not set to 12 AM by this method, then will include time value too (it seems to “reset” itself even if you had previously set its value to 12 AM). The textbox displays date only because the Format is set to Short Date. When you insert cursor in textbox you see actual value, including time, because Format property affects how value is displayed but does NOT change actual value, which is why query may not work correctly. If you want to use textbox for query criteria try using this expression for its ControlSource:

=DateValue([DTPicker1])

This will ensure textbox returns date value only. If you insert cursor in textbox you should not see time value.

I’m using Access XP. The DTPicker wasn’t originally registered as ActiveX control on my installation, I found the file (MSCOMCT2.OCX) buried about 10 folders deep under the C:Program FilesMicrosoft Visual Studio .NET folder, the version number is 6.0.81.40 (“Microsoft Common Controls 2 ActiveX Control DLL”).

Due to flaky behavior I avoid using DTPicker & similar controls in work environment. Usually use plain old unbound textboxes or combo and listboxes to allow user to select date criteria for a form or report. For example, use 2 combo boxes to select date range for report; RowSource for each uses SQL to display valid date range based on values in OrderDate field (example from Northwind.mdb Orders table):

SELECT DISTINCT OrderDate FROM Orders ORDER BY OrderDate;

Second combo uses same SQL only ORDER BY specifies DESC order. The query criteria for report then references the two combo box values using BETWEEN… AND expression. Of course plain ole combo & textboxes aren’t as fancy as Date/Time Picker but are less hassle to work with.

HTH

Reply | Quote