I have a date field in a form that is designed to return last month's date in m/yy format eg in April 2001 it should automatically come up with 3/01. The expression used for this field was DateAdd("m",-1,Date()). This worked well until February 2001 and up till then only returned a value as illustrated above. Since February 1st 2001 the value shown is OK but if I click into the field on the form the day also shows up eg 01/02/01. How do I get it to leave out the day and what would have caused this to happen?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
DateAdd function 5
- Thread starter zabawa
- Start date
LonnieJohnson
Programmer
Check your format and input mask properties for this field in the table and the form. ljprodev@yahoo.com
Professional Development
MS Access Applications
Professional Development
MS Access Applications
raskew
Programmer
- Oct 18, 2000
- 488
? format(DateAdd("m",-1,date()), "mm/yy"
should get it.
Don't know what country you're in, or the date format you're using, but it's my understanding that most of the Access date functions operate on the U.S. short date format (mm/dd/yy).
Could this be a factor?
Bob
should get it.Don't know what country you're in, or the date format you're using, but it's my understanding that most of the Access date functions operate on the U.S. short date format (mm/dd/yy).
Could this be a factor?
Bob
-
5
- #4
raskew's suggestion returns a string that you can store in a Text field and show in your form, but if you're using a Date/Time field, as I expect you are, LonnieJohnson's suggestion was to change the Format property of the form control.
Most likely, the control's Format property was changed somehow. The DateAdd function always returns a Date value, complete with the day, so I'd have to say you've been getting the day all along, but it was being hidden by the Format property of the control. Rick Sprague
Most likely, the control's Format property was changed somehow. The DateAdd function always returns a Date value, complete with the day, so I'd have to say you've been getting the day all along, but it was being hidden by the Format property of the control. Rick Sprague
- Thread starter
- #5
Thanks very much to all for your suggestions. I am indeed using a Date/Time field but don't know how I should change the Format property of the form control. I tried raskews suggestion which was great but this returned US format so instead of getting 3/01 I got 1/01 (with the month number hidden until I click in the field). Oh,I forgot to say I'm in England. My current setting returns UK format in the same way except the hidden day is the current one, not the first of the month as in raskews format. What I don't understand is why records I created prior to 2001 don't have the day hidden and only show up as m/yy. Problems arise if I put some records in one day and the rest of a set of records in another day, then the query I run from them produces 2 different values.
Hope all this makes sense. Sorry to be so dense about all this but am relatively new to Access.
Zabawa
Hope all this makes sense. Sorry to be so dense about all this but am relatively new to Access.
Zabawa
Uh oh! I'm pretty sure your database has the wrong dates stored for this year. And I think I know why. This is going to be complicated.
You see, beginning in 2001, dates entered with 2-digit years are more ambiguous than previously. The year is now 01, which is also a valid month and day, so March 2nd, 2001 comes out 03/02/01 or 01/03/02 or 02/03/01--all of which can be interpreted many different ways. Prior to this year, at least the year part of the date (00 or 99, etc.) was unambiguous, since there is no day or month 00, and that limited the ways Access could interpret the date. I think Access has been misinterpreting the dates entered in this column since the start of the year.
One thing is certain: In the example you just cited (getting 1/01 instead of 3/01), the stored date is in January, not March. It's not the month that's hidden, it's the day. It's not that the displayed format is wrong, it's that the stored date is wrong. The stored date can only be wrong because an ambiguous date was incorrectly interpreted.
You said the value of this column is being calculated as DateAdd("m", -1, Date()). The resulting value is an internal Date value, which is never ambiguous (only formatted dates can be ambiguous), so that's puzzling. But I'm going to guess that at some point, this date value is being converted to a string, probably using the Format("MM/yy",...) function, and that string is then being stored in the date field. That will cause your problem, but it's complicated to explain.
Suppose you did just that, you used the Format() function to take that calculated date value, convert it to a string, and assign it to a form control which is bound to the date/time column in the database. When you assign to a bound control, you're really assigning to the underlying bound field. In this case, that's an internal Date variable. But because you're providing a string, VBA has to guess how to interpret the string. Before this year, VBA could tell that one of the numbers in your string was a year, so it knew the other was a month (because dd/yy isn't an option). But starting in 2001, the string contains something like 03/01. To VBA, this could be March 2001, March 1st, or January 3rd. Which of these it picks depends on the database's default locale, which was taken from the Control Panel Regional Settings of the system the database was created on (and may or may not match the settings on your system). I don't know what the interpretation would be in your case, but I'm pretty sure it's not March 2001, which is what you intended.
If my scenario is correct, you could fix this problem by simply removing the formatting before assigning the calculated date to the form control (or more accurately, to its bound field). Use the control's Format property to specify the format to be displayed ("MM/yy", instead of using the Format() function. If for some reason removing the formatting would cause you a problem in your code, at least change the Format() function argument to "MM/yyyy", that is, a 4-digit year; that will disambiguate the string so VBA will interpret it correctly.
Unfortunately, your database already has incorrect dates stored in it. You need to open your table and correct them manually, probably using a 4-digit year.
A lot of Access/VBA programmers don't realize that you can't store a formatted date or a partial date in a date/time column. They think that if they want an mm/yy format in their application, they have to format the date as mm/yy before it's stored, so they do something like <date/time field> = Format("MM/yy", <some date>). This is useless and inefficient, since it involves converting a date value to a string, and then converting that back to a date value. It's also dangerous sometimes, as in this case, because the string loses some information about the date, which leads to your kind of problem.
In reality, formatting should only be done to dates as they're about to be displayed. All internal manipulation should occur with full dates. By following this policy, you minimize the opportunity for misinterpretation to occur.
I recommend that you think about this enough to really understand it well. From now until 2013, the first 12 days of every month have highly ambiguous short date formats, so you'll be dealing with this kind of problem for years to come. The ambiguous date problem is going to be a fact of life for a long time. It's definitely worth learning to avoid it. Rick Sprague
You see, beginning in 2001, dates entered with 2-digit years are more ambiguous than previously. The year is now 01, which is also a valid month and day, so March 2nd, 2001 comes out 03/02/01 or 01/03/02 or 02/03/01--all of which can be interpreted many different ways. Prior to this year, at least the year part of the date (00 or 99, etc.) was unambiguous, since there is no day or month 00, and that limited the ways Access could interpret the date. I think Access has been misinterpreting the dates entered in this column since the start of the year.
One thing is certain: In the example you just cited (getting 1/01 instead of 3/01), the stored date is in January, not March. It's not the month that's hidden, it's the day. It's not that the displayed format is wrong, it's that the stored date is wrong. The stored date can only be wrong because an ambiguous date was incorrectly interpreted.
You said the value of this column is being calculated as DateAdd("m", -1, Date()). The resulting value is an internal Date value, which is never ambiguous (only formatted dates can be ambiguous), so that's puzzling. But I'm going to guess that at some point, this date value is being converted to a string, probably using the Format("MM/yy",...) function, and that string is then being stored in the date field. That will cause your problem, but it's complicated to explain.
Suppose you did just that, you used the Format() function to take that calculated date value, convert it to a string, and assign it to a form control which is bound to the date/time column in the database. When you assign to a bound control, you're really assigning to the underlying bound field. In this case, that's an internal Date variable. But because you're providing a string, VBA has to guess how to interpret the string. Before this year, VBA could tell that one of the numbers in your string was a year, so it knew the other was a month (because dd/yy isn't an option). But starting in 2001, the string contains something like 03/01. To VBA, this could be March 2001, March 1st, or January 3rd. Which of these it picks depends on the database's default locale, which was taken from the Control Panel Regional Settings of the system the database was created on (and may or may not match the settings on your system). I don't know what the interpretation would be in your case, but I'm pretty sure it's not March 2001, which is what you intended.
If my scenario is correct, you could fix this problem by simply removing the formatting before assigning the calculated date to the form control (or more accurately, to its bound field). Use the control's Format property to specify the format to be displayed ("MM/yy"
, instead of using the Format() function. If for some reason removing the formatting would cause you a problem in your code, at least change the Format() function argument to "MM/yyyy", that is, a 4-digit year; that will disambiguate the string so VBA will interpret it correctly.Unfortunately, your database already has incorrect dates stored in it. You need to open your table and correct them manually, probably using a 4-digit year.
A lot of Access/VBA programmers don't realize that you can't store a formatted date or a partial date in a date/time column. They think that if they want an mm/yy format in their application, they have to format the date as mm/yy before it's stored, so they do something like <date/time field> = Format("MM/yy", <some date>). This is useless and inefficient, since it involves converting a date value to a string, and then converting that back to a date value. It's also dangerous sometimes, as in this case, because the string loses some information about the date, which leads to your kind of problem.
In reality, formatting should only be done to dates as they're about to be displayed. All internal manipulation should occur with full dates. By following this policy, you minimize the opportunity for misinterpretation to occur.
I recommend that you think about this enough to really understand it well. From now until 2013, the first 12 days of every month have highly ambiguous short date formats, so you'll be dealing with this kind of problem for years to come. The ambiguous date problem is going to be a fact of life for a long time. It's definitely worth learning to avoid it. Rick Sprague
A follow up: I should have checked my facts before posting, but it was long and I was hurrying to finish.
The interpretation of ambiguous dates entered in a form or table depends on the user's Control Panel Regional Settings, not on the locale setting of the system it was developed on. I was confusing it with the database default collating order, which is language-specific, and is set when the database is created.
I think the bulk of my analysis of the problem still applies, nevertheless.
Terry--good idea! I see people here using Format() in the wrong place all the time; an instructional FAQ will at least avoid having to explain why it shouldn't be used all the time. I'll stick it on my list of things to do. Rick Sprague
The interpretation of ambiguous dates entered in a form or table depends on the user's Control Panel Regional Settings, not on the locale setting of the system it was developed on. I was confusing it with the database default collating order, which is language-specific, and is set when the database is created.
I think the bulk of my analysis of the problem still applies, nevertheless.
Terry--good idea! I see people here using Format() in the wrong place all the time; an instructional FAQ will at least avoid having to explain why it shouldn't be used all the time. I'll stick it on my list of things to do. Rick Sprague
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question