typecasting characters with VBA

[ctaylor]

TASK: Write a function that inputs a character (dealt with as a string for VBA purposes) and returns the Windows ANSI character map value as an integer.

PORPOSE: I need to encrypt particular fields within an Access query while leaving other fields unencrypted.

APPROACH: Write a function for MS Access to convert all characters to integers before performing mathematical operations (such as matrix multiplication) and reconverting the result back into characters.

PROBLEM: I'm finding that MS created several functions such as CINT, CSTR, CDBL, but apparently each of these functions performs an ISNUMERIC-type test prior to execution. This results in easy conversion of "7" (character) to 7 (integer) with ease. Unfortunately it falls flat when converting "D" to 068.

The code produces run-time errors currently, indicating a type-mismatch.

Any assistance would be greatly appreciated.


NOTE: If I was able to use C++, all I would need to do is typecast a char as an int to resolve this task.

[ctaylor]

I just found the ASC function in the MSDN library that converts characters to ANSI code values.

I think I have this one worked out now.

[ctaylor]

PROBLEM: I thought I had this resolved, but I am having trouble dealing with null values. Every time my function attempts to encrypt a null field within a record, I receive "#Error" in the query results for the encrypted field.

ATTEMPTS TO TEST FOR NULL FIELDS:
I am trying to test to see if strString, (the argument within the my function Encrypt) is null.

(FUNCTION PROTOTYPE:
Public Function Encrypt(strString As String) As String)


ATTEMPT#1:
'test to see if strString is null using null notation
If strString is null Then
intStrLength = 0
End If
RESULT: data type mismatch error at compile time.


ATTEMPT#2:
'test to see if strString is null using "" notation
If strString = "" Then
intStrLength = 0
End If
RESULT: no compile or runtime error messages, but "#Error" appears in the query results view.


ATTEMPT#3:
'test by measuring string length. If string length is not greater
'than zero, assign field length to zero
If intStrLength > 0 Then
intStrLength = intStrLength
Else
intStrLength = 0
End If
RESULT: no compile or runtime error messages, but "#Error" appears in the query results view.
OBSERVATION: The problem with using the len function appears to be that the function will not return the value zero when measuring the length of a null string. The len function simply fails to return any results.


Any thoughts would be appreciated.

[ctaylor]

Problem solved. My function prototype was receiving ists argument as a String. Once I changed the function prototype to receive a Variant, I was able to use the IsNull() function to test for null values.