Getting different numbers

[Golfinray]

I have 4 different types of project numbers. First type is a one, two, or
three digit number. (ie, 312, 91, 8, 614.) Second is 4 digits-two digits,
3412-12, 5633-07. Third and fourth types are 4 digits-four digits-two or
three digits, 4303-7876-032 or 5689-4501-86. What I need is for digits to be
converted. I want any 1,2, or 3 digit numbers to now say "AFIRP Type
Project." I want 4 digit-two digit to now say "Transitional Project." And I
want the third and fourth types to say "Partnership Type I" or "Partnership
Type II" based on the first 4 digits. For the AFIRP type I tried IIF([project
ID]like "###",[AFIRP],[project ID]) but I never could get that to work right.
Help! Thanks a bunch!!!!

 

[KARL DEWEY]

Well your first type has less than 4 digits.
The second type has 7 digits so those are easy - test for lenght.

Your third and fourth both have 12 or 13 digits.

Your IIF statement is in error. It needs a wildcard when you use LIKE.
IIF([project ID]like "###*",[AFIRP],[project ID])

 

[John Spencer]

Karl, "#" is a wildcard in Access with JET. So there is no need for the "*"
at the end.

IIF(Len([Project ID])<4, "AFIRP",IIF([project ID] like "####-###",
"Transitional", "???"))

I can't go any further since I don't know how you intend to distinguish
between Type I and Type II projects.

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

 

[Ken Sheridan]

Add a function along these lines to a standard module. You don't say what
first four digits distinguish between Partnership Type I and Partnership Type
II, so for this example I've assumed the former fall within the range 4000 to
4999 and the latter are anything outside that range. You should be able to
amend the inner Select Case construct simply enough to cater for whatever the
criterion really is.

Public Function GetProjectType(varProjectNumber)

If Not IsNull(varProjectNumber) Then
Select Case Len(varProjectNumber)
Case Is <= 3
GetProjectType = "AFIRP Type Project"
Case Is <= 7
GetProjectType = "Transitional Project"
Case Is <= 13
Select Case Left(varProjectNumber, 4)
Case "4000" To "4999"
GetProjectType = "Partnership Type I"
Case Else
GetProjectType = "Partnership Type II"
End Select
End Select
End If

End Function

You can then call it, passing in the project number, to get the project
type, e.g. in a query:

SELECT [ProjectNumber], [Project],
GetProjectType([projectNumber]) AS ProjectType
FROM [Projects];

If you want to permanently convert the values call the function in an UPDATE
query. But back up the table first!!

Ken Sheridan
Stafford, England

 

[Golfinray]

All you guys are awesome! Thanks a bunch!!! Ray

Add a function along these lines to a standard module. You don't say what
first four digits distinguish between Partnership Type I and Partnership Type
II, so for this example I've assumed the former fall within the range 4000 to
4999 and the latter are anything outside that range. You should be able to
amend the inner Select Case construct simply enough to cater for whatever the
criterion really is.

Public Function GetProjectType(varProjectNumber)

If Not IsNull(varProjectNumber) Then
Select Case Len(varProjectNumber)
Case Is <= 3
GetProjectType = "AFIRP Type Project"
Case Is <= 7
GetProjectType = "Transitional Project"
Case Is <= 13
Select Case Left(varProjectNumber, 4)
Case "4000" To "4999"
GetProjectType = "Partnership Type I"
Case Else
GetProjectType = "Partnership Type II"
End Select
End Select
End If

End Function

You can then call it, passing in the project number, to get the project
type, e.g. in a query:

SELECT [ProjectNumber], [Project],
GetProjectType([projectNumber]) AS ProjectType
FROM [Projects];

If you want to permanently convert the values call the function in an UPDATE
query. But back up the table first!!

Ken Sheridan
Stafford, England

I have 4 different types of project numbers. First type is a one, two, or
three digit number. (ie, 312, 91, 8, 614.) Second is 4 digits-two digits,
3412-12, 5633-07. Third and fourth types are 4 digits-four digits-two or
three digits, 4303-7876-032 or 5689-4501-86. What I need is for digits to be
converted. I want any 1,2, or 3 digit numbers to now say "AFIRP Type
Project." I want 4 digit-two digit to now say "Transitional Project." And I
want the third and fourth types to say "Partnership Type I" or "Partnership
Type II" based on the first 4 digits. For the AFIRP type I tried IIF([project
ID]like "###",[AFIRP],[project ID]) but I never could get that to work right.
Help! Thanks a bunch!!!!