Hide part of field ?

[Dave Elliott]

On my form I have a control named Platform which contains the name of an
oilfield platform, i.e. High Island 376 A
If this platform shows a A or B, then I need to hide the A or B so my report
does not show it.

High Island 376 A

 

[Klatuu]

In the Current event for your form:

If not Me.NewRecord Then
If Instr("AB",Right(Me.Platform,1) ) <> 0 And Not IsNull(Me.Platform) Then
Me.Platform = Left(Me.Platform,Len(Me.Platform - 1))
End If
End If

 

[Ofer]

Try this, in the control source of the field

=IIf(Right([Platform],1)="A" Or Right([Platform],1)="B",
Left([Platform],Len([Platform]) -1),[Platform])

 

[Klatuu]

I like your solution, Ofer, but it does not take care of Nulls or New
Records. I also had a thought since my last post. When the record updates,
it will loose the A or B. There needs to be some code to put the last
character back on before it updates. This is going to get a little complex.
Let's say the value came in at 378 A and we remove the A so now the value is
378. We store the knowledge that we have to put the A back on. But, the user
changes the value to 422 A. Unless we trap for a change, when the record
updates, it will update to 422 AA or, if the field length is too short for
the extra character, we get an error.

Try this, in the control source of the field

=IIf(Right([Platform],1)="A" Or Right([Platform],1)="B",
Left([Platform],Len([Platform]) -1),[Platform])

In the Current event for your form:

If not Me.NewRecord Then
If Instr("AB",Right(Me.Platform,1) ) <> 0 And Not IsNull(Me.Platform) Then
Me.Platform = Left(Me.Platform,Len(Me.Platform - 1))
End If
End If

 

[Ofer]

Hi Klatuu,
Dave wrote that he want to display this value in a report, this is why I
didn't thought of new records.
I understood from Dave's post that the user type the value in the form, but
he want to change the display in the report.
I'm confused now.

I like your solution, Ofer, but it does not take care of Nulls or New
Records. I also had a thought since my last post. When the record updates,
it will loose the A or B. There needs to be some code to put the last
character back on before it updates. This is going to get a little complex.
Let's say the value came in at 378 A and we remove the A so now the value is
378. We store the knowledge that we have to put the A back on. But, the user
changes the value to 422 A. Unless we trap for a change, when the record
updates, it will update to 422 AA or, if the field length is too short for
the extra character, we get an error.

Try this, in the control source of the field

=IIf(Right([Platform],1)="A" Or Right([Platform],1)="B",
Left([Platform],Len([Platform]) -1),[Platform])

In the Current event for your form:

If not Me.NewRecord Then
If Instr("AB",Right(Me.Platform,1) ) <> 0 And Not IsNull(Me.Platform) Then
Me.Platform = Left(Me.Platform,Len(Me.Platform - 1))
End If
End If

:

On my form I have a control named Platform which contains the name of an
oilfield platform, i.e. High Island 376 A
If this platform shows a A or B, then I need to hide the A or B so my report
does not show it.

High Island 376 A

 

[Gijs Beukenoot]

Dave Elliott schreef op 31-8-2005 :

On my form I have a control named Platform which contains the name of an
oilfield platform, i.e. High Island 376 A
If this platform shows a A or B, then I need to hide the A or B so my report
does not show it.

High Island 376 A

