recoding a field

[yaheira]

Hi

I'm facing the following problem:
In one field of my table, each record contains a number. It looks like
this:

33384721
33384561
33374721
33284721
43384721
....

Now I would like to recode a part of this number: I would like to have
a command that says: If a number has the format 333x472x, then recode
this 472 into 989.
So I'd get 333x989x.
(This would recode lines 1 and 3 in my example.)

Do you know how to do this?
Thanks for any help
Yaheira

 

[Allen Browne]

Use an Update query to make the change.
(Update on Query menu in query design.)

In the Criteria row under this field, enter:
"####472#"

In the Update row:
Left([F1], 4) & "989" & Right(F1],1)
where F1 represents the name of your field.

If this is a Number field, you might need to use:
CLng(Left([F1], 4) & "989" & Right(F1],1))

 

[Brendan Reynolds]

The following code accepts a string and returns a string. If your number is
actually stored as a numeric data type, you can use the CStr() function to
convert to a string before passing to this procedure, and use Val() to
convert the result of this function back to a number.

Public Function RecodeNumber(ByVal InputString As String) As String

'> 33384721
'> 33384561
'> 33374721

'> Now I would like to recode a part of this number: I would like to
have
'> a command that says: If a number has the format 333x472x, then recode
'> this 472 into 989.
'> So I'd get 333x989x.

Dim strWork As String
strWork = InputString
If Left$(strWork, 3) = "333" Then
If Mid$(strWork, 5, 3) = "472" Then
strWork = Left$(strWork, 4) & "989" & Mid$(strWork, 8)
End If
End If
RecodeNumber = strWork

End Function

 

[yaheira]

Hi Allen

Thank you very much for your help. Unfortunately, it still doesn't
work.. When I use
Left([fieldname], 4) & "989" & Right(fieldname],1)
as you suggested, it says "invalid syntax". I can't figure out, what is
wrong about it. The field contains text. The cursor is placed before
the first comma. Do you know?

- Yaheira

 

[yaheira]

I wonder if I'm so dumb, but I can't make your code work in my update
query. It says I entered an invalid dot or ! operator or invalid
parentheses.
Do you know whats wrong?
Thanks in any way
- Yaheira

 

[Allen Browne]

There's a square bracket missing, inside the Right() function call:
Left([fieldname], 4) & "989" & Right([fieldname],1)

If that still doesn't work, you may have a References problem:
http://allenbrowne.com/ser-38.html

 

[Brendan Reynolds]

I'd need to see the SQL.