Solved Date is displaying odd behavior (1 Viewer)

dibblermail

Member
Local time
Today, 00:28
Joined
Jan 10, 2025
Messages
55
I'm still quite new to Access & I've hit what seems to be a formatting issue. I've trawled google but cant find an answer to my exact issue.

I'm trying to fill a text box on a form with todays date using this code.

Code:
Forms!Form0_AddEditJob!TBOX_DateInitial.DefaultValue = Format(Now, "yyyy-mm-dd")

I'm using .DefaultValue because its a new record which isn't saved yet

If I hover over "Now" in the line of code as it runs I get Now = 24/09/2025 09:23:31

if I go to the immediate window I get...

Debug.Print Format(Now, "yyyy-mm-dd")
2025-09-24


But my textbox is showing 14/06/1905

If I change to the format I actually want to "dd-mm-yyyy" my text box shows 29/06/1894

It's not showing 12/31/1899 so I assume its not a null value

Anyone been here before? It seems like its got to be a simple formatting error somewhere?
 
use:
Code:
=Now()
as the Default value and don't use any Format on it.
on design view of Form, Forms!Form0_AddEditJob,
click on TBOX_DateInitial textbox and on Property->Format, use:

Code:
dd-mm-yyyy

ms access save date in US date format.
the Format property of TBox_DAteInitial is for viewing purpose, only.
 
Try the following

Forms!Form0_AddEditJob!TBOX_DateInitial.DefaultValue = "= Date() "
 
Try the following

Forms!Form0_AddEditJob!TBOX_DateInitial.DefaultValue = "= Date() "
If I use exactly that, it Errors with "Runtime error 438: Object doesn't support this property or method"
If I lose the speech marks it strips the parenthesis off leaving me with = Date & the text box shows 00:01:54
 
use:
Code:
=Now()
as the Default value and don't use any Format on it.
on design view of Form, Forms!Form0_AddEditJob,
click on TBOX_DateInitial textbox and on Property->Format, use:

Code:
dd-mm-yyyy

ms access save date in US date format.
the Format property of TBox_DAteInitial is for viewing purpose, only.
this looks even more like a format issue as the text box is now showing #Name?

I'll check the table holding the info & report back
 
The table has DateInitail set as Date/Time with no validations or masks

this is set up as back end / Front end databases, in case that has its own potential explanation.
 
If you simply set the control default value to Date it will only apply to new records, unless there is something more complicated going on here.
 
If you simply set the control default value to Date it will only apply to new records, unless there is something more complicated going on here.
The same form is used to open existing jobs or new jobs.

It currently opens an existing job & shows the date the job was created correctly.

If I were to set the textbox properties to show todays date at a form level, would that not cause an issue to existing jobs displaying their dates correctly?
 
If I were to set the textbox properties to show todays date at a form level, would that not cause an issue to existing jobs displaying their dates correctly?
it will not affect previous saved records.

btw, just tested on ms access BE, and did work.
 
If you simply set the control default value to Date it will only apply to new records, unless there is something more complicated going on here.
Thanks for the nudge in the right direction. I just tested your method & its working just fine.

I still don't understand why what I was doing was failing, but I have a working solution so that's the important thing
 
It's easy to miss the importance of the distinction between date values and date formats. Arnel alluded to that in post #2 in his explanation of how to address the problem, and Minty further explained in post #7.

The key point is that Format() does NOT change the date value stored in the field from say, #2025/09/23 08:13:34 AM# to whatever other value it would appear in when the Format() is applied. Format only changes the way that value is displayed to the user in the interface. The value itself is not changed, only it's appearance to the user is changed.

And, actually, the above statement is only partially true. It refers to the different displays.

Actually, in addition to changing the display of the date, the Format() expression converts it from a date to a string that looks like a date. I.e. a string made up of digits and delimiters.

