IIf help

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

Guest

I have a text field in a table containing ID's. The ID's look like this -

1234
R25466
RMA987456
C87546

I am trying to create a query to strip off the letters. Here is what I have
right now -

Expr1: IIf([test2]="A" Or [test2]="C" Or [test2]="F" Or [test2]="H" Or
[test2]="R",Mid([ID],2),[ID])

Expr2: IIf(Left([ID],3)="RMA",Mid([ID],4),[ID])

When I run the query I get the results I need but I would like to have it
all in 1 field. When I tried to combine the formula's and then run the query
again all I get is -1. Any help would be greatly appreciated. Thanks
 
I have a text field in a table containing ID's. The ID's look like this -

1234
R25466
RMA987456
C87546

I am trying to create a query to strip off the letters. Here is what I have
right now -

Expr1: IIf([test2]="A" Or [test2]="C" Or [test2]="F" Or [test2]="H" Or
[test2]="R",Mid([ID],2),[ID])

Expr2: IIf(Left([ID],3)="RMA",Mid([ID],4),[ID])

When I run the query I get the results I need but I would like to have it
all in 1 field. When I tried to combine the formula's and then run the query
again all I get is -1. Any help would be greatly appreciated. Thanks
Click to expand...

Assuming all of the numbers are at the end of the string, and that a
number starts at 1 (not zero)...

Paste the below code into a new module:

Function FindNumbers(strIn As String) As Long
Dim intY As Integer
Dim intX As Integer
For intY = 1 To Len(strIn)
intX = Asc(Mid(strIn, intY))
If intX >= 49 And intX <= 58 Then
Exit For
End If
Next intY
FindNumbers = Val(Mid(strIn, intY))
End Function

Add your own error handling as needed.

Then, in the query:

NumberValues:FindNumbers([FieldName])
 
fredg -

thanks, that was exactly what i needed. It worked perfectly.

fredg said:
I have a text field in a table containing ID's. The ID's look like this -

1234
R25466
RMA987456
C87546

I am trying to create a query to strip off the letters. Here is what I have
right now -

Expr1: IIf([test2]="A" Or [test2]="C" Or [test2]="F" Or [test2]="H" Or
[test2]="R",Mid([ID],2),[ID])

Expr2: IIf(Left([ID],3)="RMA",Mid([ID],4),[ID])

When I run the query I get the results I need but I would like to have it
all in 1 field. When I tried to combine the formula's and then run the query
again all I get is -1. Any help would be greatly appreciated. Thanks
Click to expand...

Assuming all of the numbers are at the end of the string, and that a
number starts at 1 (not zero)...

Paste the below code into a new module:

Function FindNumbers(strIn As String) As Long
Dim intY As Integer
Dim intX As Integer
For intY = 1 To Len(strIn)
intX = Asc(Mid(strIn, intY))
If intX >= 49 And intX <= 58 Then
Exit For
End If
Next intY
FindNumbers = Val(Mid(strIn, intY))
End Function

Add your own error handling as needed.

Then, in the query:

NumberValues:FindNumbers([FieldName])
Click to expand...
 
Back
Top