Type Conversion
By Richard Rost
3 years ago
Type Conversion Functions in Microsoft Access
CBool, CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CStr, CVar
In this Microsoft Access tutorial I'm going to teach you how to take text from one field in your database and convert it to another format like a date using the built-in type conversion functions.
Charlotte from Raleigh, North Carolina (a Platinum Member) asks: I get a spreadsheet from one of my suppliers and dates are formatted as YYYYMMDD. After I import them into my database, they come in as text. How can I get them into a normal date field? If I try importing them into a date field, I get a "type conversion failure" error. Help!
Prerequisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Type Conversion Functions, CBool, CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CStr, CVar, Left, Right, Mid, Format, Converting Data Types, Importing From Text File, Type Conversion Failure, convert text to date, convert text to number, fixing date/time import error, import spreadsheet
Intro In this video, we will talk about type conversion functions in Microsoft Access, focusing on how to convert text data imported from sources like spreadsheets into actual date values. I will show you how to use string functions such as Left, Right, and Mid to reformat dates stored as text, and then use the CDate function to turn them into valid date fields Access can work with. We will also go over the various type conversion functions available in Access, discuss why conversion errors occur, and demonstrate these concepts with a step-by-step query example.Transcript Today we are going to learn about type conversion functions in Microsoft Access, why they are useful, and what you might need them for.
Today's question comes from Charlotte in Raleigh, North Carolina, one of my Platinum members. Charlotte says, I get a spreadsheet from one of my suppliers and dates are formatted yyyyymmdd. I see this a lot. After I import them into my database, they come in as text. How can I get them into a normal date field? I try something, I try importing them directly into a date field, I get a type conversion failure error. Help.
That is going to happen because Access does not recognize that as a valid date format. So we just have to do a little massaging with some string functions to make that look like a normal date and then actually convert it to a date using one of the type conversion functions.
What are the type conversion functions? Well, that is these guys: CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CStr, CVar. Say that 10 times fast. Those are all the different data types that you can convert to in Microsoft Access and these work in your queries, forms, and reports. These also work in VBA and pretty much everywhere.
Now this is going to be an expert-level class. I consider expert to be between beginner and developer. So we are not going to do any VBA programming today, although you can use these functions in VBA. But you should know a little bit more than the basics. You should know how to use calculated fields in queries. If you do not, watch this video. You should know how to use the string functions to get pieces of a string. In this video, we are going to use Left, Right, and Mid to get pieces of that date and align them correctly.
And I am going to be using the ISO date format, which, Charlotte, your date is coming in very close to this. This is the best date format. No matter where you are on the planet, ISO makes the most sense: year, month, day.
You do not have to worry because I am in the US. I have a lot of students that are in the UK, and you guys do day, month, year, which does make more sense than the US version of month, day, year. To beat around all those problems, I switched to the ISO date format a while ago. This is the best thing to use. I use it. You should use it. Go watch this video.
All of these are free videos. They are on my website and on my YouTube channel. Go watch them if you do not know any of this stuff and then come on back.
Here I am in my TechHelp free template. This is a free database you can download off my website if you want to. Let's just take any one of these tables. It does not matter. Let's use the Order table.
Let's just put a new text field in here that will represent what we imported from our spreadsheet. I will call this ImportedDate, and that will be a short text field.
Because what happens a lot of times when you import data, like from Excel or a text file or whatever, if Access does not see that as a valid date, it will import it as text. And that is fine. Leave it as text in your database, and then we will just massage it and convert it over to a date value.
Save that. Let's go into here and put some actual dates in here the way that they would come in from our sheets. So we will do 20231023, 20210101, 20221231, whatever. Put a couple of things in here: 20200506. Make sure they are valid dates, of course; 19990708; all right. So that is how it is being imported from our spreadsheet. Save that. Close it.
Let's make a query. Create Query Design. I am just going to bring in that Order table. Bring in our field we just made: ImportedDate.
Now, so I do not have to keep referring to it as ImportedDate, I am just going to call it D. Okay. That is going to alias that. That is what it looks like.
If I run that real quick, that is what we have. Let's get rid of the null values. So we will come down here for criteria and say Is Not Null. If you do not know what null is, I have a whole separate video on that; I will put a link down below. But now if we run it, we do not see any values that are missing dates.
What is the first step? The first step is we are going to use the string functions to rearrange this and display it like a normal date—a date that Access will recognize as a valid date.
Since I am using the ISO date format, it is pretty much in the same order. I just need the left four characters, and that will represent the year, then the middle characters starting at five and going two across. We will use the Mid function for that. Then the right two. If you are using a different date format, if you are in the United States, you will just put this first and then that and then the year. Same thing for European standard.
So we are going to come over here, and I am going to zoom in so you can see this better.
We are going to make a string date. We will call it SD, my string date. That is going to be Left(D, 4) and a dash. I am going to use a dash because I am using the ISO again. Mid(D, 5, 2), go five across and give me two characters. The fifth character is where it starts. Then another dash and Right(D, 2).
That is how you build your date.
We are going to save it as ConvertDateQ, and let's give it a good run. There we go. Now you can tell it looks good, but you can tell it is a string. How can you tell it is a text string? Because it is lined up on the left side of the cell. Numeric values, including dates, numbers, and currency, generally line up on the right side of the cell.
It is not a guarantee because there could still be formatting involved. I know one of my friends, John, just had an issue where it looked like it was text, but it was a number because it was lined up the other way, but that was just because of formatting in a form. You can also format queries too.
So it is a general rule that text is on the left, numeric values line up on the right, but there are exceptions.
Now that we have that as a text value, we cannot do anything to it. We cannot use it as a date in our other query criteria. We cannot add values to it. So now we have to convert that string to an actual date value that Microsoft recognizes as a date.
So in the next column, we are going to say NewD, my new date or just NewDate, whatever. The last one, when I am just using these as intermediary steps, I just give them the initials because they are easier to use in other functions. When I get to the last one, I try to give it a good name if I am going to be using this in my other stuff.
So NewDate is going to be, now I need to convert that other date that I just built from text into a date type. So it is going to be CDate as my conversion function. What am I converting? SD. Take SD, which is a string, and convert it to an actual date value.
Save it. Run it. There you go. Notice it lines up on the right side of the cell—cell; this is in Excel, folks—the right side of the field.
How can we test that and make sure it is a date? Let's just add one to it. Next field over here, we will call it AddOne, whatever you want to call it. This is going to be NewDate plus one.
As we know from our date math, if you take a date value and add one to it, that adds a day. If you add seven, that adds a week, but we will just add one.
Save it. Run it. You can verify—there you go. Check this one out right here. December 31, 2022 becomes January 1, 2023. That is a date value.
Now that it is fixed, all you have to do is create an update query. If you want to keep it in your Order table, you can add another date field, call it NewDate2, whatever you want to call it—CorrectedDate, whatever. Give it a good name. Add it as a date field. Then use an update query to update that field with this value.
If you have never done an update query before, I have a video for that too. I will put a link to that down below as well.
That is pretty much how the conversion functions work. In this one, I just used CDate.
This is PowerPoint. So you can use all the rest. When you want to convert to Currency, if you are dealing with a Boolean (yes/no value), you are dealing with a Double, you can use these different conversions to go to whatever format you need.
I almost never use some of them like CVar, CSng. I do not use Singles. I do not use Decimals that often. I do not use Bytes that often. But the ones you are going to pretty much use a lot are convert to Currency, convert to Date, convert to Long, which I use a lot, and convert to String if you want to take a number and make a string out of it.
If you want to learn more about these type conversion functions, I cover it in my Access Expert Level 26 class. This is part two of my comprehensive guide to Access functions. You can see there are all the type conversion functions. We cover them and a lot more about this stuff. We use some trigonometry in this one. We do some cool stuff in this one.
I have a whole series of Access functions. Part one starts in Access Expert 25. We use string functions, logical functions. Then we do the math functions, type conversion functions, date/time functions, all kinds of stuff. I go through all the different parts. There is part three, part four does the date/time (part two), there are aggregate functions, all kinds of good stuff. Part six, looks like it is a six-part series. There are lots and lots. If you want to learn about Access functions, I cover all the functions just like I do with my Excel classes. I cover all the different functions that are available, so check it out.
Of course, members get discounts on all my classes and some free classes too.
There is your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends. I will see you next time.Quiz Q1. Why does Access produce a type conversion failure error when trying to directly import dates formatted as "yyyyymmdd" into a date field?
A. Access interprets the format as a number, not a date
B. Access recognizes only the US format "mm/dd/yyyy"
C. Access does not recognize "yyyyymmdd" as a valid date format
D. Access automatically converts all text to dates
Q2. What is the ISO date format?
A. day, month, year (ddmmyyyy)
B. month, day, year (mmddyyyy)
C. year, month, day (yyyymmdd)
D. day, year, month (ddyyyymm)
Q3. When importing data containing dates in an unrecognized format, how does Access typically store these dates?
A. As numeric values
B. As short date values
C. As text fields
D. As long date values
Q4. Which sequence of string functions would you use to split "20231023" into its year, month, and day components?
A. Left, Right, Mid
B. UCase, LCase, Trim
C. Replace, Format, Trim
D. Len, Instr, Chr
Q5. After reconstructing a "normal" date string (like "2023-10-23") from text, why does Access still treat it as text?
A. All new fields are text until saved as another type
B. Adding dashes automatically makes it text
C. Formatting with dashes is not recognized as a date
D. The data type is still text unless explicitly converted
Q6. Which function would you use in Access to convert a string to an actual date value?
A. CStr
B. CDbl
C. CDate
D. CLng
Q7. What is a characteristic way to tell if a value in an Access datasheet is being treated as text?
A. It appears bold
B. It is left-aligned in the cell
C. It is always red
D. It has a special font
Q8. In the conversion example, after using CDate to create NewDate, what happens if you add 1 to this date value?
A. It adds one month
B. It subtracts one day
C. It adds one day
D. It results in an error
Q9. Which type conversion function would you use to convert a value to currency in Access?
A. CInt
B. CCur
C. CStr
D. CDbl
Q10. When working with imported data and converting text dates, what is the recommended practice before performing the conversion?
A. Leave the data as numbers
B. Remove all non-numeric characters
C. Make sure the text matches a recognizable date format
D. Change the field type to currency
Q11. Which of the following is NOT a type conversion function in Access?
A. CByte
B. CDec
C. CVDate
D. CLng
Q12. After converting and validating your dates using a query, how can you save these updated values into your main table?
A. By exporting the query to Excel
B. By creating an update query
C. By deleting all the original records and reimporting
D. By copying and pasting manually
Q13. If you want to convert a number to a string in Access, which function would you use?
A. CDec
B. CInt
C. CStr
D. CDate
Q14. Which of the following statements about alignment in Access datasheets is TRUE?
A. Text typically aligns left, numeric values align right
B. Dates always align in the center
C. All values align to the left by default
D. Formatting never affects alignment
Q15. What benefit does the ISO date format provide when working with international databases?
A. It is recognized by all US-only databases
B. It avoids confusion by using a universal year-month-day order
C. It sorts dates alphabetically
D. It is compatible with only European systems
Answers: 1-C; 2-C; 3-C; 4-A; 5-D; 6-C; 7-B; 8-C; 9-B; 10-C; 11-C; 12-B; 13-C; 14-A; 15-B
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.Summary Today's TechHelp tutorial from Access Learning Zone focuses on type conversion functions in Microsoft Access, why you might need them, and how to put them to use with common data import issues.
A frequent scenario comes from users who receive spreadsheets with dates formatted as yyyymmdd. These dates, once imported into Access, often arrive as text fields. You may attempt to import them straight into a date field and encounter a type conversion failure. This happens because Access does not recognize that string format as a valid date. The solution is to use string functions to reformat the data and then use a type conversion function to turn it into a date Access can work with.
Microsoft Access offers a range of type conversion functions: CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CStr, and CVar. Each function converts its argument to a different data type. These functions are useful throughout Access: in queries, forms, reports, and in VBA. For this lesson, I will not focus on VBA, but you need some familiarity with calculated fields in queries and with string manipulation functions like Left, Mid, and Right.
For referential purposes, I am using the ISO date format (year-month-day), which is a standard across many regions. This eliminates confusion between different countries that use day-month-year or month-day-year formatting.
The first step is to work with a sample table, such as an Order table, and add a text field that will hold the imported date strings. Imported dates from outside systems will likely be brought in as text, often looking something like 20231023. After adding some sample records in this format, we can build a query to clean and convert these values.
In the query design editor, I bring in the Order table and the ImportedDate field. The first transformation step is to generate a new string in the ISO-standard format (YYYY-MM-DD). Using the Left function extracts the year, Mid gets the month portion, and Right returns the day. These segments are recombined with dashes in between, creating a string that closely matches Access's preferred date layout.
However, at this stage, the value is still a text string. You can usually tell because text fields align left, while numeric and date fields typically align right in datasheets. This is a general guideline rather than an absolute rule, since formatting can affect alignment. Still, the important detail is that as text, you cannot use these values in date-specific calculations or as criteria in other queries.
Next, we need to convert the formatted string into a true date. This is where type conversion functions are put to work. By applying the CDate function to our newly constructed string date, Access assigns the data type as a date. You can confirm this works properly by testing a simple calculation, such as adding one to the new date (which increments the date by a single day). If the output makes sense—for example, 2022-12-31 becomes 2023-01-01—you now have a working date value.
Once you have the date in the format Access understands, you may want to update your table with the new values. You can add a dedicated date field to your table and use an update query to populate it with the calculated results. If you are not familiar with update queries, there are additional tutorials on my website that go over this process in detail.
Though this example focused on date conversion, the same principles and functions apply for converting to and from other data types, including Currency, Number, and String. Some functions, like CVar and CSng, are less commonly needed, but conversion to Long, Currency, Date, and String occurs frequently in Access development.
For those interested in gaining a deeper understanding of these type conversion functions, I offer an Access Expert Level 26 class, which is part of my comprehensive Access functions series. In this set of classes, I cover all aspects of Access's built-in functions, including string, logical, math, date/time, aggregate, and type conversion functions, giving you the practical know-how to work with data effectively.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Type conversion functions in Access
Common types: CByte, CCur, CDate, CDbl, CDec, CInt, CLng, CSng, CStr, CVar
Why imported dates show type conversion failure
Handling imported text dates in yyyyymmdd format
Using a text field to store imported dates
Building queries to manage imported date fields
Filtering null values in queries
Using Left, Right, and Mid string functions to extract date parts
Reformatting yyyyymmdd to yyyy-mm-dd using string functions
Converting reformatted text date to a true date with CDate
Verifying conversion by using date math (adding days)
Creating and running update queries to save corrected dates |
