trim

[tmarsh]

I'm trying to use the trim function to remove trailing whitespace from 2 fields in a form. Is this possible? If so, where does the code go?

Thanks.

 

[Bat17]

depends how you are trying to use it.
If you are just displaying data then
=Trim([myField]) in the control Record Scource

If the field is used for data entry you age going to need code.

Trim will trim blanks from both ends of a string, Rtrim() will just trim from the right

HTH

Peter

 

[tmarsh]

depends how you are trying to use it.
If you are just displaying data then
=Trim([myField]) in the control Record Scource

If the field is used for data entry you age going to need code.
Peter

I'm not sure really. I imported a lot of names from excel and they all contain whitespace, both in the table and the form. I wanted to remove it somehow but I'm not sure if trim is any use.

 

[Mile-O]

Make an update query:

UPDATE MyTableSET MyField = Trim(MyField);

 

[tmarsh]

Make an update query:

UPDATE MyTableSET MyField = Trim(MyField);

I put it in the update to section but it returns a zero in each record. What am I doing wrong?

 

[Bat17]

can you post the SQL you used to do it, we may be able to spot the error then

Peter

 

[tmarsh]

can you post the SQL you used to do it, we may be able to spot the error then

Peter

I haven't much experience of sgl, I just typed the code in query design.

UPDATE q SET q.FNAME = "UPDATE MyTableSET FNAME"=LTrim("FNAME");

q is the table name and fname is the field I want cleared of whitespace.

Thanks.

 

[Bat17]

try
UPDATE q SET q.FNAME = Trim([FNAME]);

This may still not work if the 'Spaces' are something other than normal spaces though.

Peter

 

[tmarsh]

try
UPDATE q SET q.FNAME = Trim([FNAME]);

This may still not work if the 'Spaces' are something other than normal spaces though.

Peter

Yes, it returns blank. I tried using replace using ^w which didn't work so they must not be regular spaces. What are they??!!!

 

[Bat17]

It should not have returned blank. when run it should have updated your table removing any spaces from begining and end of the field. I not real spaces you should have the data untouched.
You could try copying the offending blank and pasting it into the Replace function to clear it.

Peter

 

[tmarsh]

It should not have returned blank. when run it should have updated your table removing any spaces from begining and end of the field. I not real spaces you should have the data untouched.
You could try copying the offending blank and pasting it into the Replace function to clear it.

Peter

Not sure what's happening. It removes the text in the field and returns a blank field or zero depending on the code. Pasting doesn't work either. Ah well - you can't win them all!

 

[Bat17]

have you actualy run this as a query and ended up with bank data in FName in table 'q' ?

Peter

 

[tmarsh]

have you actualy run this as a query and ended up with bank data in FName in table 'q' ?

Peter

Yes, it's only a test table. I tried again with replace and it seems to have worked. There were several different lengths of spaces if you see what I mean, but I think I got them all.

Thanks.