Forma

[samcannet]

I have data that is not in a consistent format that needs to be split
in to two different fields. I have provided samples below. What formal
could I use to split this out?
Orig Column1 Column2
PanelNum Should be Should be
0002 0002
0005C 0005 C
0002B 0002 B
01A 0001 A
0005B 0005 B
04 0004
I only need the last character if it is Alpha.

Thanks for your help

 

[vbasean]

2 Calculated Fields in Query
Column1: IIF(Right([Orig], 1) Like "[A-Z]", Left(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Left(10000 * CInt([Orig]))

Column2: IIF(Right([Orig], 1) Like "[A-Z]", Right([Orig],1),"")

Public Function ReturnChar(str As String) As String
If Right(str, 1) Like "[A-Z]" Then
ReturnChar = Right(str, 1)
End If
End Function

Public Function ReturnNum(str as String) as String

End Function

 

[vbasean]

Column1: IIF(Right([Orig], 1) Like "[A-Z]", Left(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Left(10000 * CInt([Orig]), 4)

oops, got to get that left 4 ...
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

2 Calculated Fields in Query
Column1: IIF(Right([Orig], 1) Like "[A-Z]", Left(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Left(10000 * CInt([Orig]))

Column2: IIF(Right([Orig], 1) Like "[A-Z]", Right([Orig],1),"")

Public Function ReturnChar(str As String) As String
If Right(str, 1) Like "[A-Z]" Then
ReturnChar = Right(str, 1)
End If
End Function

Public Function ReturnNum(str as String) as String

End Function
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I have data that is not in a consistent format that needs to be split
in to two different fields. I have provided samples below. What formal
could I use to split this out?
Orig Column1 Column2
PanelNum Should be Should be
0002 0002
0005C 0005 C
0002B 0002 B
01A 0001 A
0005B 0005 B
04 0004
I only need the last character if it is Alpha.

Thanks for your help

 

[vbasean]

I feel like a bozo today... LOL

I don't want the LEFT 4 I want the RIGHT 4

Column1: IIF(Right([Orig], 1) Like "[A-Z]", Right(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Right(10000 * CInt([Orig]), 4)

--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Column1: IIF(Right([Orig], 1) Like "[A-Z]", Left(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Left(10000 * CInt([Orig]), 4)

oops, got to get that left 4 ...
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

2 Calculated Fields in Query
Column1: IIF(Right([Orig], 1) Like "[A-Z]", Left(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Left(10000 * CInt([Orig]))

Column2: IIF(Right([Orig], 1) Like "[A-Z]", Right([Orig],1),"")

Public Function ReturnChar(str As String) As String
If Right(str, 1) Like "[A-Z]" Then
ReturnChar = Right(str, 1)
End If
End Function

Public Function ReturnNum(str as String) as String

End Function
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I have data that is not in a consistent format that needs to be split
in to two different fields. I have provided samples below. What formal
could I use to split this out?
Orig Column1 Column2
PanelNum Should be Should be
0002 0002
0005C 0005 C
0002B 0002 B
01A 0001 A
0005B 0005 B
04 0004
I only need the last character if it is Alpha.

Thanks for your help

 

[John Spencer]

Column1: Format(Val(Orig),"0000")

Column2: IIF(Orig Not Like "*[0-9]",Right(Orig,1),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[vbasean]

and it would help if I tested it... I want to ADD not Multiply by 10000

Column1: IIf(Right([ID],1) Like
"[A-Z]",Right(10000+CInt(Left([ID],Len([ID])-1)),4),Right(10000+CInt([ID]),4))

FYI, I tested this and it works. LOL
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I feel like a bozo today... LOL

I don't want the LEFT 4 I want the RIGHT 4

Column1: IIF(Right([Orig], 1) Like "[A-Z]", Right(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Right(10000 * CInt([Orig]), 4)

--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Column1: IIF(Right([Orig], 1) Like "[A-Z]", Left(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Left(10000 * CInt([Orig]), 4)

oops, got to get that left 4 ...
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

2 Calculated Fields in Query
Column1: IIF(Right([Orig], 1) Like "[A-Z]", Left(10000 * CInt(Left([Orig],
len([Orig]) -1), 4), Left(10000 * CInt([Orig]))

Column2: IIF(Right([Orig], 1) Like "[A-Z]", Right([Orig],1),"")

Public Function ReturnChar(str As String) As String
If Right(str, 1) Like "[A-Z]" Then
ReturnChar = Right(str, 1)
End If
End Function

Public Function ReturnNum(str as String) as String

End Function
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud


:

I have data that is not in a consistent format that needs to be split
in to two different fields. I have provided samples below. What formal
could I use to split this out?
Orig Column1 Column2
PanelNum Should be Should be
0002 0002
0005C 0005 C
0002B 0002 B
01A 0001 A
0005B 0005 B
04 0004
I only need the last character if it is Alpha.

Thanks for your help

 

[vbasean]

Thanks John,

I'm not in good form today. lol
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Column1: Format(Val(Orig),"0000")

Column2: IIF(Orig Not Like "*[0-9]",Right(Orig,1),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have data that is not in a consistent format that needs to be split
in to two different fields. I have provided samples below. What formal
could I use to split this out?
Orig Column1 Column2
PanelNum Should be Should be
0002 0002
0005C 0005 C
0002B 0002 B
01A 0001 A
0005B 0005 B
04 0004
I only need the last character if it is Alpha.

Thanks for your help