When that happens, Access can, in certain circumstances, get confused about the apparent value. It's supposed to be a date and time, but it's been converted to a string that has the format usually reserved for date/time fields. Access often can correctly interpret that formatted string as the date you intend, but not always. One could delve into the technical explanation of why that is and under what circumstances, but one can also just realize that trying to apply a format to dates for data entry is a fool's errand. Reserve formatting for those places in the interface where your users are going to expect certain displays, i.e. in reports or on summary forms where you are only displaying them, and not in those situations where you are entering or modifying values, or relying on the value of the dates in queries or calculations.

It's important at a fundamental level NOT to try to impose formatting on date/time values at any time other than when they are displayed in the interface.

For data entry, use Now() if you need both the date and time to be stored. For data entry, use Date() if you only need the date, with the default time of 00:00:00.

All dates are stored with both a date and time, by the way. The difference between Date() and Now() is, as I said, whether the time is the default of 00:00:00 or the actual time.
 
Do you actually want to store the time? Somehow I don't think so. You are using Now() which returns the current date AND TIME rather than Date() which returns just the date and then you are using Format() to try to get rid of the time. Well, doesn't matter what you do with a format, Format() only changes the displayed value.

Your problem would probably be solved by learning the difference between these two functions and using the correct one.

NEVER format data at the table level since it just hides the true value and you will drive yourself nuts trying to figure out why #9/24/25# is not equal to #9/24/25# but if you remove the format property, you will see that one of the dates includes a time value. If you remove the format on the table and see that you actually have time values you don't want, you will need to run an update query using the DateValue() function to replace the datetime with just the date for all the existing data.
 
I'm trying to fill a text box on a form with todays date using this code.

Code:
Forms!Form0_AddEditJob!TBOX_DateInitial.DefaultValue = Format(Now, "yyyy-mm-dd")
Firstly, to assign a value to a bound column in the form's current record you should use the bound control's Value property. As this is the default property you do not need to specify it. Secondly a date literal must be delimited with # characters and be in US or an otherwise internationally unambiguous format. Your use of the ISO standard for date notation of YYYY-MM-DD is appropriate. Thirdly, you can call the VBA date function to return the current date with a time of day element of zero. There is no such thing as a date value per se, the date/time data type represents the date and time as a 64 bit floating point number, of which the integer part represents the day, and the fractional part the time of day. An integer value therefore represents the point in time at midnight at the start of a day, not a date to a precision of one day. In your case you can use the following therefore:

Code:
Forms!Form0_AddEditJob!TBOX_DateInitial = VBA.Date

The format is of course irrelevant to how the date is stored. Whatever your regional short date format is, the date will be stored as the same floating point number, and, by default, expressed in the short date format of the system. The fact that the row has not yet been committed to the table is not relevant.

In a situation where you do need to define the DefaultValue property of a control, this property is a string expression, regardless of the data type of the column in question, so to assign the current date to this property the code would be:

Code:
Forms!Form0_AddEditJob!TBOX_DateInitial.DefaultValue = """" & Format(VBA.Date, "yyyy-mm-dd") & """"

Wrapping the value is literal quotes characters, each represented by a contiguous pair of quote characters, is essential. Otherwise the value would be interpreted as an arithmetical expression, and return the wrong date/time value. When you set a control's DefaultValue property, that value will appear in the control whenever the form is moved to a new record. Unlike setting the Value property, however, it will not initiate a new unsaved record, so the user can abort the insertion of a record simply by moving to another record or closing the form.

PS: Another way to return a string expression as a date/time value is to call the CDate function. Unlike a date literal this respects the local date format settings, e.g. on my system:

? CDate("04/07/2025")
04/07/2025

If this is formatted it can be seen that this correctly uses the UK format of dd/mm/yyyy:

? Format(CDate("04/07/2025"),"dd mmmm yyyy")
04 July 2025

whereas a date literal does not:

