Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Chr() and Asc() Function in MS Access
MS Access is a powerful database management system that allows users to easily organize, store, and manipulate large amounts of data. Among the many built-in functions that MS Access offers, the Chr() and Asc() functions are particularly useful when working with strings. In this article, we will take a closer look at these functions, including what they do, how to use them, and real-life examples of when they can be applied.
Understanding the Chr() Function
The Chr() function in MS Access is used to return the character that corresponds to a specific ASCII value. The function takes a single argument, which is an integer between 0 and 255. The returned value is a string containing the character that corresponds to the ASCII value passed to the function.
For example, the following code returns the character "A" because the ASCII value for "A" is 65 −
SELECT Chr(65)
It is important to note that the ASCII values for uppercase and lowercase letters are different. For example, the ASCII value for "A" is 65 and the ASCII value for "a" is 97.
Understanding the Asc() Function
The Asc() function in MS Access is the opposite of the Chr() function. It is used to return the ASCII value of the first character of a string. The function takes a single argument, which is a string containing a single character. The returned value is an integer representing the ASCII value of the character passed to the function.
For example, the following code returns the ASCII value of "A" which is 65 −
SELECT Asc("A")
It is important to note that if the string passed to the function contains more than one character, only the first character will be considered.
Using Chr() and Asc() in Queries
The Chr() and Asc() functions can be used in a variety of ways within MS Access queries. They are particularly useful when working with character data in a table.
For example, you can use the Chr() function to convert a numeric field to a character field by using the function in a SELECT statement. The following query converts a field called "Code" to a character field −
SELECT Chr([Code]) AS Code
You can also use the Asc() function to convert a character field to a numeric field. The following query converts a field called "CharCode" to a numeric field −
SELECT Asc([CharCode]) AS Code
Real-Life Examples
Here are a few real-life examples of how the Chr() and Asc() functions can be used in MS Access −
Converting a phone number field to a string − Phone numbers are often stored as numbers in a database, but they need to be displayed as strings in a report or form. The following query converts a phone number field to a string −
SELECT PhoneNumber, Chr(PhoneNumber) AS PhoneNumberString FROM Contacts
Extracting the first letter of a name − You can use the Asc() function to extract the first letter of a name and use it as a key for sorting or grouping. The following query returns the first letter of a field called "Name" −
SELECT Name, Asc(Left(Name, 1)) AS FirstLetter FROM Employees
Encoding and decoding data − The Chr() and Asc() functions can be used together to encode and decode data. For example, you can use the Chr() function to convert a numeric value to a character, and then use the Asc() function to convert that character back to the original numeric value. This can be useful in situations where you need to store sensitive data in a way that is not easily readable.
Here's an example of how you might use these functions to encode and decode a Social Security Number field −
–Encoding SELECT SSN, Chr(Asc(Left(SSN, 1)) + 1) & Chr(Asc(Mid(SSN, 2, 1)) + 1) & Chr(Asc(Mid(SSN, 3, 1)) + 1) & Chr(Asc(Mid(SSN, 4, 1)) + 1) & Chr(Asc(Mid(SSN, 5, 1)) + 1) & Chr(Asc(Mid(SSN, 6, 1)) + 1) & Chr(Asc(Mid(SSN, 7, 1)) + 1) & Chr(Asc(Mid(SSN, 8, 1)) + 1) & Chr(Asc(Mid(SSN, 9, 1)) + 1) & Chr(Asc(Right(SSN, 1)) + 1) AS EncodedSSN FROM Employees –Decoding SELECT EncodedSSN, Chr(Asc(Left(EncodedSSN, 1)) - 1) & Chr(Asc(Mid(EncodedSSN, 2, 1)) - 1) & Chr(Asc(Mid(EncodedSSN, 3, 1)) - 1) & Chr(Asc(Mid(EncodedSSN, 4, 1)) - 1) & Chr(Asc(Mid(EncodedSSN, 5, 1)) - 1) & Chr(Asc(Mid(EncodedSSN, 6, 1)) - 1) & Chr(Asc(Mid(EncodedSSN, 7, 1)) - 1) & Chr(Asc(Mid(EncodedSSN, 8, 1)) - 1) & Chr(Asc(Mid(EncodedSSN, 9, 1)) - 1) & Chr(Asc(Right(EncodedSSN, 1)) - 1) AS SSN FROM Employees
Combining Chr() and Asc() with Other Functions
The Chr() and Asc() functions can also be combined with other built-in functions in MS Access to achieve more complex results.
For example, you can use the LEN() function to determine the length of a string and then use the Chr() function to return a specific character at a certain position within that string. Here's an example of how you might use these functions to retrieve the last character of a string −
SELECT Name, Chr(Asc(Right(Name, 1))) AS LastChar FROM Employees
Another example is to use the ROUND() function to round a number and then use the Chr() function to return the corresponding ASCII character. Here's an example of how you might use these functions to round a number to the nearest multiple of 10 and return the corresponding ASCII character.
SELECT ROUND(Num, -1) AS RoundedNum, Chr(ROUND(Num, -1)) AS AsciiChar FROM Table
Considerations
It is important to keep in mind that the Chr() and Asc() functions only work with single characters. If you pass a string containing more than one character to the function, only the first character will be considered.
Also, ASCII codes only represent a limited set of characters, and the ASCII codes for characters from different languages may not be consistent. If you are working with non-English characters, it's important to use the appropriate character encoding system.
Additionally, when using the Chr() and Asc() functions with other functions, make sure that the data types returned by these functions match the data types expected by the other functions. For example, if you are trying to concatenate a string with the result of the Chr() function, make sure that the result of the Chr() function is also a string.
Conclusion
In conclusion, the Chr() and Asc() functions in MS Access are powerful tools for working with string data. They allow you to easily convert between character and numeric data, extract specific characters from a string, and even encode and decode data. By understanding how these functions work and when to use them, you can greatly enhance your ability to work with string data in MS Access.
524 Views
To Continue Learning Please Login