Removing blank space in the field. (1 Viewer)

[aman]

Hi guys

Can anyone please tell me how to remove the blank space in the field? I mean I have a table in which data is uploaded from excelsheets. And now one field in the table "Planno" stores blankspace in the begining of text.

Is there anyway I can remove the begining blankspace from all the values stored in Planno field of the table.

Please let me know.

Thanks

 

[JANR]

Use Trim() function. http://www.techonthenet.com/access/functions/string/trim.php

JR

 

[vbaInet]

The LTrim() variant should be sufficient.

 

[aman]

Hi guys

I have written the following code on the click event of a button that will remove the blank spaces at the begining of all the values in the field.

dim sql as string
sql="Update tblmain set Planno='"& trim(Planno) &"' where ID>1"
docmd.runsql sql

But when the above code runs then the table stores only blank values for Planno field in each record.

Any help would be much appreciated.

Thanks

 

[JANR]

Why do you insist on a VBA when you can just execute an update query?

UPDATE tblmain SET planno = Trim([planno]);

that said remove the delimiters and where-clause since you are going to update the hole table anyway.

Dim sql As String
sql = "Update tblmain set Planno = Trim([Planno])"
CurrentDb.Execute sql

JR