Using Field Values in If/Then/Else Statements

[Thomas]

I need to know how to use the value of a table field in a
If...Then...Else... statement. I'm using an expression to
merge data from several fields into one field. I'm also
using character codes to place data on separate lines

(e.g. [Field6] = [Field1] & Chr(13) & Chr(10) & _
[Field2] & Chr(13) & Chr(10) & _
[Field3] & Chr(13) & Chr(10) & _
[Field4] & Chr(13) & Chr(10) & _
[Field5])

Towards the end of the form that I'm using, I have one
field, and then two groups of about 3-4 fields. I need to
be able to have the value of the one field determine which
of the two groups is actually used in the expression.

(e.g. [Field6] = [Field1] & Chr(13) & Chr(10) & _
[Field2] & Chr(13) & Chr(10) & _
[Field3] & Chr(13) & Chr(10) & _
[Field4] & Chr(13) & Chr(10) & _
If [Field5] = OptionOne Then & _
[Field6] & [Field7] & [Field8] & _
Else [Field9] & [Field10] & [Field11])

I've actually tried excatly what I did for the example
above, and it didn't work.

 

[Allen Browne]

Create a variable to hold the result. You can concatenate the values
conditionally like this:

Dim varResult As Variant
varResult = Me.[Field1] & vbCrLf & Me.[Field2] & vbCrLf & ...

If Me.Field9 = 1 Then
varResult = varResult & Me.Field10 & vbCrLf
Else
varResult = varReulst & Me.Field9 & vbCrLf
End If

Me.Field6 = varResult

 

[Marty Suckstorff]

-----Original Message-----
I need to know how to use the value of a table field in a
If...Then...Else... statement. I'm using an expression to
merge data from several fields into one field. I'm also
using character codes to place data on separate lines

(e.g. [Field6] = [Field1] & Chr(13) & Chr(10) & _
[Field2] & Chr(13) & Chr(10) & _
[Field3] & Chr(13) & Chr(10) & _
[Field4] & Chr(13) & Chr(10) & _
[Field5])

Towards the end of the form that I'm using, I have one
field, and then two groups of about 3-4 fields. I need to
be able to have the value of the one field determine which
of the two groups is actually used in the expression.

(e.g. [Field6] = [Field1] & Chr(13) & Chr(10) & _
[Field2] & Chr(13) & Chr(10) & _
[Field3] & Chr(13) & Chr(10) & _
[Field4] & Chr(13) & Chr(10) & _
If [Field5] = OptionOne Then & _
[Field6] & [Field7] & [Field8] & _
Else [Field9] & [Field10] & [Field11])

I've actually tried excatly what I did for the example
above, and it didn't work.
.You don't say whether you are trying to write all of

this in an expression or in a VB procedure. I'm assuming
VB, so try this in your code:


[Field6] = [Field1] & Chr(13) & Chr(10) & [Field2] _
& Chr(13) & Chr(10) & [Field3] & Chr(13) _
& Chr(10) & [Field4] & Chr(13) & Chr(10) _
& IIF([Field5] = OptionOne,[Field6] & _
[Field7] & [Field8],[Field9] & [Field10] _
[Field11])

Now, you are using the IIF function to determine the value
of your final concatenation to [Field6]. Also, You can't
use the coninuation character in the middle of an
expression in VB. So if your entire expression won't fit
on one line comfortably for you, break it up, something
like this:

varC = [Field6]
[Field6] = [Field1] & Chr(13) & Chr(10)
[Field6] = [Field6] & [Field2] & Chr(13) & Chr(10)
[Field6] = [Field6] & [Field3] & Chr(13) & Chr(10)
[Field6] = [Field6] & [Field4] & Chr(13) & Chr(10)
varA = varC & [Field7] & [Field8]
varB = [Field9] & [Field10] [Field11])
[Field6] = IIF([Field5] = OptionOne,varA,varB)

I noticed that you were using [Field6] in the expression
that redefines [Field6] so the above code would store the
previous value in varC before you start recomputing
[Field6]. However, if what you are trying to do is find a
brand new value for [Field6] that is not based on its
previous value you would not need the first line of code
above and you would use [Field6] instead of varC to
compute the value of varA.

Hope this helps. Best of luck!