How to format date as mm/dd/yy in form and report? (1 Viewer)

ross6655

New member
Local time
Today, 06:36
Joined
Oct 7, 2020
Messages
7
Hi everyone,

This is my first post here, I've recently started working as a developer and part of my job is managing the countless databases my company has. This has been a great resource, but I'm stuck on a date format issue and can't find an answer by searching.

The issue I'm having is converting a mm/dd/yyyy date to mm/dd/yy. As a preface, I am using Windows and the US date format. This database's main table is linked to a report that runs every month with account information. The data in the table is in mm/dd/yyyy format, which is good. The problem is, my company likes to have the mm/dd/yy format in their reports as it looks cleaner.

There is a form that the user enters data into, and then from there they launch the report. I have set the 'Format' property in the text box's property sheet in both the form and report to 'mm/dd/yy' to reflect this. This works fine until the dates are greater than 2049. For example: 10/07/40 is fine, but 10/07/2050 is not.

I'm sure I'm overthinking it, but the only thing I can think of is using one of the string functions like LEFT, MID, or RIGHT to remove the century from the date if it's present. Any ideas would be greatly appreciated. If I can clarify the question or provide more details please let me know.

Thanks,
Ross
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:36
Joined
Oct 29, 2018
Messages
21,357
Hi Ross. Welcome to AWF!

I guess I'll have to try it out, but I don't get it right now. Can you post a screenshot of your Format property? I've never seen it format some dates but not others. Thanks.
 

Isaac

Lifelong Learner
Local time
Today, 03:36
Joined
Mar 14, 2017
Messages
8,738
It sounds like what you are saying is that when 10/07/2050 is the "true" date, your current set-up is returning to you: 10/07/2050, rather than 10/07/50, which is what you would like. Do I have that right?
If yes, I guess the first thing that comes to mind is the question, are you sure you want that?? There is a reason Y2K concerned everyone! Suddenly dates like 10/07/50 would be meaningless to the whole world of programming and user consumption....

Also, I just want to confirm my understanding that the date column in the Table is actually Date ... Right?

Last question, what is the design (on the column in question), at the query-level....the query that presumably underlies this Report?
Last-last question. :) Can you post a zipped small version of your DB ? Just enough to demonstrate the issue?
 

ross6655

New member
Local time
Today, 06:36
Joined
Oct 7, 2020
Messages
7
Hi Ross. Welcome to AWF!

I guess I'll have to try it out, but I don't get it right now. Can you post a screenshot of your Format property? I've never seen it format some dates but not others. Thanks.
Thanks!

Here's a screenshot of how the dates look in the report. The ones before 2050 look right, but 2050 and later they default to the 4 digit year. And also of the report property sheet for the field in question, maturity date. It is the same in the form.

1602105528563.png
1602105436467.png
 

ross6655

New member
Local time
Today, 06:36
Joined
Oct 7, 2020
Messages
7
It sounds like what you are saying is that when 10/07/2050 is the "true" date, your current set-up is returning to you: 10/07/2050, rather than 10/07/50, which is what you would like. Do I have that right?
If yes, I guess the first thing that comes to mind is the question, are you sure you want that?? There is a reason Y2K concerned everyone! Suddenly dates like 10/07/50 would be meaningless to the whole world of programming and user consumption....

Also, I just want to confirm my understanding that the date column in the Table is actually Date ... Right?

Last question, what is the design (on the column in question), at the query-level....the query that presumably underlies this Report?
Last-last question. :) Can you post a zipped small version of your DB ? Just enough to demonstrate the issue?
Yes that's correct on how the date is returning. I know it sounds counterproductive to display 10/07/50, but I only want it displayed like this in the report that is sent off to clients monthly. I would cringe to see it stored like that in the table.

And yes, the date column in the table is Date/Time. As for the query, it's just a select statement that selects the date, along with a lot of other fields, and displays the data based on user filters. In a whole, it's a complicated query but as for the date part it's simple.

I can potentially post a small version of the DB if necessary.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:36
Joined
Oct 29, 2018
Messages
21,357
Thanks!

Here's a screenshot of how the dates look in the report. The ones before 2050 look right, but 2050 and later they default to the 4 digit year. And also of the report property sheet for the field in question, maturity date. It is the same in the form.

