delete comma at end of field

[bricklebrit]

Hello,

In a form, I cut and paste a lot of address information. On one field [ShipCity], the pasted data often ends with a comma --

Is there a simple script I can put into the After Update event that will check to see if the last text is ", " or just "," and delete the comma?

Thanks in advance for your help!

 

[Fornatian]

Don't use the AfterUpdate - thats too late - the data has already been saved - use the BeforeUpdate like:

If Right$(Me.MyControl,1) = "," Then
Me.MyControl = Left$(Me.MyControl,Len(Me.MyControl)-1))
End if

Alternatively, use a query to update the entire database all at once, build a calculated column with Right$(Me.MyControl,1) as the expression, "," as the criteria. This will ID all records that require amendment.

Convert to an update query and update the ACTUAL field to:

Left$(Me.MyControl,Len(Me.MyControl)-1))

[This message has been edited by Fornatian (edited 05-26-2002).]

 

[Fornatian]

Take a backup of the database as always just in case

 

[bricklebrit]

Fornation -

Thanks for the prompt response. I'm using the code:

Private Sub ShipCity_BeforeUpdate(Cancel As Integer)

If Right$(Me.[ShipCity], 1) = "," Then
Me.[ShipCity] = Left$(Me.[ShipCity], Len(Me.[ShipCity]) - 1)
End If

End Sub

I'm getting a Run-time error '-21473567 (800200009)':

The macro or function set to the BeforeUpdate or Validation rule is preventing Microsoft Access from saving the data in the field and prompts for DEBUGGERY!

Any thoughts on what I'm doing wrong?

Thanks!

 

[Fornatian]

Move the code to the forms before update event. I used this and it worked fine.
If that doesn't work consider whether you have any other fields that may be affecting the fields value.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Right$(Me.TestField, 1) = "," Then
Me.TestField = Left$(Me.TestField, Len(Me.TestField) - 1)
End If
End Sub

 

[bricklebrit]

Thanks for the clarification -- I had it on the BeforeUpdate of the actual entry field. It now works great under the BeforeUpdate of the form with the code:

The only error I get it if the field turns up a null. Is there a simple IF statement I can avoid running the code like:

if [shipCity] = Not Null then

If Right$(Me![ShipCity], 1) = "," Then
Me![ShipCity] = Left$(Me![ShipCity], Len(Me![ShipCity]) - 1)
End If

end if


Although the code above doesn't create any errors with null entries it won't go ahead and execute the comma cutting statement any more (cuting off the null if works fine, but still causes errors when there is a null).

Thanks again for all your help.

 

[Fornatian]

use:

if Nz(Me.ShipCity,"") <> "" then
'previous code
end if

this will catch both null and empty strings.

 

[bricklebrit]

Thanks for the prompt response and fix! It works great now -- I certainly appreciate it!

Thanks again!
Jon