Replace and a bit calculation

  • Thread starter Thread starter kcwconline
  • Start date Start date
K

kcwconline

Hi Group,

Here's my question.....

Raw data --->> Actual Number
193C-->>1933
193L-->>-1933

How it is possible to write a formula that coverts "C" and "L" to "3"
and "negative value, 3" respectively? I am thinking about writing
something like IIf(Right()=C, ()*10+3).......

Could anyone provide a much better solution?
Thanks a lot!!
 
Try using replace

Val(Replace(Replace(FieldName,"L",3),"C",3)) *
IIf(Instr(FieldName,"L")=True,-1,1)
 
Good question. The original data file is in txt format and I need to
do some analyses in Access or Excel because of poor knowledge in
writing SAS programs...I can only think of this "replace" method to
reach my goal. (Honestly, I didn't know this is COBOL numbering
system.)
Any suggestion on dealing with this kind of data?
Thanks a lot!
 
First of all, thanks for your prompt reply....
I tried to use your formula in the Criteria row in the Design View of
Query function, but it didn't work....
(I did change the FieldName into my own defined Fieldname)

Thanks again. (Please excuse me for not being very familiar with
Access formulas...)
 
Hi Cohen,

Got it.....I should write the formula in "Field" row like this New
FieldName: Val(Replace(Replace(FieldName,"L",3),"C",3)) *
IIf(Instr(FieldName,"L")=True,-1,1)

Right?

Thanks, it really helps
 
Sorry for being annoying........because it's quite urgent to get the
analysis done.
I've the whole conversion table as follows....
If I follow the concept of your formula, it will be a really long
formula......any fast track to solve the problem?

Thank you sooooo much!


{ 0
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J -1
K -2
L -3
M -4
N -5
O -6
P -7
Q -8
R -9
} -0
 
Sorry for being annoying........because it's quite urgent to get the
analysis done.
I've the whole conversion table as follows....
If I follow the concept of your formula, it will be a really long
formula......any fast track to solve the problem?


{ 0
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J -1
K -2
L -3
M -4
N -5
O -6
P -7
Q -8
R -9
} -0
Click to expand...


If that really is in a table, then Join that table to your
data table using a nonequi join:

SELECT fa, fb, . . .,
Sgn(codenum) * CLng(Left(fx,3) & Right(codeum,1))
FROM table INNER JOIN conversions
ON Right(fx ,1) = codechar
 
Hi Group,

Here's my question.....

Raw data --->> Actual Number
193C-->>1933
193L-->>-1933

How it is possible to write a formula that coverts "C" and "L" to "3"
and "negative value, 3" respectively? I am thinking about writing
something like IIf(Right()=C, ()*10+3).......

Could anyone provide a much better solution?
Thanks a lot!!
Click to expand...

I'm guessing that there is more to this! Should A be converted to 1, B to 2,
etc.? What's the logic for negative numbers? How is a number ending in 0
represented?

John W. Vinson [MVP]
 
This function - author unknown - has been posted in the newsgroups
several times over the years.

Function fncZonedToNumber(ZonedValue As Variant) As Variant


Dim strValue As String
Dim strLast As String


If IsNull(ZonedValue) Then
fncZonedToNumber = Null
ElseIf VarType(ZonedValue) <> vbString Then
fncZonedToNumber = CVErr(5) ' invalid argument
ElseIf Len(ZonedValue) = 0 Then
fncZonedToNumber = Null
Else
strLast = Right(ZonedValue, 1)
strValue = Left(ZonedValue, Len(ZonedValue) - 1)


If InStr(1, "0123456789", strLast, vbBinaryCompare) Then
strValue = strValue & strLast
ElseIf InStr(1, "ABCDEFGHI", strLast, vbBinaryCompare) Then
strValue = strValue & Chr(Asc(strLast) - 16)
ElseIf InStr(1, "JKLMNOPQR", strLast, vbBinaryCompare) Then
strValue = "-" & strValue & Chr(Asc(strLast) - 25)
ElseIf StrComp(strLast, "{", vbBinaryCompare) = 0 Then
strValue = strValue & "0"
ElseIf StrComp(strLast, "}", vbBinaryCompare) = 0 Then
strValue = "-" & strValue & "0"
Else
fncZonedToNumber = CVErr(5) ' invalid argument
Exit Function
End If


fncZonedToNumber = Val(strValue)
End If


End Function
 
Thanks to all who provide solutions.

Just one silly question....
How to execute this program in "Queries" or "Tables"?
I saved the program you posted in "Modules" named as "COBOL" and how
should I apply the program?
Say I have one column called "Amount" in the "Table1", and how should
I run the program and successfully convert the numbering system into
actual numbers in column "Amount"?

Really appreciate your help! Thanks so much!
 
1) Rename the Amount column to (say) OldAmount

2) Add a number field (probably a Long) to the table and name it
Amount.

3) Create an update query that updates Amount to
fncZonedToNumber([OldAmount])

4) Run the query and make sure everything's right.

5) Delete the OldAmount column.
 
Back
Top