Using Select Case

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Can anyone tell me how to put the following code into a Select Case so that
it will look better? I have tried many times but can not get the right
syntax for the Not IsNull in the Case Statement.

Thanks in advance,

If Not IsNull(txtSerial1.Value) Then
![Serial] = txtSerial1.Value
Else
If Not IsNull(txtSerial2.Value) Then
![Serial] = txtSerial2.Value
Else
If Not IsNull(txtSerial3.Value) Then
![Serial] = txtSerial3.Value
Else
If Not IsNull(txtSerial4.Value) Then
![Serial] = txtSerial4.Value
Else
If Not IsNull(txtSerial5.Value) Then
![Serial] = txtSerial5.Value
Else
If Not IsNull(txtSerial6.Value) Then
![Serial] = txtSerial6.Value
Else
If Not IsNull(txtSerial7.Value) Then
![Serial] = txtSerial7.Value
Else
End If
End If
End If
End If
End If
End If
End If
 
I don't think that you can really use a Select Case here because you are not
testing one condition, but seven different conditions.

However, to clean things up you could try

Dim X as Integer

For X = 1 to 7
If Not IsNull(me.controls("txtSerial" & X).Value Then
![Serial] = me.controls("txtSerial" & X).Value
Exit For
End If
Next

Note: this is untested so check for typos.
 
Thanks Paul,

Now that you say that, it makes sense. I will take your advice on the For
Loop.

Paul said:
I don't think that you can really use a Select Case here because you are not
testing one condition, but seven different conditions.

However, to clean things up you could try

Dim X as Integer

For X = 1 to 7
If Not IsNull(me.controls("txtSerial" & X).Value Then
![Serial] = me.controls("txtSerial" & X).Value
Exit For
End If
Next

Note: this is untested so check for typos.

--
Paul
Visit my website www.pdtech.co.uk for developer resources.

Dan said:
Can anyone tell me how to put the following code into a Select Case so
that it will look better? I have tried many times but can not get the
right syntax for the Not IsNull in the Case Statement.

Thanks in advance,

If Not IsNull(txtSerial1.Value) Then
![Serial] = txtSerial1.Value
Else
If Not IsNull(txtSerial2.Value) Then
![Serial] = txtSerial2.Value
Else
If Not IsNull(txtSerial3.Value) Then
![Serial] = txtSerial3.Value
Else
If Not IsNull(txtSerial4.Value) Then
![Serial] = txtSerial4.Value
Else
If Not IsNull(txtSerial5.Value) Then
![Serial] = txtSerial5.Value
Else
If Not IsNull(txtSerial6.Value) Then
![Serial] = txtSerial6.Value
Else
If Not IsNull(txtSerial7.Value) Then
![Serial] = txtSerial7.Value
Else
End If
End If
End If
End If
End If
End If
End If
Click to expand...
Click to expand...
 
Paul's For ... Next loop looks good to me. But it is possible to do it with
a Select Case statement. I think the For ... Next is a better solution in
this particular instance, but in case anyone is interested, here's how you
could do something like this with a Select Case ...

Select Case True
Case IsNull(Me!txtSerial1)
'etc
Case IsNull(Me!txtSerial2)
'etc

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dan said:
Thanks Paul,

Now that you say that, it makes sense. I will take your advice on the For
Loop.

Paul said:
I don't think that you can really use a Select Case here because you are not
testing one condition, but seven different conditions.

However, to clean things up you could try

Dim X as Integer

For X = 1 to 7
If Not IsNull(me.controls("txtSerial" & X).Value Then
![Serial] = me.controls("txtSerial" & X).Value
Exit For
End If
Next

Note: this is untested so check for typos.

--
Paul
Visit my website www.pdtech.co.uk for developer resources.

Dan said:
Can anyone tell me how to put the following code into a Select Case so
that it will look better? I have tried many times but can not get the
right syntax for the Not IsNull in the Case Statement.

Thanks in advance,

If Not IsNull(txtSerial1.Value) Then
![Serial] = txtSerial1.Value
Else
If Not IsNull(txtSerial2.Value) Then
![Serial] = txtSerial2.Value
Else
If Not IsNull(txtSerial3.Value) Then
![Serial] = txtSerial3.Value
Else
If Not IsNull(txtSerial4.Value) Then
![Serial] = txtSerial4.Value
Else
If Not IsNull(txtSerial5.Value) Then
![Serial] = txtSerial5.Value
Else
If Not IsNull(txtSerial6.Value) Then
![Serial] = txtSerial6.Value
Else
If Not IsNull(txtSerial7.Value) Then
![Serial] = txtSerial7.Value
Else
End If
End If
End If
End If
End If
End If
End If
Click to expand...
Click to expand...
Click to expand...
 
another option is to use the IF ... ElseIF ... End if structure.

If Not IsNull(txtSerial1.Value) Then
![Serial] = txtSerial1.Value
ElseIf Not IsNull(txtSerial2.Value) Then
![Serial] = txtSerial2.Value
ElseIf Not IsNull(txtSerial3.Value) Then
![Serial] = txtSerial3.Value
ElseIf ...

End If



Brendan said:
Paul's For ... Next loop looks good to me. But it is possible to do it with
a Select Case statement. I think the For ... Next is a better solution in
this particular instance, but in case anyone is interested, here's how you
could do something like this with a Select Case ...

Select Case True
Case IsNull(Me!txtSerial1)
'etc
Case IsNull(Me!txtSerial2)
'etc

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Dan said:
Thanks Paul,

Now that you say that, it makes sense. I will take your advice on the For
Loop.

Paul said:
I don't think that you can really use a Select Case here because you are not
testing one condition, but seven different conditions.

However, to clean things up you could try

Dim X as Integer

For X = 1 to 7
If Not IsNull(me.controls("txtSerial" & X).Value Then
![Serial] = me.controls("txtSerial" & X).Value
Exit For
End If
Next

Note: this is untested so check for typos.

--
Paul
Visit my website www.pdtech.co.uk for developer resources.

Can anyone tell me how to put the following code into a Select Case so
that it will look better? I have tried many times but can not get the
right syntax for the Not IsNull in the Case Statement.

Thanks in advance,

If Not IsNull(txtSerial1.Value) Then
![Serial] = txtSerial1.Value
Else
If Not IsNull(txtSerial2.Value) Then
![Serial] = txtSerial2.Value
Else
If Not IsNull(txtSerial3.Value) Then
![Serial] = txtSerial3.Value
Else
If Not IsNull(txtSerial4.Value) Then
![Serial] = txtSerial4.Value
Else
If Not IsNull(txtSerial5.Value) Then
![Serial] = txtSerial5.Value
Else
If Not IsNull(txtSerial6.Value) Then
![Serial] = txtSerial6.Value
Else
If Not IsNull(txtSerial7.Value) Then
![Serial] = txtSerial7.Value
Else
End If
End If
End If
End If
End If
End If
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top