Convert Values in Field to another VALUE? EX: W785 = 7785 due to W = 7

[taimysho0]

Hello, i have a link table to an excel spreadsheet. How do i create a query so that it converts IDs which begin with a letter to a number? These are the letters with their respective numeric value: Thanks!

A = 1
B = 2
C = 4
D = 5
E = 7
F = 9

[June7]

W = 7 and E = 7 ?

Something like:

NEWID: Switch(Left(ID,1)="A",1, Left(ID,1)="B",2, ...) & Right(ID,3)

[pbaldy]

Another option would be to create a table with fields for the two values and join to it in your query. It would be more dynamic in my opinion.

FYI, I deleted your duplicate threads; please don't start multiple threads on the same topic.

[taimysho0]

hanks june, the only problem is that i have both IDs that begin with a Letter and ones that are already beginning with a number. The code above removes the first digit of the ones that are already beginning with a number. is there a way to modify it so that it only looks at IDs beginning with letters?

ex: If begin with letter a,b,c,d,f, then...code

thanks!

[June7]

Okay, IIf(Left(ID,1) LIKE "[A-Z]", ...

[taimysho0]

thanks june! is there a way to add an "else" in there? such as if [A-Z] then code....Else, leave it alone. because currently that column does not contain the numeric values, it does the conversion for the ones that have letters in front but displays a blank field for those that begin with a number

[taimysho0]

nevermind, i just added Left(ID,1)="1",1, for example. thanks for all the help!

[June7]

The general syntax of an IIf is:

IIf(this true, then this, else this)

Example:

IIf(x=2, [field name] * 2, [field name])

IIf(Left(ID,1) LIKE "[A-Z]", Switch(...), ID)

or

IIf(IsNumeric(ID), ID, Switch(...))

My suggestion for an expression entirely within the query could be impractical. After all, there are 26 letters in the alphabet - are all of them used in your data scheme? This would be a very long expression and might exceed string length limit. Might have to build a custom function with VBA and call the function from query.