Using Select Case

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
 
P

Paul

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.
 
D

Dan

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...
 
B

Brendan Reynolds

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...
 
J

John Spencer (MVP)

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - exporting serial letters to single documents 0
Drop down list control value in vba 2
how to do this bit of code better? 2
Code using list box and multi select option 8
Error 2427 0
More efficient code for multiple If...End If statements 4
Using DoCmd in an IF/Then statment 2
Forcing All records in a Continuous form to be populated 6

Top