strip first character from a string 3

[hefly]

I have a field "address" which has a period as a leading character. I need to remove that leading period. Other periods in the string remain.

tbl_db1.address

I have found String.remove method that allows the removal of a character from my string. How do I point the module to the field in my table?

Thanks.

Hefly

 

[PHV]

Have a look at the Mid function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hefly]

I found the function string.remove method, but I dont know how to point the function to the field in my table.

 

[pdldavis]

It's pretty easy to do this in a query:

TrimAddress: Right([address],(Len([address])-1))

Hope that helps

 

[TheAceMan1]

How are ya hefly . . .

Here a query/SQL that will remove all leaing '.'s from the address field in the table ([blue]backup the table 1st![/blue] ... just in case):

[blue]UPDATE [purple][b]YourTableName[/b][/purple] 
SET [Address] = Right([Address],Len([Address])-1) 
WHERE (Left([Address],1)=".");[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hefly]

Hmmm:

I am running:

UPDATE tbl_hyperlinkparts
SET [addr2] = Right([addr2],Len([addr2])-1) 
WHERE (Left([addr2],1)=".");

And get a compile error in expression:

'Right(addr2],Len([addr2])-1)'

 

[TheAceMan1]

hefly . . .

I ran this as the SQL of a query. In VBA it would be:

[blue]   Dim db As DAO.Database, SQL As String
   
   Set db = CurrentDb
   
   SQL = "UPDATE tbl_hyperlinkparts " & _
         "Set [addr2] = Right([addr2], Len([addr2]) - 1) " & _
         "WHERE (Left([addr2],1)='.');"
   db.Execute SQL, dbfainonerror
   
   Set db = Nothing[/blue]

Note: in vba I changed [red]"[/red].[red]"[/red] to [red]'[/red].[red]'[/red] ... (double quotes to single).

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

hefly . . .

Hit submit too soon.

Note that the [blue]WHERE claus[/blue] prevents stripping the 1st character [blue]in case its not a period[/blue].

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hefly]

AceMan1... You are awesome!!!

THANK YOU.

Hefly

 

[TheAceMan1]

hefly . . .

BTW ...

[blue]   db.Execute SQL, dbfai[red][b]n[/b][/red]onerror
should be
   db.Execute SQL, dbFai[red][b]l[/b][/red]OnError[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

What about this simpler way ?

SQL = "UPDATE tbl_hyperlinkparts SET addr2=Mid(addr2,2) WHERE addr2 Like '.*'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886