Textbox DatePicker

[LB79]

Morning all - Happy Friday!

Can anyone tell me if its possible to make the calander select button of the date picker (text box) visible all of the time. Currently I have to double click the text box to make it appear, which is fine for me, but not for people who dont know its there.

Thanks

 

[missinglinq]

Don't run 2007 but contribute to half a dozen Access forums/newsgroups and archive stuff that I think will pop up over and over again.

In the control's On Got Focus event place this code:

DoCmd.RunCommand acCmdShowDatePicker

When the user moves into the textbox the calendar should then popup.

Linq ;0)>

 

[LB79]

Hi and thanks for the suggestion, but I want the button to be visable at all times (the button that appears next to the textbox to select the date). Is this possible?

 

[DCrake]

You clould place an image in its place and when the date picker image is displayed is appears seemless.

The user would be clicking on the picker not the image.

 

[LB79]

Good thinking DCrake... Thanks

 

[boblarson]

Hi and thanks for the suggestion, but I want the button to be visable at all times (the button that appears next to the textbox to select the date). Is this possible?

You would have to resort to something like what David posted because the answer is NO, you can't have it show all of the time. This was something identified to the Access team and that we hope they fix in the next version (15) because I don't think they did do it in Access 2010 (14).

 

[LB79]

Seems a pretty basic thing for them not to include.
I think maybe there are some bugs with it too when running SQL in VBA

 

[boblarson]

Seems a pretty basic thing for them not to include.

Most of the Access MVP's and other people at the Access 2010 dev kitchen that I attended, a couple of years ago, thought so too.

I think maybe there are some bugs with it too when running SQL in VBA

What bugs? Can you be more specific? I don't know of any surrounding date fields, text boxes with dates.

 

[LB79]

I have 2 textboxes with DatePicker. They are identical (one is a copy of the other). When I run SQL in VBA filtering the date using my 2 date picker txtboxes, it ignores the first date. If i transfer the code to a query, it doesnt ignore the first date.

Thanks for any insights

VarSQL = Me.ATMT_txt1 & "# And #" & Me.ATMT_txt2

ATMTSQL = "SELECT Format([DEPART_ACTUAL_DT],'yyyymm') AS CostMonth, dbo_MG_VESSEL_VOYAGE.SERVICE_CD, dbo_MG_VESSEL_VOYAGE.OPERATOR_CD, dbo_MG_VESSEL_SCHEDULE.VESSEL_CD, dbo_MG_VESSEL_SCHEDULE.VOYAGE_CD, dbo_MG_VESSEL_SCHEDULE.LEG_CD, dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD, dbo_MG_VESSEL_SCHEDULE.DEPOT_CD, dbo_MG_VESSEL_SCHEDULE.DEPART_ACTUAL_DT, dbo_MG_VESSEL_SCHEDULE.DEPART_STATUS_FLG, dbo_MG_VESSEL_VOYAGE.DELETED_FLG, dbo_MG_VESSEL_SCHEDULE.CALLING_PURPOSE_CD, dbo_MG_VESSEL_SCHEDULE.PORT_SEQUENCE_ID"
ATMTSQL = ATMTSQL & " INTO tbl_SAI_PortProspectsTemp"
ATMTSQL = ATMTSQL & " FROM dbo_MG_VESSEL_SCHEDULE INNER JOIN dbo_MG_VESSEL_VOYAGE ON (dbo_MG_VESSEL_SCHEDULE.LEG_CD = dbo_MG_VESSEL_VOYAGE.LEG_CD) AND (dbo_MG_VESSEL_SCHEDULE.VESSEL_CD = dbo_MG_VESSEL_VOYAGE.VESSEL_CD) AND (dbo_MG_VESSEL_SCHEDULE.VOYAGE_CD = dbo_MG_VESSEL_VOYAGE.VOYAGE_CD)"
ATMTSQL = ATMTSQL & " WHERE (((dbo_MG_VESSEL_VOYAGE.SERVICE_CD)Like '" & Trade & "*') AND ((dbo_MG_VESSEL_VOYAGE.OPERATOR_CD)='U') AND ((dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'GB*' Or (dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'DE*' Or (dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'NL*' Or (dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'DK*' Or (dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'SE*') AND ((dbo_MG_VESSEL_SCHEDULE.DEPART_ACTUAL_DT) Between # " & VarSQL & "#)"
ATMTSQL = ATMTSQL & " AND ((dbo_MG_VESSEL_VOYAGE.DELETED_FLG)='N') AND ((dbo_MG_VESSEL_SCHEDULE.CALLING_PURPOSE_CD)<>'C') AND ((dbo_MG_VESSEL_SCHEDULE.PORT_SEQUENCE_ID)=1)) OR (((dbo_MG_VESSEL_VOYAGE.SERVICE_CD)Like '" & Trade & "*') AND ((dbo_MG_VESSEL_VOYAGE.OPERATOR_CD)='U') AND ((dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'PT*') AND ((dbo_MG_VESSEL_SCHEDULE.DEPART_ACTUAL_DT)Between # " & VarSQL & "#) AND ((dbo_MG_VESSEL_VOYAGE.DELETED_FLG)='N') AND ((dbo_MG_VESSEL_SCHEDULE.CALLING_PURPOSE_CD)<>'C') AND ((dbo_MG_VESSEL_SCHEDULE.PORT_SEQUENCE_ID)=2));"
DoCmd.RunSQL ATMTSQL

 

[boblarson]

Have you done

Debug.Print ATMTSQL

just after your assignment to it so see what the actual output of the query string is?

 

[LB79]

Aha... This is odd. Txt2 is a copy of Txt1. In the txtboxes I select the dates I want and they show as DD MMMM YYYY. When I run the VBA/SQL with debug.print, it gives me the below code.

