stripping off dash

  • Thread starter Thread starter susan
  • Start date Start date
S

susan

Field 3 contains numerous records as follows:
03-81669-ml
02-21567-rjl
00-58902-kl
and so forth.

I need to remove the second dash and letters after the
dash so it comes out
03-81669
02-21567
00-58902
The correct syntax for an update query for access 2000
would be appreciated.
Thanks to anyone who helps.
 
Format(Val(Replace(xxx, "-", "")), "00\-00000")

Example from immediate window:
?Format(Val(Replace("03-81669-ml", "-", "")), "00\-00000")
03-81669
 
Didn't work. I created an update query with field 3, and
pasted >Format(Val(Replace(xxx, "-", "")), "00\-00000")
into the update line and received an error message. Is
replace a function under access 2000.
 
Yes, I believe that 2000 was the first version with the Replace function.
You will need to replace xxx with the name of the field and it appears you
may have a > in front of the function that shouldn't be there. Try posting
the SQL of the query so that we can see how you incorporated the function
into the query.
 
2000 was the first version with Replace function in VBA, BUT it did not work
when used in queries.

If your fields are always of the same exact pattern, you can just use the Left
function and grab the first 8 characters.

Left(YourField,8) will return the first 8 characters.

If the second dash always occurs and always occurs after the 4th character, but
not necessarily in a specific function, you can try

Left(YourField, Instr(5,YourField,"-")-1)

If the second dash is not always there, then you could try.
Left(YourField,IIF(Instr(5,YourField,"-")=0,Len(yourField),Instr(5,YourField,"-")-1)
 
Thanks John,

I assume you could then create your own function to call and use Replace in
that function.
 
Yep, you can and it has been done.

Quote from Dirk Goldgar, MS Access MVP

The Replace function works in Access 2000 VBA, but it can't be used directly
in queries. You need to create a user-defined "wrapper" function that
receives arguments of the appropriate types, passes them on to the Replace
function, and returns the Replace function's result. Here's one:

'---- start of code ----
Function fncReplace(strExpression As String, _
strFind As String, _
strReplace As String, _
Optional lngStart As Long = 1, _
Optional lngCount As Long = -1, _
Optional lngCompare As Long = vbBinaryCompare) _
As String

fncReplace = Replace(strExpression, strFind, strReplace, _
lngStart, lngCount, lngCompare)

End Function
'---- end of code ----
 
Back
Top