If Then Else statements

[BonnieG]

I need a bit of help with syntax as this one keeps throwing me off. I think it is likely to be a simple fix but I just can't figure it out.

I have a field on frm_main2 called txt_leavers_list_multichange_id and within my VB code I want to make sure this field is populated before the script is run.

When I run the following script I get Run-time error '424' Object required.

I'm guessing my syntax is wrong. Any advice? Do I always need to specify "Is Null" AND =""?

If Forms!frm_main2!txt_leavers_list_multichange_id Is Null Or Forms!frm_main2!txt_leavers_list_multichange_id = "" Then

MsgBox "You must enter a multichange ID."

Else

(...)

End If

 

[pr2-eugin]

If you are trying to Check for Null or Empty String use Nz() or Len() as shown below..

If Len(Forms!frm_main2!txt_leavers_list_multichange_id & vbNullString) = 0 Then
:

The above method will catch both Null and Empty String.. The function you have "Is Null" is a SQL function.. What you should have used is

[URL="http://www.techonthenet.com/access/functions/advanced/isnull.php"]IsNull[/URL](Forms!frm_main2!txt_leavers_list_multichange_id)

However this will not consider ZLS (Zero Length String) as they are technically not Null..

 

[Brianwarnock]

Leave the. (...) out

Brian

 

[BonnieG]

pr2-eugin, you are my hero today! That worked perfectly, thanks.

Brian, the (...) was just to save me pasting a ridiculous amount of confusing code.

 

[pr2-eugin]

pr2-eugin, you are my hero today!

Well as much as I am flattered, it is a very hard truth I am just a student at this Forum, I am happy to help with what I know but most of the time I learn.. Specially from people like Brian ..

BTW, You can call me Paul..

 

[BonnieG]

Well I'm a complete n00b soooo...

I'm used to SQL functions and PHP, so working with Access and VB at work is melting my brain a bit, but it's good to learn.

 

[Brianwarnock]

Brian, the (...) was just to save me pasting a ridiculous amount of confusing code.

I thought it might be something like that, but you never can be too sure.

Best of luck with you project

Brian

 

[MSAccessRookie]

I need a bit of help with syntax as this one keeps throwing me off. I think it is likely to be a simple fix but I just can't figure it out.

I have a field on frm_main2 called txt_leavers_list_multichange_id and within my VB code I want to make sure this field is populated before the script is run.

When I run the following script I get Run-time error '424' Object required.

I'm guessing my syntax is wrong. Any advice? Do I always need to specify "Is Null" AND =""?

If Forms!frm_main2!txt_leavers_list_multichange_id Is Null Or Forms!frm_main2!txt_leavers_list_multichange_id = "" Then
 
MsgBox "You must enter a multichange ID."
 
Else
 
(...)
 
End If

I think that you might be able to kill two birds with one stone by using the nz() Function

If nz(Forms!frm_main2!txt_leavers_list_multichange_id, "") = "" Then
 
MsgBox "You must enter a multichange ID."
 
Else
 
(...)
 
End If

 

[Brianwarnock]

Doesn't Paul's method using Len cover both ?

Brian

 

[MSAccessRookie]

Doesn't Paul's method using Len cover both ?

Brian

Paul indicated that his example might not handle ZLS. Mine should.

 

[pr2-eugin]

Paul indicated that his example might not handle ZLS. Mine should.

Oops.. sorry if that was not clear, I was talking about the IsNull function.. Which will not capture ZLS..

 

[BonnieG]

Paul's snippet seems to work for IsNull and ZLS, so thank you... and thank you MSAccessRookie too for the alternative suggestion.