? Format(#04/07/2025#,"dd mmmm yyyy")
07 April 2025
 
Last edited:
I understood the basics, but some on the nuance was new to me. You've just pointed out the difference between Date & Now which I hadn't caught until now.

I was trying to control how the date appeared in the text box of a user form. Whilst I was ignoring how it appeared in the back end database, I didn't make the leap of thought to control it in the text box itself, rather than the data being added to the text box. I now see the error in my logic.

What I also didn't twig was that setting the text box itself to show the current date was fine, as it would be overwritten when using the same text box to load the date from the back end for an an existing job.

Thanks all for taking the time to help to clarify where I went wrong. Very much appreciated.
 
One other very important concept is that using the Format() function converts a date to a string so any date you format this way will act like a string and not a date. Formatting is ONLY for display. If your standard date format is not US standard of m d y, then you always need to format dates when they are used in strings to make sure that SQL handles them correctly. Is 1/2/25 Jan 2 or Feb 1?

There is lots written here regarding formatting dates but to summarize, use the format property of controls on a form if you want the date format to be controlled.

Here are two queries. The second requires the date to be formatted whereas the first does not.

SQL = "Select ... From ... Where SomeDate < Forms!myform!MyDate"

SQL = "Select ... From ... Where SomeDate < #" & Format(Forms!myForm!MyDate, "mm/dd/yyyy") & "#"

Why? Because the first query is referring to a date control on the form so Access KNOWs it is a date and uses the internal data value. But the second case embeds the date within a string and so unless the date is formatted as mm/dd/yyyy or yyyy/mm/dd, then the confusion of Jan 2 vs Feb 1 arises.

And finally, JIK you don't know, datetime stores dates internally as a double precision number with the integer portion being the number of days from a specific point depending on the database engine and the decimal being the elapsed time since midnight.

For Jet/ACE the origin date is 12/30/1899 but for SQL Server it is 1/1/1900 not that you care. So for SQL Server 1.5 = 1/2/1900 12:00:00 and -1.5 = 12/31/1899 12:00:00 and 0.5 = 1/1/1900 12:00:00

1758729353205.png


 

Attachments

  • 1758729170239.png
    1758729170239.png
    224.4 KB · Views: 4
I would recommend that when including date parameters in a query they be declared as such, e.g.

SQL:
PARAMETERS Forms!frmDateRange!txtStartDate DATETIME,
Forms!frmDateRange!txtEndDate DATETIME;
SELECT TransactionDate, FirstName, LastName, TransactionAmount
FROM Customers INNER JOIN Transactions
ON Customers.CustomerID = Transactions.CustomerID
WHERE TransactionDate >= Forms!frmDateRange!txtStartDate
AND TransactionDate < Forms!frmDateRange!txtEndDate + 1
ORDER BY TransactionDate;

Firstly note that the parameters are declared as DATETIME. This avoids any possibility of their being misinterpreted as arithmetical expressions. It also caters for differences in the regional date format in which the parameter values are entered into the unbound text box controls.

The second point to note is that the date range is defined as on or later than the start date and less than the date following the end date. This ensures that any rows with a TransactionDate value on the final day of the range which includes a non-zero time of day element will be returned. The possibility of such values existing cannot be ruled out with complete confidence unless specific provision has been made in the table definition to disallow such values. Such rows would not be returned if the range is defined by means of a BETWEEN...AND operation.
 
I will not argue that you don't need to declare the parameter as datetime because in some cases you might. It is never wrong to be precise. But one thing you may find early on is that if the form control is unbound, if you don't specifically add a format property to define the control as a date, Access will not recognize the control as a valid date. I don't know if declaring the data type solves this problem or not or if you still have to set a format property for an unbound control.
 
Declaring the parameters is generally not essential, but I did discuss this with John Viescas many years ago in the context of a post we'd received in the old CompuServe Access forum, in which we were both sysops, where the poster had found that parameters in her local date format were not being recognised correctly. We concluded that declaring the parameters was the best solution, at least for those of us not using US date formats.

PS: I always format such controls as well, as this brings up the date picker.
 

Users who are viewing this thread

Back
Top Bottom