Invalid use of null?

[snipe]

Hey guys.



I can't figure out what is causing the error in this code.

Public Function NewCode(ByVal EmpCode As String) As Variant


Dim nums As String
Dim lefttwo As String
Dim rightthree As String
Dim newlet As String




nums = "0" & Left(Mid(EmpCode, 3), 4)
lefttwo = Left(nums, 2)
rightthree = Right(nums, 3)


newlet = DLookup("Letter", "NumToAlpha", "Number = '" & lefttwo & "'")


NewCode = newlet & rightthree


End Function

I added the ByVal because I was getting a ByRef argument type mismatch when I would call the function.

This function takes RL71730, drops the RL and the last 0, adds a 0 at the beginning, and converts the 07 to a letter from the table, and concatenates that letter with the last 3 digits. Final result: H173.

[JoeM]

Look at your last two lines, "call newcode..." and "?Dlookup...".
You need to have the value RL71730 enclosed in double-quotes. Otherwise, it is treated as a variable or a field, and not the literal value "RL71730".

[snipe]

That errors out too:

[pbaldy]

And the immediate cause of your error is that a String variable can't take a Null, which is what your DLookup() is returning. Joe has you on the path to fixing that.

[snipe]

That DLookup line you mentioned was me trying to find the error. Quotes didn't fix that either.

[snipe]

pbaldy, that was another issue. Why is that dlookup returning a null? 07 clearly corresponds to "H" in the table.

[JoeM]

You need to have the value RL71730 enclosed in double-quotes

not

call newcode('RL71730')

but rather:

call newcode("RL71730")

[snipe]

Ah. Ok that eliminated the error, but I am getting a null result. Still can't figure out why the dlookup is not pulling up "H" as it should.

[JoeM]

Note that your Lookup table ("NumToAlpha") and DLOOKUP function are totally unnecessary. Since the numbers and letters are all in corresponding order, you can just use ASCII functions to get what you need, i.e.

Public Function NewCode(ByVal EmpCode As String) As Variant

Dim nums As String
Dim lefttwo As String
Dim rightthree As String
Dim newlet As String

nums = "0" & Left(Mid(EmpCode, 3), 4)
lefttwo = Left(nums, 2)
rightthree = Right(nums, 3)

newlet = Chr(lefttwo + 65)

NewCode = newlet & rightthree

End Function

[snipe]

Wow.. Didn't even think to do that. Thanks Joe! Working like a charm now.

[JoeM]

Great! Glad it works out for you!

[Missinglinq]

Going by your code

newlet = DLookup("Letter", "NumToAlpha", "Number = '" & lefttwo & "'")

you apparently have a Field named 'Number;' this is a Reserved Word in Access, and these should never be used as Field names! Sooner or later it's apt to jump up and bite you where you'd rather not be bitten!

Linq ;0)>

[snipe]

Thanks Linq. I always forget about the reserved words. That table got deleted anyways with the ASCII fix. Thanks for the reminder tho!