View attachment 85627 View attachment 85626
Very, very interesting... I've never seen that before, thanks! It could be a "bug" by design (in case there's an overlap in centuries - just a guess).

I'll have to ask someone from Microsoft.
 

Isaac

Lifelong Learner
Local time
Today, 03:36
Joined
Mar 14, 2017
Messages
8,738
Can you post the full controlsource of that textbox? i was able to see part of the expression.

I am wondering if it is a combination of your Expression, your textbox TextFormat, and the Format property.

But would prefer you posted the full expression in controlsource before I speculate further.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:36
Joined
Oct 29, 2018
Messages
21,357
Very, very interesting... I've never seen that before, thanks! It could be a "bug" by design (in case there's an overlap in centuries - just a guess).

I'll have to ask someone from Microsoft.
Hi @ross6655,

I just got a reply from someone (not from MS). Try going to your Regional Settings and click the Additional Settings button. From there, you can try adjusting your Calendar settings and let us know what happens.
1602107022144.png
 

ross6655

New member
Local time
Today, 06:36
Joined
Oct 7, 2020
Messages
7
Hi @ross6655,

I just got a reply from someone (not from MS). Try going to your Regional Settings and click the Additional Settings button. From there, you can try adjusting your Calendar settings and let us know what happens.
View attachment 85629
This did it! Thank you so much - and to whoever suggested that.

I'll have to apply the change to the users' who are frequently in the database as they are the ones who generate the reports. But if it worked on my machine, I see no reason why it wouldn't on theirs. (Well, I won't say no reason, because I've seen some weird, weird things in Access)

I'm very grateful, that would have taken me forever to figure out by myself. Hopefully I'll be seeing everyone around more often :)
 

Isaac

Lifelong Learner
Local time
Today, 03:36
Joined
Mar 14, 2017
Messages
8,738
This did it! Thank you so much - and to whoever suggested that.

I'll have to apply the change to the users' who are frequently in the database as they are the ones who generate the reports. But if it worked on my machine, I see no reason why it wouldn't on theirs. (Well, I won't say no reason, because I've seen some weird, weird things in Access)

I'm very grateful, that would have taken me forever to figure out by myself. Hopefully I'll be seeing everyone around more often :)

I created a db with a table, datetime type, format mm/dd/yyyy
I then entered some data in it, including dates in the year 2040, 2050, and 2060+
I then created a form with a textbox whose controlsource was: =Format([datefield],"mm/dd/yy")
I couldn't replicate the problem you are having--even though my Windows date settings are (also) the way it appears yours were, before you changed them. My 2063 dates were willingly displayed by the Textbox as 10/07/63.

Hence, while that may have eliminated the problem, it tells me that whatever I was doing differently that you weren't doing, could also have probably solved the problem on your end (I'm not sure yet what that was, would need more info).

Solving that would avoid the need to have all your users change their operating system date settings ... And generally a design change is better than a client OS change, if possible. But if you're happy I'm happy--glad you got something working.
 

ross6655

New member
Local time
Today, 06:36
Joined
Oct 7, 2020
Messages
7
I created a db with a table, datetime type, format mm/dd/yyyy
I then entered some data in it, including dates in the year 2040, 2050, and 2060+
I then created a form with a textbox whose controlsource was: =Format([datefield],"mm/dd/yy")
I couldn't replicate the problem you are having--even though my Windows date settings are (also) the way it appears yours were, before you changed them. My 2063 dates were willingly displayed by the Textbox as 10/07/63.

Hence, while that may have eliminated the problem, it tells me that whatever I was doing differently that you weren't doing, could also have probably solved the problem on your end (I'm not sure yet what that was, would need more info).

Solving that would avoid the need to have all your users change their operating system date settings ... And generally a design change is better than a client OS change, if possible. But if you're happy I'm happy--glad you got something working
Thanks for the response. That’s strange you got it to work with presumably the same regional settings. I’ll post my control source later tonight or tomorrow morning. It’s an if statement that sets the date field = Null if another field equals “cash”, and otherwise sets it equal to the date in the table. The logic behind this is cash securities have no maturity dates unlike stocks.

I did not try using the Format(field, “mm/dd/yy”), would it be possible to use this in an if statement? I’ll try this - like you said, that’d be a better solution than changing operating system settings. I ended up changing my date settings from 1949 - 2049 to 1990 - 2090.
 

