Converting a text string to its ASCII value

[Access_Novice]

Let's say I have a string that includes mm/dd/yyyy + person's name. All of this is concatenated and is recognized as a text value. Then I want to convert all of these characters to a number. Since each character has an ASCII code, I would like to find ASCII equivalent.

In Excel, this would involve using something like the CODE ( ) function, but that only gives the numerical equivalent for the first character in the string, but I want all characters at once.

When I try the VAL ( ) function, it returns the first character which is the number of the month.

[Rawb]

I'm not sure exactly what you're trying to do. Are you wanting to convert a text string into a list of ASCII values (one number for each character in the string) so you can store each one separately? Are you trying to add all the values together (sum them)? Are you wanting to just concatenate them all together into one big number?

Also be aware that, especially if your program is going to be used to parse text strings from a word processing program (Like Wordpad, MS Word, OOo Writer, or Google Docs) or in non-english languages, it's possible to run into characters that don't exist in the ASCII character map.

Can you tell us more about what you're trying to accomplish and why?

[Access_Novice]

I'm not sure exactly what you're trying to do. Are you wanting to convert a text string into a list of ASCII values (one number for each character in the string) so you can store each one separately? Are you trying to add all the values together (sum them)? Are you wanting to just concatenate them all together into one big number?

Also be aware that, especially if your program is going to be used to parse text strings from a word processing program (Like Wordpad, MS Word, OOo Writer, or Google Docs) or in non-english languages, it's possible to run into characters that don't exist in the ASCII character map.

Can you tell us more about what you're trying to accomplish and why?

I plan to use a table of data where many of the rows are the same except for the date. For example, I might have a concatenated value of 1/1/2015Johnson,Barry and 03/1/2015Johnson,Barry, each with a different billing rate. I was thinking that in order to differentiate between the two, I would convert the concatenated values to their ASCII equivalent (the code for each character would be summed) and the instance with the later date would have a higher numerical value.

Then in order to get the billing rate tied to that value, I would do something like DLOOKUP where the and take the higher concatenated value or something to that effect.

[orange]

Can I suggest that you do not have to concoct some sort of concatenation. Database works very well when you put 1 fact in 1 field.
Please tell us - in plain English - WHAT you are trying to accomplish. Once readers understand that, they may have options on how it may be accomplished. But we have to understand the issue before focused responses can be made.

[Rawb]

I agree with orange. At the very least, you should split the date portion out into its own Column in the Table if at all possible. This would accomplish 3 things:

• It would simplify and speed up your date comparisons - You wouldn't have to worry about cutting apart data in a single Column. And since Date/Time Columns are stored as numbers instead of Text strings, the comparison (between multiple dates) would be computationally less intense as well.
• It would save space in the database to store the data in an actual Date/Time Column - Since Date/Time Columns are stored as numbers, they take up less space than a date stored as a Text string.
• It would force consistency - Date/Time Columns would allow you to control display of your data without changing how it's stored in the database or how much space it takes. For example, you will be able to control if the dates have leading zeroes or not or if you have a two-digit year or a four-digit year. And it would let you display the data using those rules even if users enter data without following them.

P.S.
Summing characters like you want could also lead to inconsistent results: Using your method 04/02/2015 and 03/03/2015 would have the same values even though they are clearly different dates.