none
Text box formatted for General Date displays pound/hash signs RRS feed

  • Question

  • I have an Access form with several text boxes that display dates, all of which are formatted as General Date (i.e. mm/dd/yyyyy hh:mm:ss AM/PM).  On some dates, instead of the date and time being displayed, what appears is a string of hash/pound signs filling the text box (i.e. "##################").  If I click the mouse in one of the fields, the display changes to the desired formatted date.  However, I need the date to display properly formatted without any user effort.  I've attached screen shots of one of the text boxes as it first appears, and one showing the properly formatted date and time that appears after I click inside of the text box.   Note that the date that displays improperly is slightly longer than the dates that display without any user intervention - but, as you can see, there is plenty of room within the text box to display the date as m/dd/yyyy h:mm:ss AM/PM.

    Monday, September 18, 2017 5:31 PM

Answers

  • I don't know why this happens with date/time values.  The only thing I can think of is that it is somehow connected with the implementation of the date/time data type in Access as a 64 bit floating point number.  How is the value inserted?  As it is to a precision of one second, I assume that it is not inserted manually.  And is there any occasion for the value to be edited in the form?  If not, then you could perhaps hide the bound control and display the value in an unbound control with:

        =Format([AssignDate],"mm/dd/yyyy hh:nn:ss AM/PM")

    As the Format function returns a string expression there should be no problem fitting it into a more appropriately sized control.

    Ken Sheridan, Stafford, England

    • Marked as answer by Tim Peters Tuesday, September 19, 2017 6:07 PM
    Tuesday, September 19, 2017 4:30 PM

All replies

  • Hi Tim,

    When you said the dates are formatted as General Date, is this done through the Textbox's Format property? If not, try selecting General Date in the Format property of the Textbox on the form.

    Just a thought...

    Monday, September 18, 2017 6:42 PM
  • This usually means that the control, despite appearances to the contrary, is insufficiently wide to accommodate the data.  What happens if (a) you increase the width of the control, or (b) reduce the font size of the control?

    Ken Sheridan, Stafford, England

    • Proposed as answer by Terry Xu - MSFT Tuesday, September 19, 2017 7:10 AM
    Monday, September 18, 2017 11:24 PM
  • .theDBguy,

    Yes, I meant that the Textbox's Format property was already set to General Date.

    Tuesday, September 19, 2017 12:55 PM
  • Hi Ken,

    8 pts seems to be the smallest size I can select for the Font (this is what it is currently set to).  If I make the textbox wider, it does indeed display the date properly, but with a lot of unnecessary leading white space in the textbox, and takes up an inordinate amount of space on the form.  As you can see from the screen shots I attached, visually, there appears to be more than ample space for the date and time being displayed.  Just based on looking at it, I can't imagine a date and time that should not fit within the control.   Do you know why it would require so much space?

    Tuesday, September 19, 2017 1:54 PM
  • If you're concerned about how the text box looks, then just set the alignment to centered and size the box accordingly.
    Tuesday, September 19, 2017 3:30 PM
  • I don't know why this happens with date/time values.  The only thing I can think of is that it is somehow connected with the implementation of the date/time data type in Access as a 64 bit floating point number.  How is the value inserted?  As it is to a precision of one second, I assume that it is not inserted manually.  And is there any occasion for the value to be edited in the form?  If not, then you could perhaps hide the bound control and display the value in an unbound control with:

        =Format([AssignDate],"mm/dd/yyyy hh:nn:ss AM/PM")

    As the Format function returns a string expression there should be no problem fitting it into a more appropriately sized control.

    Ken Sheridan, Stafford, England

    • Marked as answer by Tim Peters Tuesday, September 19, 2017 6:07 PM
    Tuesday, September 19, 2017 4:30 PM
  • Thanks Ken.  Putting the value into an unbound control with the Format function seems like a good work-around. 
    Tuesday, September 19, 2017 6:07 PM