Expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to make an IIf statement and am getting errors. The following is
the expression:

Building:=IIf(Left [Housing],1="B" ,Left[Housing],3)

What might be the problem?

Thanks,

Dennis
 
Describe in words what you are trying to do, i.e. the logic of your
calculation.

Your expression is incoorect: the IIf is used with exactly 3 aruments and
you have 4. If you meant the Left() function, its 2 arguments must be in
parentheses.

HTH
Van T. Dinh
MVP (Access)
 
I have a string that I want to break up into three fields. The string has
three possible formats as follows:

B21 00000000104U
F10100000000249U
F2GY00000000126U

If the string begins with a "B" then I want to break it down to first field
data, "B21" in field [Building] and disregard the 8 zeros and keep the data
"104U" in field [Bunk].

If the string begins with an "F" and third character is an integer then I
want to break it down to first field data "F1" in field [Facility] and second
field data "01" in field [Building] and disregard the 8 zeros and third field
data "249U" in field [Bunk].

If the string begins with an "F" and the third character is "G" then break
it down to first field data "F2" in field [Facility] and second field data
"GY2" in field [Building] and disregard the 8 zeros and third field data
"126U" in field [Bunk]. Note that the 2 in F2 must be added to the GY to
differentiate the location. Each GY is one of four. Example GY1, GY2, GY3. or
GY4

Please also note that each string is different and only the format is
consistent. All of the three fields combined are unique and will be
CONCATENATED to be
B21-104U
B01-249U
GY2-126U

What do you think?

Thanks,
Dennis



Van T. Dinh said:
Describe in words what you are trying to do, i.e. the logic of your
calculation.

Your expression is incoorect: the IIf is used with exactly 3 aruments and
you have 4. If you meant the Left() function, its 2 arguments must be in
parentheses.

HTH
Van T. Dinh
MVP (Access)



Don said:
I am trying to make an IIf statement and am getting errors. The following is
the expression:

Building:=IIf(Left [Housing],1="B" ,Left[Housing],3)

What might be the problem?

Thanks,

Dennis
Click to expand...
Click to expand...
 
You are much better off writing a VBA function because in VBA, you can use
the proper If ... ElseIf ... Else ... End If rather than the IIf()
function, especially that you have 3 or more possiblities. To use IIf with
3 or more possiblity, you need to use nested IIf()s which will be very
confusing. For example, to get the [Building] and assume that you have only
3 possibilities posted, with IIf, you need:

Building:=IIf( Left([Housing],1)="B",
Left([Housing],3),
IIf ( Left([Housing], 1) = "F" And
IsNumeric(Mid([Housing], 3, 1)),
Mid([Housing], 3, 2),
Mid([Housing], 3, 2) & Mid([Housing], 2, 1)
)
)

As you can see, I used new lines and indentations so that I can see
different components of the nested IIf() - and I am not even sure I got it
correctly. Imagine trying to work out if it is entered all on 1 line.
Forget about more than 3 possibilities as you need another level of IIf for
each additional possibility.

BTW, work out your logic carefully and check what you write before you post.
I am not sure where the "B" in the second concatenated String comes from,
certainly not from the algorithm you described.
 
Van,

<BTW, work out your logic carefully and check what you write before you post.
I am not sure where the "B" in the second concatenated String comes from,
certainly not from the algorithm you described.>

Sorry I had to add the "B" as it is a prefix that stands for building. I
neglected to mention it. I was able to incorporate it. Your example for the
expression worked well. Thank You!

Dennis


Van T. Dinh said:
You are much better off writing a VBA function because in VBA, you can use
the proper If ... ElseIf ... Else ... End If rather than the IIf()
function, especially that you have 3 or more possiblities. To use IIf with
3 or more possiblity, you need to use nested IIf()s which will be very
confusing. For example, to get the [Building] and assume that you have only
3 possibilities posted, with IIf, you need:

Building:=IIf( Left([Housing],1)="B",
Left([Housing],3),
IIf ( Left([Housing], 1) = "F" And
IsNumeric(Mid([Housing], 3, 1)),
Mid([Housing], 3, 2),
Mid([Housing], 3, 2) & Mid([Housing], 2, 1)
)
)

As you can see, I used new lines and indentations so that I can see
different components of the nested IIf() - and I am not even sure I got it
correctly. Imagine trying to work out if it is entered all on 1 line.
Forget about more than 3 possibilities as you need another level of IIf for
each additional possibility.

BTW, work out your logic carefully and check what you write before you post.
I am not sure where the "B" in the second concatenated String comes from,
certainly not from the algorithm you described.

--
HTH
Van T. Dinh
MVP (Access)




Don said:
I have a string that I want to break up into three fields. The string has
three possible formats as follows:

B21 00000000104U
F10100000000249U
F2GY00000000126U

If the string begins with a "B" then I want to break it down to first field
data, "B21" in field [Building] and disregard the 8 zeros and keep the data
"104U" in field [Bunk].

If the string begins with an "F" and third character is an integer then I
want to break it down to first field data "F1" in field [Facility] and second
field data "01" in field [Building] and disregard the 8 zeros and third field
data "249U" in field [Bunk].

If the string begins with an "F" and the third character is "G" then break
it down to first field data "F2" in field [Facility] and second field data
"GY2" in field [Building] and disregard the 8 zeros and third field data
"126U" in field [Bunk]. Note that the 2 in F2 must be added to the GY to
differentiate the location. Each GY is one of four. Example GY1, GY2, GY3. or
GY4

Please also note that each string is different and only the format is
consistent. All of the three fields combined are unique and will be
CONCATENATED to be
B21-104U
B01-249U
GY2-126U

What do you think?

Thanks,
Dennis
Click to expand...
Click to expand...
 
Back
Top