CODEWITHSUNDEEP
ms-accessvba
Mr-Auto
Mr-Auto

Reputation: 51

Access remove line break in string, program tells it's NULL

I have code that does something then you press enter in textfield, problem is when you use Ctrl+Enter, i can capture that event but access tells me in next line that that field is apparently NULL

Private Sub Text5_KeyPress(KeyAscii As Integer)
If KeyAscii = vbKeyReturn Or KeyAscii = 10 Then
    If Len(Me.Text5) = 0 Then Exit Sub


    If Val(Right(Me.Text5, 1)) > 2 Then Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 1) & "0"

So 'Len' works fine, but the 'Right' function gives out 'Invalid use of null', when i hit debug and check the value it is NULL

I can't figure it out

I guess i need to remove new line characters but how to do that when the text box is null and every function for strings spits out that error

Upvotes: 1

Views: 437

Answers (3)

Mr-Auto
Mr-Auto

Reputation: 51

Well, i test it as much as i can and it's just that when you use Ctrl+Enter on a field and capture the key press, field will be null for some reason, i don't see possible way around this

Upvotes: 0

AHeyne
AHeyne

Reputation: 3455

Also you could use Nz and set a return value of your wish in case if the expression is null, in this example also vbNullString and check the result of this function:

If Nz(Me.Text5, vbNullString) = vbNullString Then

or

If Len(Nz(Me.Text5, vbNullString) = 0) Then

or

If Nz(Me.Text5, 0) = 0 Then

or

If Not Nz(Me.Text5, False) Then

For sure you can store the result in a variable first and then check and work with this later on. Whatever fulfills your needs.

Upvotes: 1

Comintern
Comintern

Reputation: 22195

The problem with your check is that Len(Null) is not 0, it's Null.

There are a couple ways to get around this. First, as mentioned in the comments, you can simply add a check for IsNull:

If IsNull(Me.Text5) Or Len(Me.Text5) = 0 Then

The other way you can do this is force it to coalesce by concatenating vbNullString:

If Len(Me.Text5 & vbNullString) = 0 Then

Upvotes: 2

Related Questions