ASCII to numbers (97)

Topic

New Reply

Does anyone know of any functions that will convert numbers of different datatypes to their equivalent ASCII strings (like would be stored on disk). For example, let’s say I have an integer datatype. Integers are stored as 2-character ASCII strings. So if I have a number like 19789, what is the ASCII equivalent (in this case, it is “MM”)?

And then vice versa, if I have a given string, what is its numeric equivalent. Both Long Integers and Singles are stored with 4 characters; so given 4 such characters, what would be the equivalent Long Integer or Single value?

Reply | Quote

Replies

For your first question, try chr(number256) & chr(number mod 256). Going the other way, try asc(left(string,1))*256 + asc(right(string,1))

You can do something similar for long ints – it just takes more multiplications, divisions, and modulos by 256. I don’t have an answer for singles or doubles – in C it would probably be easiest to set up a Union, but I don’t know of an equivalent in VBA

Reply | Quote

I really needed something for the other data types, like single and double. I know I could create my own function for integers and long integers, but I’m somewhat lazy, and I was hoping to give someone else the glory!

Reply | Quote

Here’s an embellishment on Douglas’ suggestion. These handle 4-byte longs and 4 character strings, respectively:

Public Function strASCII(lngNum As Long) As String
Dim intByteCount As Integer
strASCII = “”
For intByteCount = 1 To 4
‘ Strip off 8 bits at a time
strASCII = Chr(lngNum And 255) & strASCII
‘ Shift number 8 bits to the right
lngNum = lngNum / 256
Next intByteCount
End Function

Public Function lngASCII(strText As String) As Long
Dim intByteCount As Integer
lngASCII = 0
‘ Assumes 4 characters are present
For intByteCount = 1 To 4
‘ Convert each character to ASCII code and shift 24, 16, 8, or 0 bits to the left
lngASCII = lngASCII + Asc(Mid(strText, intByteCount, 1)) * 256 ^ (4 – intByteCount)
Next intByteCount
End Function

Reply | Quote

Thanks, your suggestion seems like a novel approach, but it is really the other datatypes that are giving me trouble. I understand the translation from to/from integers, I just don’t understand what the translation is to/from the others and ASCII.

Reply | Quote

Storage of doubles depends on the IEEE encoding, which stores the exponent and its sign in some number of bits, and the mantissa and its sign in the rest of the storage ‘unit’. Furthermore, the number is ‘normalized’ to maintain the maximum accuracy, and, because the first bit of the normalized mantissa is always 1, it is omitted to allow storage of another bit of accuracy. So you can give the glory to IEEE and just treat the number as a double.

OTOH, i wonder why this is necessary? Your number 19789 is stored as the Hex value 4D4D, which is directly legible as an integer?

Reply | Quote

>>OTOH, i wonder why this is necessary? Your number 19789 is stored as the Hex value 4D4D, which is directly legible as an integer?<<

It really isn't integers that are giving me troubles, it is Singles, Doubles, and Dates. I could write a function to convert integers as I know the formula; I was just looking for one already written, if possible. However, it is the floating point numbers that I can't write a function for, since I don't understand how the conversion works.

Reply | Quote

As I’ve watched this thread, I’ve grown curioser and curioser. Why do you care how data is stored internally in Access? Hex display of floating point numbers isn’t very useful, as it doesn’t give any idea of the magnitude of the number unless you convert it to text, and Access does that pretty well. So what’s the underlying problem here – just my curiosity!

Reply | Quote

I don’t care how Access stores it. I’ve got a conversion project, and the input file has dates and number stored that way. I just need a way to read it! I was trying just importing, but that didn’t do it. I’ve just tried using GET, and that looks like it will work. My only problem now is trying to guess the actual layout of the input file!

Reply | Quote

Echoing Wendell’s wonder…

Are you looking for Val() and Str()? I found the AXP help ‘good enough’, but still…

A double is written out in 8 bytes = 8 ascii chars = 4 unicode chars. If you look at the bytes using an ascii translater, you will see the ascii chars of the underlying stored bit pattern. But the double is not ‘stored as chars’, it’s stored as a double according to the rules for its representation. It is exactly the same bitpattern you would see if you looked at a double in memory. The idea is to write it out as fast as possible; such a process does not involve a translation into chars, nor does the read involve a translation from chars.

Reply | Quote

Try searching MSDN for Q42980, that has the title
‘(Complete) Tutorial to Understand IEEE Floating-Point Errors’.
It does contain info about the structuring of the single and double datatypes and their storage.

Reply | Quote

Thanks, that MSKB article at least gets me started. I had tried the MSKB already, but I guess I didn’t enter the right keywords!

I still think it odd that Access (and other databases) obviously converts floating point numbers to/from storing them, yet there are no functions to do the same thing.

Reply | Quote

I would have thought it is the OP system that performs the conversion on storing to/from disk, real numbers will be used in many programs and there must be a standard defined.

Reply | Quote

The solution that comes to mind is to write the numbers out in “binary” format to a temporary file and read them back into string variables. I think you’d want to use PUT and GET, but I’ve never had the need to use them, and when I looked at the descriptions I didn’t want to take a stab at the code!

Reply | Quote

I don’t know what the numbers are, that is the problem. I have a text file that contains data stored that way, which I want to import into Access. Using import didn’t handle the data. I was just working with GET, and it appears that it will work after all. Part of my problem was not having a layout of the text file, so a long integer I thought was in 5-8 turned out to be a double in 2-9! Conversions can be fun!

Reply | Quote

if the data are stored ‘that way’, then you don’t have a text file, you have a binary file, whatever extension it has notwithstanding.

if you don’t (re)develop the file structure, how can you tell a short int from 2 consecutive bytes? etc.

Reply | Quote

>>if you don’t (re)develop the file structure, how can you tell a short int from 2 consecutive bytes? etc.<<

Experience, clairvoyance, and trial & error. Besides, if just anyone could do this, then no one would need me!

Reply | Quote

You’re probably fairly safe if the file was created in Windows or DOS; however be careful if it originated anywhere else. There are different ways out there of storing things, and the format of numbers (or even strings) in the file may not be the format you need in your variables.

Reply | Quote

>>You’re probably fairly safe if the file was created in Windows or DOS; however be careful if it originated anywhere else. There are different ways out there of storing things, and the format of numbers (or even strings) in the file may not be the format you need in your variables.<<

And as I've found out, you are so right! Date fields seem to be one problem area. Since the source is an old application, this could even be a Y2K problem!

Reply | Quote

Just a further comment. Some time ago Microsoft used a proprietry format for real numbers, NOT the IEEE standard. If it is a file created from an old program then you may have to adress that issue also.

Reply | Quote

>>Some time ago Microsoft used a proprietry format for real numbers, NOT the IEEE standard. <<

I discovered that also while reading thru the MSKB, and was wondering if that might be relevant here, since my understanding is that this is from an old application.

Reply | Quote

Do the original developers of the program exist? In other words can you get the format of the file you want to read from them? Without that I think you may well be on a highway to nowhere, unless you can generate a file from inputs you know and reverse engineer from that.

Reply | Quote

>>Do the original developers of the program exist? <<

Nope, that's why I got involved. Took a bit of work, but I think I got it licked now!

Reply | Quote