[COLOR=#1f497d][FONT=Arial]SELECT Format([DEPART_ACTUAL_DT],'yyyymm') AS CostMonth, dbo_MG_VESSEL_VOYAGE.SERVICE_CD, dbo_MG_VESSEL_VOYAGE.OPERATOR_CD, dbo_MG_VESSEL_SCHEDULE.VESSEL_CD, dbo_MG_VESSEL_SCHEDULE.VOYAGE_CD, dbo_MG_VESSEL_SCHEDULE.LEG_CD, dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD, dbo_MG_VESSEL_SCHEDULE.DEPOT_CD, dbo_MG_VESSEL_SCHEDULE.DEPART_ACTUAL_DT, dbo_MG_VESSEL_SCHEDULE.DEPART_STATUS_FLG, dbo_MG_VESSEL_VOYAGE.DELETED_FLG, dbo_MG_VESSEL_SCHEDULE.CALLING_PURPOSE_CD, dbo_MG_VESSEL_SCHEDULE.PORT_SEQUENCE_ID INTO tbl_SAI_PortProspectsTemp FROM dbo_MG_VESSEL_SCHEDULE INNER JOIN dbo_MG_VESSEL_VOYAGE ON (dbo_MG_VESSEL_SCHEDULE.LEG_CD = dbo_MG_VESSEL_VOYAGE.LEG_CD) AND (dbo_MG_VESSEL_SCHEDULE.VESSEL_CD = dbo_MG_VESSEL_VOYAGE.VESSEL_CD) AND (dbo_MG_VESSEL_SCHEDULE.VOYAGE_CD = dbo_MG_VESSEL_VOYAGE.VOYAGE_CD) WHERE (((dbo_MG_VESSEL_VOYAGE.SERVICE_CD)Like '*I') AND ((dbo_MG_VESSEL_VOYAGE.OPERATOR_CD)='U') AND ((dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'GB*' Or (dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'DE*' Or (dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'NL*' Or (dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'DK*' Or (dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'SE*') AND ((dbo_MG_VESSEL_SCHEDULE.DEPART_ACTUAL_DT) Between # 01/03/2011# And #31/03/2011#) AND ((dbo_MG_VESSEL_VOYAGE.DELETED_FLG)='N') AND ((dbo_MG_VESSEL_SCHEDULE.CALLING_PURPOSE_CD)<>'C') AND ((dbo_MG_VESSEL_SCHEDULE.PORT_SEQUENCE_ID)=1)) OR (((dbo_MG_VESSEL_VOYAGE.SERVICE_CD)Like 'I*') AND ((dbo_MG_VESSEL_VOYAGE.OPERATOR_CD)='U') AND ((dbo_MG_VESSEL_SCHEDULE.PORT_OF_CALL_CD) Like 'PT*') AND ((dbo_MG_VESSEL_SCHEDULE.DEPART_ACTUAL_DT)Between # 01/03/2011# And #31/03/2011#) AND ((dbo_MG_VESSEL_VOYAGE.DELETED_FLG)='N') AND ((dbo_MG_VESSEL_SCHEDULE.CALLING_PURPOSE_CD)<>'C') AND ((dbo_MG_VESSEL_SCHEDULE.PORT_SEQUENCE_ID)=2));[/FONT][/COLOR]

Which looks as it should. BUT if I then transfer that SQL to a query, its changing the first date from 01/03/2011 to 03/01/2011. Im confused!

 

[boblarson]

Aha... This is odd. Txt2 is a copy of Txt1. In the txtboxes I select the dates I want and they show as DD MMMM YYYY.

Aha - that would be the problem. You need to use US date format or a non-ambiguous format (like 24 Apr 2010) so that Access knows what to use. It is the way it is set up by Microsoft.

To use US date format, you would format your dates like this:

Format([DateTimeField], "#mm\/dd\/yyyy#")

See this article for more about this.

 

[LB79]

Thanks Bob - you have saved my sanity! Have a great weekend people!

 

[todAnthony]

Morning all - Happy Friday!

Can anyone tell me if its possible to make the calendar select button of the date picker (text box) visible all of the time. Currently I have to double click the text box to make it appear, which is fine for me, but not for people who don't know its there.

Thanks

I realize this topic is a bit older but my 2-cents worth:

ActiveX Controls/Microsoft Date and Time Picker Control 6.0 (SP6)

I use it on a form where I have 2 of these. You pick 2 dates and run DoCmd.OpenReport "RPT_WEEKLY", acViewPreview, , "[MASTER_DATE] Between #" & Me.frmFrom & "# And #" & Me.frmTo & "#" on a button to make a report of whatever is between those dates.

I had to use the ActiveX Control because it looks like I could not set up a "Date Box". EVEN THOUGH in the properties of a Text Box on a form on the format tab there is an option to 'show date picker'. I am assuming this is for when the Control Source of the text box is already pointing to a date data type in the DB already.

Anyways the ActiveX Control works for me. You don't get the pretty little date picker icon but it does appear as a drop down so the user knows there is an option.

 

[boblarson]

Just be careful about using ActiveX controls. You need to ensure that they are installed for each computer (not always an easy task) and then things happen where they will not work for later versions. So, you need to be careful when using them. I, personally, have gone to non-ActiveX solutions which has worked out well.

 

[missinglinq]

Most experienced developers do the same as Bob, eschewing the ActixeX Calendars/DatePickers for the reason he gave, going to a Form-based Calendar, such as Allen Browne's site offers.

I understand your use of Unbound Controls here, but when trying to set the 'show date picker' property did you have the format of the Control set as a Date format?

Linq ;0)>
The alternative