Need to apply an IF statement to a sined field

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

Guest

I have a field that the last character is sined. I need to apply the IF
Statement below to convert the sined field.

If the last digit is { then value should be 0
If the last digit is A then value should be 1
If the last digit is B then value should be 2
If the last digit is C then value should be 3
If the last digit is D then value should be 4
If the last digit is E then value should be 5
If the last digit is F then value should be 6
If the last digit is G then value should be 7
If the last digit is H then value should be 8
If the last digit is I then the value should be 9

I had this months ago but I cant find it or figure it out. I appreciate any
help you can give me.
 
Try something like

Select FieldName, Right(FieldName,1) As LastChar, Switch(LastChar = "{",0,
LastChar = "A", 1 , LastChar = "B" , 2, LastChar = "C" ,3 , LastChar = "D" ,
4 , LastChar = "E" , 5 , LastChar = "F" , 6 , LastChar = "G" , 7 , LastChar =
"H" , 8 , LastChar = "I" , 9) As NewFieldName From TableName
 
I tried and I got the last character separated into its own field and New
Quantity field that was blank.. I found the code i had used months ago but
when i try to apply it to my query in the critera i get a type mismatch
error. Here is code, I just need to know how to apply it to my query. Thanks
again

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
 
In your query design add another field

NewFieldName: fncZonedToNumber([FieldName])

Or, in SQL
Select fncZonedToNumber([FieldName]) As NewFieldName From TableName
 
Back
Top