|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
How to Handle BCE Dates By Richard Rost 
5 years ago
How to Handle BCE Dates in the Distant Past (BCE/CE or BC/AD) In this video I'll show you how to store and display dates that fall outside of the range of a Microsoft Access Date Field. This is necessary for dates BC/BCE, for dates very far in the past (the Founding of Rome, the dinosaur extinction, etc.) or the future. Great for historical events, anthropology, astronomy, etc. Dagbjart from Denmark (a Silver Member) asks: how can I store dates BC or BCE (Before the Common Era) in Access? MembersI'll show you how to create a data entry form which will allow users who are used to typing in "10/31/2021" to still enter dates that way and have them converted to your new fields. Plus we'll display the new date type either 31 AUG 443 BCE or 8/31/443 BCE.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksValidation Rules: https://599cd.com/ValidationRule
IntroIn this video, we will talk about how to handle BCE (Before Common Era) dates in Microsoft Access, which does not natively support dates before the year 100 or after 9999. I will show you my method for storing historical dates using separate fields for day, month, and year, how to display those dates in various formats (including BCE and CE suffixes), and techniques for making your data easier to read and manage. We will discuss field data types, validation rules, and how to use queries to assemble and display custom date formats.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.One thing Access does not do well is store dates that are either before the year 100 or after the year 9999. So, if you have to store BC or BCE dates (dates before the year 1) or dates far in the future or something like the age of the dinosaurs 65 million years ago, Access really does not handle those dates. So, in this video, I will show you my favorite way to store that stuff internally and then how to display it for your users. Today's question comes from Dagbiart, and I apologize if I mispronounced that. I actually looked online to try to find a pronunciation guide and I could not find that specific name. So, Dagbiart from Denmark, one of my Silver members, asks: "How can I store and display dates BC or BCE (before the common era) in Access?" Well, if you are going to use the Date type field in Microsoft Access, valid dates go from the year 100 to the year 9999 CE (common era), or AD if you prefer. That is because Access stores dates as a numeric value internally. The value of zero is December 30, 1899. Why? I do not know; that is just what Microsoft decided to do. So, internally, dates are stored numerically from the number minus 647,000-something to plus about 3 million, and those are days, remember. Internally, Access stores a value of one as a day, which makes it easy for doing date calculations. If you want to see how many days are between one date and another, just subtract the values and you get the number of days. You can multiply that by seven to get weeks. You can do all kinds of easy math to figure out, like, someone's age, and I have other videos on that. So, Access is really good for most dates that we would encounter in our lifetimes—people's dates of birth, their ages, accounting information, that kind of stuff. But if you want to store dates from historic events like the eruption of Vesuvius or the extinction of the dinosaurs, and you want to calculate, for example, the number of years between the founding of Rome and today, those kinds of dates just do not work in Access. So, I did my research. Of course, I Googled to see what else was out there and if anybody else made any suggestions. There is very little out there from other people about storing information in these large historic figures, and most people just suggest, "Well, just store it as text, because you are probably not going to be doing math on it anyway." Well, that is not good for me. I might want to be able to do some calculations and say, "Okay, how many years ago from right now was the founding of Rome?" So, if you want to do math on these numbers, you have to store them in numeric data types. So, what I suggest, and this is the simplest method I could come up with, is I suggest you use three different fields: day, month, and year. Store them in three different fields. The reason why is you might not have all of this information. If you have, for example, my birthday, you know it is October 23, 1972—okay, no problem. Dinosaur extinction: 65 million years ago. So, you are going to put that in, but you do not know the month and the day. The Neanderthals—I just put in there, "What if they were around 70,000 years ago in June?" I think I did that just for an example of how to display just the year and the month. You might only know the year and the month. The first Olympics, for example, 776 BCE, but you do not know the month. But at least if you have the data stored in your table that way, you can assemble it however you want. If you know the date with the year and the month, you can put just the year and the month together. If you know just the year, just display the year. If you have got all three pieces, display that. So, that is what I am going to show you how to do in this lesson. Now, for these three fields, we can use a Byte, which is a number for day and month. Those can be between 0 and 255. You can use Integer if you want to, if you are really cramped for space, if you have got tons and tons and tons and tons of records—tens of thousands of them—then you want to use these smaller data types. But if you want to use Integer or even Long Integer, that is fine; it is not going to kill you. Now, for year, the largest number value you can store is in a Double. Double is pretty big; in fact, that might even be too big. Double can be a number plus or minus 1.8 E308. What does that mean? Well, 1.8 E308 is the same as saying 1.8 times 10 to the 308th power. That is a 1 with 308 zeros after it. So, it is... There is a hundred zeros right there, so it is 3.8 of those, so it is a pretty big number, all right? That is before and after the existence of the universe. The universe is, what, 14 billion years old, so that is a pretty big number. You could probably come down to a Single, which is 10 to the 38th power, and that would be plenty, but we will just use Double. So, here is my blank customer database. You could download a free copy of this template from my website if you want to—there is a link down in the description below. So, let us open this guy up. Let us create a new table to store our dates in—so, Table Design. This will just be an ID—we probably will not even use it—but I like to put an ID in every table just in case. So, we have three bits of information: myYear, which will be a number of type Double (right down there). I do not want a default value of 0, so I would not want to default to the year 0; you may only know the month and the day—you might not know the year. So, this type of system will allow you to store any kind of dates. If you just want to store, for example, someone's birthday as far as the month and day; if you are doing customers and you want to track when to send anniversary information, for example, you might not want to put the year in there. So, let us get rid of that, and therefore year could be null. Next we have myMonth, and I am using myYear and myMonth because year, month, and day are reserved words in Access. Do not use those for your fields. That will be a number of type Byte. That is the smallest kind of number you can have. It is always going to be a value from 0 to 255 (really 1 to 12), and we can delete that default value as well. Same thing for myDay, which is also a number of type Byte. Get rid of that default value. We might want to have a description in here. What is this? Short Text is fine. Let us save this as myDateT—it is a date table. I am storing all my dates in it (or whatever kind of table you want to call it). Let us put some data—let us put some values in here. Okay, we have 1972, 10, 23—that is Richard's birthday. I am just going to copy—I did a little sample database before, so I am just going to copy these over here to save retyping all these. Copy and paste. There we go. We have the dinosaur extinction; that is what I showed you earlier. Neanderthals—I just put month in there, so we have an example that has just the year and the month. The founding of Rome—April 21, 753. James Kirk's birthday, of course—very important date in the future—and the first Olympics. Let us put one more in here: mom's birthday, and let us say you do not know the year, so you are just going to put in here 12, 2. I do know the year, but I am just using that as an example. Now, I have them all apart like this. How do I put them together and display that date how I want to see it? Notice that BCE dates are negative numbers: that is 65 million years ago, for example, or 70,000 years ago. That is the year 2233; if you want to have a big future date, that is fine too. Let us say 5 billion. Let us see—that is a million, right? Billion—5 billion years in the future. Let us get rid of those, and this will be our Sun goes Red Giant, whatever. So, you can have astronomical values in here if you want to—you can have anthropological values in here if you want to—and you can store mom's birthday. I just wiped out mom's birthday, did I? Yeah, I did, I typed right over that—okay, let us do it again: 12, 2, mom's birthday. Now we can use a query to put these three things together to show them the way that you want to see them. So, let us make a query: Create, Query Design. Let us bring in our dateT. It is the only table we need. Close that dateT, close that, okay. Let us bring in the description. I want to put the dates in the format day month year. I think that is the most universal way to display it, especially if you are not sure where the person's country is. Everyone understands it. In the US, the system is month/day/year, then you have most of Europe, it's day/year—it is just confusing. So, when it comes to things like this where you are not sure, I like to go day, then the full month name (or at least a three-digit abbreviation), and then the year. We are going to combine those into one field over here. There is one other thing that I thought of for just a second here—let us put some validation rules in real quick. Let us save what we have got here so far and save this as dateQ. I want to throw a couple validation rules on, primarily because I do not want to have any dates outside of the valid ranges, and there is no year zero. That is important to our calculations here in a second. Let us go to the dateT real quick, and for the year, the validation rule has to be greater than or less than zero. That is, you have 1 BCE, the 1 CE—there was no year zero. For the validation text, you can put in, "There was no year zero," like that. For month, the validation rule should be "between 1 and 12," and then it closes the endpoints with the BETWEEN keyword. For myDay, the validation rule should be "between 1 and 31." Of course, you could put more advanced logic later on in your forms and your After Update events, for example, if you want to make sure that February is not 30 or things of that nature. Of course, tracking leap years is a little more of a tricky calculation, but if anyone really wants to see that, I could do a separate video on it. Now that we have got that set, close that. Save changes, yes. It is just saying that existing data violates the rules that we got in here, but that is okay. So, let us go back to our query. The first thing I want to do is determine whether or not this year here is CE or BCE. That is why I want to make sure we did not have a zero in there, because it would throw off this calculation. Design View. Let us just shrink these up a little bit to save some space down here, since I do not have a gigantic amount of room to work with. We are going to calculate—we are going to call this the suffix, or what goes on the end of the year. This is going to use the IF function, the Immediate IF (IIf). I have got tons of videos on the IF function if you have never used it before. Go watch those videos now. I will put a link down below. The IF function basically says: if this condition is true, then use this value; otherwise, use that value. It is an extremely versatile and powerful function. We are going to say: If myYear is less than zero, then the suffix is BCE; otherwise, it is CE (Common Era). There you go. You can use AD and BC or whatever you want to use. If I run my query now, you can see the proper suffix right there. But I do not want to display the year then with that negative sign, so we need the absolute value of that. So, we go to design view, shrink this up now because we do not need it anymore. Right here, we are going to call this the absYear, which is going to be Abs([myYear]). Abs is a function that takes the absolute value, basically strips off the negative sign. There you go—so there is our year. We have the suffix—let me shrink all this stuff up too. Suffix and the absolute year right there. Now that I have got this, this, this, and that, we can put together our actual date. Design View again: our actual date, I mean. Right here. What is it going to look like? We are going to go day month name year, so we will just call this displayDate or whatever you want. We are going to say: myDay and a space and the month name of myMonth and a space and the absYear and a space and our suffix. I will zoom in so you can see that is what we have so far. It is going to be myDay (so 23rd), then month name (that is a function that returns the month name—so January, February, March, April, whatever), then a space, then the absolute year we developed, and then our suffix. This is all string concatenation. If you have never done string concatenation before, again, I have videos on that—I will put a link down below. Let us see what this looks like now—save it and run it. We have some errors and stuff hanging out in here. What is going on? The first error here is because we do not have a month in there. We are trying to send a null value to the month name function, and that is not a good idea. The rest, though, where we have got a month in there, that works fine. There is my proper value there, and then here we have June—there is an extra space in there. We can deal with that; there is a little bit of a space in front of that, we will have to get rid of that manually. Then we have "2 December CE" there—that is fine too. It is looking good, but we have some changes to make. Let us go back to design view. Let us take care of that month problem first. We have to check to make sure that the month is not null. So, another IF: If IsNull([myMonth]), then put nothing here; otherwise, put the month name and a space (we will put close parenthesis right there). See what I did: I am saying if the month is null, put nothing there—that is an empty string (""). If there is something in there, put the month name and a space. Let us save that and take a peek to see what it looks like. Looks a lot better! Let us get rid of that leading space out there. Design View—this guy here is going to be a little complicated, but that is okay. If there is no myDay (if that is null), then do not put that space there. Now, there is a trick where you can do a little addition here, but I will just use the IF function, so we will do: If IsNull([myDay]), then put nothing there, otherwise put [myDay] and that space. Click OK, save it, and let us take a look. Looks pretty good! We got rid of that leading space, we got our CE, BCEs working. What is next? Most of the time, for dates around our lifetime, we do not need the CE. If I see "23rd October 1972," I am going to assume that is CE. So, why do we not get rid of that? You might want it for dates that are small, though. Well, if I put in here the end of the Roman Empire, that was—what do I have it in here somewhere?—that is 476 CE, so I am just going to put the year 476 in here, and for that, you would want to see 476 CE, right? The end of the Roman Republic, that was in 29 BCE, so negative 29. There you go. In those low dates like that, you want to see that, but for most modern dates, you do not need to see that suffix. So, I am going to say everything from the year 1000 until the year 3000, let us just not put a suffix on it. How does that sound? So, let us go in here—Design View. For my suffix right here, I am going to say, if myYear is less than zero, it is always BCE; otherwise, we are going to throw another IF in here. This is a nested IF function. Ready? If myYear is between 1000 and 3000, put nothing in there; otherwise, put CE in there. Click OK; let us take another peek to make sure. So, if myYear is less than zero, we always get a BCE; otherwise, if myYear is between 1000 and 3000, put nothing in there; otherwise, put CE in there. So it is 452 CE or 4562 CE, so you know. Let us see what that looks like now—save and go. Oh, beautiful! 23rd October 1972—no CE at the end, but 776 BCE shows correctly, 476 CE—see, that is perfect. By the way, if you want just the three-digit month in there, if you do not want that full guy, you can use the LEFT function. There are all kinds of functions we can throw in here to make this really cool. Let us go back into our displayDate function—right, instead of the whole month name, we could just go: Left(MonthName([myMonth]),3). Click OK and run it—see, "23rd Oct," "21 Apr," and it is always three digits: Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct. Good, they all work out that way; it is always the first three characters. If you want that capitalized, throw a UCase in there. I am not going to do it, but you get the point. UCase will convert to all uppercase. I have videos on that—I will put links down below if you want to learn about LEFT and UCase and the rest. Now, one more thing I want to do: For normal, again, dates in our lifetime, that four-digit year format is perfectly fine—right, 1972. But if I am looking at that number, it is kind of hard to read; I have to sit here and carefully count the zeros. I cannot easily tell it is a billion. So, I am going to say, for any number that is larger than 10,000 or more, let us put a comma in the format. Okay, last thing we are going to do with this: So, one more time, let us make this function even bigger here, this formula. Right around absYear here, I am going to say: If Abs([myYear]) is greater than 9999, then format absYear as "0,000", otherwise just use absYear. Let me just hit OK, make sure I typed it all in right—okay, good. Make sure it works—works just fine! Now I can very clearly and easily see 65,000,000 BCE, 70,000 BCE, 5,000,000,000 CE. Looks like we have one extra little space in there somewhere. We have to put a space in there. Let me explain this real quick, and then we will take a look at that space problem. This is saying, if the absYear is greater than 9,999, format it with the thousands separator. So, 1,000 would show as "1,000", but I said only do that if the number is 10,000 or more. Otherwise, just show it as a normal four-digit year like we are used to. Now, over here, we have the same problem. We are seeing the extra space because we are missing the year. If the year is missing, then we do not want that space there. So, we have a space coming in after the month and also after the year. We just need to tack that inside of here. We need to put this whole thing inside of an IF IsNull to get rid of that extra space: If IsNull([absYear]), then nothing; otherwise, that whole thing and that space. Save it, and that problem is fixed. There are your years. Now that is how you display it. Have the user enter it in three parts like that, and then display it like this. You would just use this dateQ query on all of your forms and your reports, because as you can see, this query is still live. As long as you have only got data from one table in here, and no aggregates or grouping or any of that crazy stuff, you can still display these values and still add, enter, and edit and delete data from the table. Remember, this is linked directly to the table. So, you can use this dateQ now in your forms. You are still entering data in these fields, and then you can use this field on there—maybe gray it out so they cannot enter it—to display that. This will now work very well in your reports as well. Want to learn more about this stuff? Well, for the members, in the Extended Cut, we are going to build a data entry form. Now, for crazy dates like BCE dates and all that stuff, you have to tell your people to type it in over here, but the majority of dates that people are usually working with are normal dates. So, we are going to make a second form here, a second field for a normal date entry. If you have got birth dates, or even something that is not too far in the future, like James Kirk's birthday, for example, you could type it in here in a normal date format, and our system will copy it over here and convert it. Plus, we will add a US date field like this if you want to see it in this format instead of this format. I will do that for you as well. That is in the Extended Cut for members. Silver Members and up get access to all of my Extended Cut videos. We are approaching a hundred of them, so there is lots of material for you to watch. How do you become a member? Click on the Join button below the video. Silver Members and up will get access to all of my Extended Cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks. But do not worry, these TechHelp videos are going to keep coming; as long as you keep watching them, I will keep making more, and they will always be free. If you enjoyed this video, please give me a thumbs-up and feel free to share it wherever you think it might help people who are interested in Access. Make sure you subscribe to my channel, which is completely free, and click on the bell icon to select All to be notified every time I post a new video. YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link below to join my mailing list. Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access. It is over three hours long, and you can find it on my website or on my YouTube channel. I will include a link below you can click on. If you like Level 1, Level 2 is just one dollar, and that is free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. If you have a specific problem you need help with or you would like to discuss having a database built for your needs, I do offer one-on-one consulting. Be sure to follow my blog and find me on Facebook, Twitter, and of course YouTube. Once again, my name is Richard Rost. Thank you for watching this TechHelp video brought to you by AccessLearningZone.com. I hope you enjoyed this video and you learned something today. I will see you again soon. QuizQ1. Why does Microsoft Access have limitations when storing BC or BCE dates and very far future dates?A. Because Access only stores dates as text B. Because Access only allows positive number storage C. Because Access stores dates internally as numbers that only cover years 100 to 9999 D. Because Access does not support date fields Q2. What is the internal numeric value representing December 30, 1899, in Access? A. 1899 B. 0 C. 9999 D. 100 Q3. Why might storing historical or astronomical dates as text in Access be a problem? A. Text takes up too much storage space B. You cannot do math or calculations on text fields easily C. Text fields are not user-friendly D. Access does not allow text fields Q4. What solution is recommended in the video for storing dates where BC, BCE, or large future dates are needed? A. Use a single text field B. Use a single numeric field C. Store day, month, and year in three separate fields D. Use only the year field Q5. What is the recommended data type for storing the year, especially if very large or small numbers are needed? A. Byte B. Integer C. Double D. Short Text Q6. What should you avoid using as field names for year, month, and day in Access? A. myYear, myMonth, myDay B. reserved words like year, month, and day C. Short Text fields D. Numeric fields Q7. What is the maximum value a Byte data type can store in Access? A. 10 B. 256 C. 127 D. 255 Q8. How should BCE (or BC) years be represented in the recommended system? A. As positive numbers B. As negative numbers C. As text values only D. Using a separate field Q9. For displaying dates, how does the video suggest combining the separate fields? A. Concatenate day, month name, year, and suffix in a query B. Store the entire date as text C. Use only the year field D. Use just the day and month fields Q10. What function is recommended to create the correct CE or BCE suffix in the display? A. MonthName B. Abs C. IIF (Immediate IF) D. SUM Q11. Why is it important not to have a year value of zero in this system? A. Year zero is considered a valid date B. Leap years cannot be calculated C. There was no year zero in the historical dating system D. Access does not accept zero as a valid value Q12. What validation rule is suggested for the month field? A. Month > 0 B. Month between 1 and 12 C. Month >= 0 and <= 31 D. Month is not null Q13. How is the absolute year value calculated for display purposes? A. Using the Left function B. Using the Abs function on the year field C. Using SUM D. Manually typing the value Q14. To prevent display errors when the month or day is missing, what should be checked? A. Whether the value is less than zero B. Whether the value is text C. Whether the value is null D. Whether the value is greater than 12 Q15. When should the CE/BCE suffix be omitted in the display, according to the tutorial? A. For all years B. For years between 1000 and 3000 C. Only for BCE years D. For years less than zero Q16. What Access function is recommended for getting a month's name from a number? A. YEAR B. DAY C. MonthName D. LEFT Q17. How does the video suggest formatting large year values (10,000 or more) for readability? A. Using text formatting only B. Formatting the year with a comma separator C. Changing the year to text D. Using only the first digit Q18. Why is using a query important for displaying the formatted date in forms and reports? A. Queries cannot be edited B. It allows combining and formatting fields for display while keeping data entry in separate fields C. Queries are faster than tables D. Only queries allow numeric calculation Q19. Why is it suggested not to use aggregates or grouping in the display query for easy data entry? A. Queries with aggregates can only display data B. Aggregates make reports confusing C. So the query remains updatable for easy data entry and editing D. Aggregates use too much memory Q20. What should you do if you want to display only the three-letter abbreviation of the month? A. Use the RIGHT function B. Use the UCase function C. Use the LEFT function on the MonthName function D. Use the SUM function Answers: 1-C; 2-B; 3-B; 4-C; 5-C; 6-B; 7-D; 8-B; 9-A; 10-C; 11-C; 12-B; 13-B; 14-C; 15-B; 16-C; 17-B; 18-B; 19-C; 20-C DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone addresses an important limitation in Microsoft Access: handling dates that fall outside the typical supported range, specifically those before the year 100 or after the year 9999. This is especially relevant when you need to capture historical events, like BCE or BC dates, or even dates as ancient as the dinosaur extinction. Access simply cannot natively store these types of dates using the standard Date field.A question came in from a student in Denmark, asking about the best way to store and display dates from before the common era in Access. For reference, the standard Date type in Access only supports years from 100 to 9999 CE. This is because Access stores dates as numbers, with the value zero set to December 30, 1899, and each increment or decrement representing a single day. This system makes typical date calculations straightforward, but it falls short for historical or far-future dates. Most resources recommend storing such dates as text, given you probably won't need to do date arithmetic. However, that reduces flexibility, especially if you want to perform calculations such as determining how many years ago something happened. For these reasons, I suggest a more structured approach: use three separate fields to store the year, month, and day. By separating these values, you gain the flexibility to enter as much or as little information as you know. If you know the exact date, you can fill all three fields. For something like the age of the dinosaurs, where you may only know the year (or even just an estimate), you can omit the month and day. Similarly, if you only know the month and year, you have that option as well. This format grants accuracy, flexibility, and supports subsequent calculations or formatting. For storage, use a numeric data type for each field. Day and month fields can use the Byte data type, which covers values from 0 to 255 and suffices for the limited ranges those entries require. For the year, Double is effective because it handles a vast range of numbers, far more than you likely need, and supports negative values for BCE or BC dates. While Single or Integer could also work depending on your needs, Double provides the broadest range. I demonstrated these concepts in a sample database by creating a table with three number fields: myYear (type Double), myMonth (type Byte), and myDay (type Byte). Choose field names that avoid reserved words in Access—using myYear instead of year, for instance. The table can store entries such as a modern birthday, historic events, or estimated dates separated by their known parts. After entering sample data—ranging from everyday birthdays to the extinction of the dinosaurs and the first Olympics—it becomes a matter of how you choose to display this information to users. For future and ancient dates, the year may be extremely large or negative. For missing month or day information, the relevant field is left null. To present these dates in a readable format, you can build a query that combines the three fields into a single string. I prefer the "day month year" format, as it is widely understood regardless of cultural differences in date notation. In the query, use immediate if statements (IIf) to determine correct suffixes (BCE or CE) depending on whether the year is negative or positive. Negative years indicate BCE, while positive years signify CE. Years are displayed as their absolute values instead of showing a negative sign. For formatting month names, you can use functions that translate the numeric month into its name, or use only the three-letter abbreviation if desired. String concatenation lets you piece together the day, month, and absolute year, followed by the appropriate suffix. Take care to only include the parts you actually have—for instance, if the month is missing, don't display an empty space or an incorrect value. Validation rules are also important at the table level. For the year, ensure you don't allow a zero value, since there was no year zero between BCE and CE. Set the month to only allow values between 1 and 12, and the day between 1 and 31. If you wish, you can add more advanced logic in forms to further refine date validation for things like leap years or months with fewer days. Handling formatting nuances is also important for a professional appearance. For instance, years above 9999 may benefit from including a thousands separator (for example, showing 65,000,000 BCE in a more readable way). Likewise, if either month or day is null, make sure your output formatting does not include extraneous spaces or misplaced delimiters. In most use cases, you might not need to display the "CE" suffix for modern dates—if you see "October 23, 1972," most will assume it refers to the common era. But for ancient or historic dates, especially around the BCE/CE transition, it is important to include it. By using nested IIf statements in your query, you can control when suffixes appear based on the value of the year. Once you have your display formatting query set, you can use this query as the record source for your forms and reports. This approach ensures that users always see properly formatted dates, regardless of how much information you entered, and can still edit the underlying data as needed. In the Extended Cut for members, I show you how to build a form for entering these dates efficiently. I cover how to handle unusual dates and also demonstrate how to accommodate normal date entry with automatic conversion, alongside offering options for regional date formatting. If you are interested in accessing this additional content, becoming a Silver Member or higher grants you access to a wealth of extended lessons, covering advanced techniques and practical database solutions. If you have not taken my free Access Level 1 course, I highly recommend it. It covers the foundational aspects of Microsoft Access and is free to everyone. For a dollar, you can continue on to Level 2, which is also complimentary for channel members at any level. If you have questions or would like your topic featured in a future video, submit your question via the TechHelp page on my website. For those with specific needs or who want to discuss a custom database project, I offer consulting services as well. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListAccess date type year limits and internal storageLimitations of Access for BCE dates and large historic dates Storing dates with separate day, month, year fields Choosing appropriate data types for date components Allowing for unknown parts of dates (partial dates) Handling negative years for BCE dates Creating a date table in Access for custom date storage Manually entering example historic and future dates Validating date components with field validation rules Using queries to format date output from separate fields Determining and displaying BCE or CE date suffixes Removing negative sign for BCE years using Abs() String concatenation to build formatted date display Conditional logic for displaying month and day names Suppressing unwanted spaces in formatted output Conditionally displaying BCE, CE, or omitting suffix for modern dates Displaying abbreviated 3-letter month names Formatting large year numbers with thousands separators Handling null values to avoid display errors Using queries for user-friendly date display in forms and reports |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access handling dates in BC in access, how to represent a BC date, very old dates, bc, ad, bc vs ad, b.c., a.d., bce dates, bc dates, ce dates, ad dates, anthropology, astronomy, historic events PermaLink How to Handle BCE Dates in Microsoft Access |