Quick : left( Platform, len( Platform - IIF( ucase(right( Platform, 2
)) = " A" Or ucase(right( platform, 2 )) = " B", 2, 0 ))
Although this does not take 376-A or 376 C and it will also bomb on
empty fields or fields shorter then 2 characters

If any of these conditions are true, you might be better of building a
function

 

[Klatuu]

Maybe I'm over analyziing it. It was because he said put it in a form that I
had concerns.

Hi Klatuu,
Dave wrote that he want to display this value in a report, this is why I
didn't thought of new records.
I understood from Dave's post that the user type the value in the form, but
he want to change the display in the report.
I'm confused now.

I like your solution, Ofer, but it does not take care of Nulls or New
Records. I also had a thought since my last post. When the record updates,
it will loose the A or B. There needs to be some code to put the last
character back on before it updates. This is going to get a little complex.
Let's say the value came in at 378 A and we remove the A so now the value is
378. We store the knowledge that we have to put the A back on. But, the user
changes the value to 422 A. Unless we trap for a change, when the record
updates, it will update to 422 AA or, if the field length is too short for
the extra character, we get an error.

Try this, in the control source of the field

=IIf(Right([Platform],1)="A" Or Right([Platform],1)="B",
Left([Platform],Len([Platform]) -1),[Platform])

:

In the Current event for your form:

If not Me.NewRecord Then
If Instr("AB",Right(Me.Platform,1) ) <> 0 And Not IsNull(Me.Platform) Then
Me.Platform = Left(Me.Platform,Len(Me.Platform - 1))
End If
End If

:

On my form I have a control named Platform which contains the name of an
oilfield platform, i.e. High Island 376 A
If this platform shows a A or B, then I need to hide the A or B so my report
does not show it.

High Island 376 A

 

[Dave Elliott]

Yes, final result to be in report, but if value in form is correct, then
report will only show what the form says.
Can use another field to store the actual syntax such as High Island 376 A
on the form !
Also , how can I add up to the letter G for the syntax? (A thru G)
Tried the below, but did not work, any suggestions as how to approach?

=IIf(Right([Platform],1)="A" Or Right([Platform],1)="B", Or
Right([Platform],1)="C",Or Right([Platform],1)="D",Or

Right([Platform],1)="E",Or Right([Platform],1)="F",Or
Right([Platform],1)="G",
Left([Platform],Len([Platform]) -1),[Platform])

 

[Klatuu]

Just change this line to include all the letters you want to exclude:
If Instr("ABCDEFG",Right(Me.Platform,1) )
I think the Iif is not a good choice for this. Note the line I sent is
easier to read and more efficient.

Yes, final result to be in report, but if value in form is correct, then
report will only show what the form says.
Can use another field to store the actual syntax such as High Island 376 A
on the form !
Also , how can I add up to the letter G for the syntax? (A thru G)
Tried the below, but did not work, any suggestions as how to approach?

=IIf(Right([Platform],1)="A" Or Right([Platform],1)="B", Or
Right([Platform],1)="C",Or Right([Platform],1)="D",Or

Right([Platform],1)="E",Or Right([Platform],1)="F",Or
Right([Platform],1)="G",
Left([Platform],Len([Platform]) -1),[Platform])

In the Current event for your form:

If not Me.NewRecord Then
If Instr("AB",Right(Me.Platform,1) ) <> 0 And Not IsNull(Me.Platform)
Then
Me.Platform = Left(Me.Platform,Len(Me.Platform - 1))
End If
End If

 

[Gijs Beukenoot]

Dave Elliott schreef op 31-8-2005 :

Yes, final result to be in report, but if value in form is correct, then
report will only show what the form says.
Can use another field to store the actual syntax such as High Island 376 A on
the form !
Also , how can I add up to the letter G for the syntax? (A thru G)
Tried the below, but did not work, any suggestions as how to approach?

=IIf(Right([Platform],1)="A" Or Right([Platform],1)="B", Or
Right([Platform],1)="C",Or Right([Platform],1)="D",Or

Right([Platform],1)="E",Or Right([Platform],1)="F",Or
Right([Platform],1)="G",
Left([Platform],Len([Platform]) -1),[Platform])

In the Current event for your form:

If not Me.NewRecord Then
If Instr("AB",Right(Me.Platform,1) ) <> 0 And Not IsNull(Me.Platform)
Then
Me.Platform = Left(Me.Platform,Len(Me.Platform - 1))
End If
End If

Remove the Left function in the first returnvalue of the IIF, it says :
Left([Platform])". That won't work since Left expects a second
parameter. But in this case, just [Platform] will do.