Isaac

Lifelong Learner
Local time
Today, 03:36
Joined
Mar 14, 2017
Messages
8,738
Sure:
=iif([anotherfield]="Cash",Null,format([datefield],"mm/dd/yy"))
 

ross6655

New member
Local time
Today, 06:36
Joined
Oct 7, 2020
Messages
7
Sure:
=iif([anotherfield]="Cash",Null,format([datefield],"mm/dd/yy"))
Thank you! This worked perfect on the reports, I changed my regional settings back to default to make sure.

I couldn’t get it to work on the form, however. The form’s record source is the table itself, while the reports’ record source is a query. Some of the dates on the form are blank, so I need to filter these out. I was either getting a #Name or #Type error depending on what I tried in the control source. I think I need something similar to:
=iif(IsNull([datefield]), Null, format([datefield], “mm/dd/yy”)) but this wasn’t working for me.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:36
Joined
Oct 29, 2018
Messages
21,357
This did it! Thank you so much - and to whoever suggested that.

I'll have to apply the change to the users' who are frequently in the database as they are the ones who generate the reports. But if it worked on my machine, I see no reason why it wouldn't on theirs. (Well, I won't say no reason, because I've seen some weird, weird things in Access)

I'm very grateful, that would have taken me forever to figure out by myself. Hopefully I'll be seeing everyone around more often :)
Hi. Even if you end up not using this, I was very happy you brought up the subject, because I didn't know the Format property was affected by it.

Good luck with your project.
 

Isaac

Lifelong Learner
Local time
Today, 03:36
Joined
Mar 14, 2017
Messages
8,738
Thank you! This worked perfect on the reports, I changed my regional settings back to default to make sure.
Awesome, glad to hear it is working! This way your users won't have to keep adjusting operating system settings based on database domain values/date ranges.

I'm optimistic on the Form too, because actually, a Form was where I tested it (using same operating system date settings that you had originally), and couldn't reproduce the problem. Maybe you could upload a small stripped out version of the table and form in question just enough to demonstrate the issue you are seeing? I'd be happy to look at it if so.
 

ross6655

New member
Local time
Today, 06:36
Joined
Oct 7, 2020
Messages
7
Awesome, glad to hear it is working! This way your users won't have to keep adjusting operating system settings based on database domain values/date ranges.

I'm optimistic on the Form too, because actually, a Form was where I tested it (using same operating system date settings that you had originally), and couldn't reproduce the problem. Maybe you could upload a small stripped out version of the table and form in question just enough to demonstrate the issue you are seeing? I'd be happy to look at it if so.
Good morning.

I've found the problem in the form, but I'm not exactly sure how to fix it. I was getting a Circular Reference error when trying to apply Format to the date text box in the form. This is because the text box has the same name ("Maturity Date") as the table's field which it pulls the data from (table's name is "tbl_account_information", and the date field is also "Maturity Date"). Is there a way to explicitly tell Access to apply the Format function to the date field from the table, and not to itself? Something like [Tables]![tbl_account_information]![Maturity Date]? (I know that doesn't work, but just wanted to give you an example of what I'm trying to accomplish)

I can't change the name of the textbox, as doing so would affect queries downstream. There are a lot of other forms and reports that reference this form.
 

Isaac

Lifelong Learner
Local time
Today, 03:36
Joined
Mar 14, 2017
Messages
8,738
Good morning.

I've found the problem in the form, but I'm not exactly sure how to fix it. I was getting a Circular Reference error when trying to apply Format to the date text box in the form. This is because the text box has the same name ("Maturity Date") as the table's field which it pulls the data from (table's name is "tbl_account_information", and the date field is also "Maturity Date"). Is there a way to explicitly tell Access to apply the Format function to the date field from the table, and not to itself? Something like [Tables]![tbl_account_information]![Maturity Date]? (I know that doesn't work, but just wanted to give you an example of what I'm trying to accomplish)

I can't change the name of the textbox, as doing so would affect queries downstream. There are a lot of other forms and reports that reference this form.
You can go ahead and put one control (txtControl1) as a regular, bound control-bound to the table date field. Hide if you want. Don't let its actual control name be the same as the table date field name.

Then put another control which is not bound to any table field, with the =format() (refer to txtControl1)
 

Users who are viewing this thread

Top Bottom