Change of Seqence

[Peter]

I have asked the following question a number of days
before:

In an Access 97 database, we get a field containing data
like 23/2001, 367/2001, 12/2002, 3/2005.

There is a request to change the display as
BB2001/23
BB2001/367
BB2002/12
BB2005/3

Naresh MVP has suggested an VBA module. Thank you for
your advice.

However, I have created a simple Function as follow and it
works for most of the time:

Function ExtractS(x) As String

Dim i, j, k As Integer

If IsNull(x) Then
Exit Function
Else
ExtractS = Right(x, (Len(x) - (InStr(x, "/")))) & "/"
& Left(x, InStr(x, "/") - 1)
End If
End Function


However, I find that it gives me an error when there is
there is record with "/".

Is there any suggestion to improve the function ?

Thanks

 

[Naresh Nichani MVP]

Hi:

I think it should be Instr(1,x,"/")

Search from starting character 1.

Regards,

Naresh Nichani
Microsoft Access MVP

 

[James Hahn]

What should it become when there isn't a "/"? All your examples include the
slash, so it's not possible to tell from your description what the function
should do if there isn't one.

 

[Joseph Meehan]

I have asked the following question a number of days
before:

In an Access 97 database, we get a field containing data
like 23/2001, 367/2001, 12/2002, 3/2005.

There is a request to change the display as
BB2001/23
BB2001/367
BB2002/12
BB2005/3

Naresh MVP has suggested an VBA module. Thank you for
your advice.

However, I have created a simple Function as follow and it
works for most of the time:

Function ExtractS(x) As String

Dim i, j, k As Integer

If IsNull(x) Then
Exit Function
Else
ExtractS = Right(x, (Len(x) - (InStr(x, "/")))) & "/"
& Left(x, InStr(x, "/") - 1)
End If
End Function


However, I find that it gives me an error when there is
there is record with "/".

Is there any suggestion to improve the function ?

Thanks

Is this a day of year and year number?

 

[onedaywhen]

I have asked the following question a number of days
before:

In an Access 97 database, we get a field containing data
like 23/2001, 367/2001, 12/2002, 3/2005.

There is a request to change the display as
BB2001/23
BB2001/367
BB2002/12
BB2005/3

Naresh MVP has suggested an VBA module. Thank you for
your advice.

I recommend SQL e.g.

SELECT
'BB' & RIGHT$(MyCol,4)
& '/'
& LEFT$(MyCol,LEN(MyCol)-5)
FROM MyTable;

Jamie.

--

 

[Peter]

Thank you for advice from all of you.

They want to show "NO BB" when
1) There is no "/" or
2) The field is NULL

Besides, theoretically, the first 4 characters should
contain YYYY but there may be exception I want to avoid.

Thanks again

 

[James Hahn]

If IsNull(x) Then
ExtractS = "NO BB"
Exit Function
End If
If InStr(x, "/") = 0 then
ExtractS = "NO BB"
Exit Function
End If
If
ExtractS = Right(x, (Len(x) - (InStr(x, "/")))) & "/" & Left(x, InStr(x,
"/") - 1)
End If
End Function

Now that you are evaluating InStr(x, "/") three times you should really do
it before the test, assign the result to a varauble, and test the variable.
Also, I can't see where the BB gets added. An improved version is

If IsNull(x) Then
ExtractS = "NO BB"
Exit Function
End If
N = InStr(x, "/")
If N = 0 then
ExtractS = "NO BB"
Exit Function
End If
If
ExtractS = "BB" & Right(x, (Len(x) - N)) & "/" & Left(x, N - 1)
End If
End Function

You haven't indicated what they want in the case where yyyy isn't four
characters, so I haven't included a test for that. But it would look
something like
If N <> Len(x)-4
ExtractS = "whatever"
Exit Function
End If
--

 

[James Hahn]

Small adjustment required (too much cut and paste)
If IsNull(x) Then
ExtractS = "NO BB"
Exit Function
End If
If InStr(x, "/") = 0 then
ExtractS = "NO BB"
Exit Function
End If
ExtractS = Right(x, (Len(x) - (InStr(x, "/")))) & "/" & Left(x, InStr(x,
"/") - 1)
End Function

or

If IsNull(x) Then
ExtractS = "NO BB"
Exit Function
End If
N = InStr(x, "/")
If N = 0 then
ExtractS = "NO BB"
Exit Function
End If
ExtractS = "BB" & Right(x, (Len(x) - N)) & "/" & Left(x, N - 1)
End Function