Mismatched type? Nz not changing null to different type?

[Vita]

Hi, Thanks for taking the time to help!
I'm not sure what I'm doing wrong here. This is my code:

Private Sub DhrScanTxt_Enter()

Dim Status As String
Debug.Print Me.DhrScanTxt
If (Me.DhrScanTxt.Value = Empty) Then Me.DhrScanTxt.Value = ""
Debug.Print Me.DhrScanTxt
Status = Nz(Me.DhrScanTxt.Value, "Error Null")
Debug.Print Status
Debug.Print Me.DhrScanTxt


Me.Text26 = Status


Dim SoNum As Long
SoNum = CLng(Left(Status, 6))
Me.SoNumTxt = SoNum


Dim DhrNum As Long
DhrNum = Right(Left(Status, 9), 2)
Me.DhrNumTxt = DhrNum


Dim StepNum As Long
StepNum = Right(Status, 3)
Me.StepNumTxt = StepNum


Me.DhrScanTxt.SetFocus


End Sub

Everytime I open the form the field is null so I was trying to replace it with an empty string so I wouldn't get the error "Invalid use of null" but now I get mismatched type.
The rest of the system works but as you can see in SoNum I am trying to take part of a string and convert it to a long but I keep getting type mismatch when I use Nz.

If I do this:

Dim Status As String
If (Me.DhrScanTxt.Value = Null) Then Me.DhrScanTxt.Value = ""
Status = Me.DhrScanTxt.Value


Me.Text26 = Status


Dim SoNum As Long
SoNum = CLng(Left(Status, 6))
Me.SoNumTxt = SoNum

Then I get invalid use of Null.

I'm not sure what I'm doing wrong.

[Minty]

You need to use the IsNull() syntax

If IsNull(Me.DhrScanTxt) Then Me.DhrScanTxt = ""

In the database world nothing actually = Null .
Even Null = Null evaluates as false.

[Vita]

You need to use the IsNull() syntax

If IsNull(Me.DhrScanTxt) Then Me.DhrScanTxt = ""

In the database world nothing actually = Null .
Even Null = Null evaluates as false.

I thought that is what the Nz function did?

Also I am still getting a type mismatch?

Private Sub DhrScanTxt_Enter()

Dim Status As String
If IsNull(Me.DhrScanTxt) Then Me.DhrScanTxt = ""
Status = Me.DhrScanTxt.Value


Me.Text26 = Status


Dim SoNum As Long
SoNum = CLng(Left(Status, 6))
Me.SoNumTxt = SoNum


Dim DhrNum As Long
DhrNum = Right(Left(Status, 9), 2)
Me.DhrNumTxt = DhrNum


Dim StepNum As Long
StepNum = Right(Status, 3)
Me.StepNumTxt = StepNum


Me.DhrScanTxt.SetFocus


End Sub

[Minty]

Which line are you getting the Type Mismatch on?

[Vita]

Which line are you getting the Type Mismatch on?

This one:

SoNum = (Left(Status, 6))

[Minty]

I have a funny feeling Status might be a reserved word, either change it to strStatus or put it in square brackets.
Personally I would recommend the former...

[Vita]

I have a funny feeling Status might be a reserved word, either change it to strStatus or put it in square brackets.
Personally I would recommend the former...

I am still getting the same error in the same spot. I'm very confused haha

Private Sub DhrScanTxt_Enter()

Dim strStatus As String
If IsNull(Me.DhrScanTxt) Then Me.DhrScanTxt = ""
strStatus = Me.DhrScanTxt.Value


Me.Text26 = strStatus


Dim SoNum As Long
SoNum = CLng((Left(strStatus, 6)))
Me.SoNumTxt = SoNum


Dim DhrNum As Long
DhrNum = Right(Left(strStatus, 9), 2)
Me.DhrNumTxt = DhrNum


Dim StepNum As Long
StepNum = Right(strStatus, 3)
Me.StepNumTxt = StepNum


Me.DhrScanTxt.SetFocus


End Sub

[Minty]

I would ensure the value is what you think it is

Dim SoNum As Long

Debug.print CLng(Left(strStatus, 6))

SoNum = CLng(Left(strStatus, 6))
Me.SoNumTxt = SoNum

[Vita]

I would ensure the value is what you think it is

Dim SoNum As Long

Debug.print CLng(Left(strStatus, 6))

SoNum = CLng(Left(strStatus, 6))
Me.SoNumTxt = SoNum

So I did this and I now get type mismatch on the debug.pring haha

Dim SoNum As Long
Debug.Print CLng(Left(strStatus, 6))
SoNum = CLng((Left(strStatus, 6)))
Me.SoNumTxt = SoNum

[Vita]

I even went as far as doing this:

Dim strStatus As String
If IsNull(Me.DhrScanTxt) Then Me.DhrScanTxt = ""
strStatus = Me.DhrScanTxt.Value


Me.Text26 = strStatus


Dim SoNum As Long
Debug.Print Me.DhrScanTxt.Value
Debug.Print strStatus
Debug.Print Left(strStatus, 6)
Debug.Print CLng(Left(strStatus, 6))
SoNum = CLng(Left(strStatus, 6))
Me.SoNumTxt = SoNum

And none of them output a result. Maybe its because it is being set to an empty string but converting that to a long should set it to 0 right?

[Minty]

Nope - try it in the immediate window. Press ctrl + G then type

? Clng("")

You'll get a type mismatch error
The ? in the immediate window means Print.

CLng expects something in string from it can convert to a number. A ZLS doesn't represent any number so it errors.

[Welshgasman]

You are not using NZ() though.?

Edit: Instead of this Right(Left()) coding, there is a MID() function?

[Vita]

You are not using NZ() though.?

Edit: Instead of this Right(Left()) coding, there is a MID() function?

Is there? And I was using Nz but it was giving the same result. I will have to add an if before the SoNum to check for ZLS and convert it to a 0 or make it just go to end of sub.
Thank you again. You are extremely helpful and nice! I will look into the MID() Function and try using Nz. I am still learning lots but I've definitely grown haha.

[davegri]

or make it just go to end of sub

Certainly. Why try to process data that you know is bad?

[Welshgasman]

Is there? And I was using Nz but it was giving the same result. I will have to add an if before the SoNum to check for ZLS and convert it to a 0 or make it just go to end of sub.
Thank you again. You are extremely helpful and nice! I will look into the MID() Function and try using Nz. I am still learning lots but I've definitely grown haha.

Look at the Val() function

tt=""
? val(tt)
 0

NZ ONLY ​ works if the value is actually Null, not a ZLS.
Better to actually validate the data though, unless leaving empty is actually allowed?
In which case, just test for it and skip when empty?