Delete Spaces within a field?

[Guest]

Can a query be used to delete spaces in a text field? We are trying to use
our Student database to create a spreadsheet that will be used in Active
Directory to create our student accounts. I've been able to do everything
within Access except to remove the spaces found in some last names, -- De La
Cruz, San Juan, etc. -- as we don't want spaces in our network logins
(FInitial, LName, last 4 of SSN). I can use a query to search for these
(Like * *) but when I tried to replace them by leaving out the space (**), I
got last names of ** for all matches (test data of course). Can this be done
in Access? I can always do it in the Excel spreadsheet after it's exported,
but I was trying to eliminate any manual operations.

 

[Guest]

I saw some of the other posts about spaces. The spaces are random, and I'd
rather eliminate them only in the query results, not the table. (And I don't
know SQL -- just seen some of it when I look at that view of my queries.)

 

[Guest]

Network Login: Replace([YourField]," ","")

 

[Guest]

Here's the SQL on the query:
UPDATE CreatePartialFields INNER JOIN Students ON
CreatePartialFields.UniqueStudentNo = Students.UniqueStudentNo SET
Students.Login = [TempID]
WHERE (((CreatePartialFields.EnrollmentDate)=[Enter Enrollment Date]));

I couldn't figure out how to do this in one query, so the
CreatePartialFields is getting the first initial and last four of SSN and
concatenating it along with the Last name.

Where do I plug in the information Replace? Does it matter that this is
Access 2000? It doesn't seem to like Replace anywhere I put that statement.

 

[Guest]

What table is [TempID] from? I am assuming that [TempID] is where you
have concatenated the name, inital, and SSN.

If so, then just use the replace on it like this --
UPDATE CreatePartialFields INNER JOIN Students ON
CreatePartialFields.UniqueStudentNo = Students.UniqueStudentNo SET
Students.Login = Replace([TempID]," ","")
WHERE (((CreatePartialFields.EnrollmentDate)=[Enter Enrollment Date]));

Here's the SQL on the query:
UPDATE CreatePartialFields INNER JOIN Students ON
CreatePartialFields.UniqueStudentNo = Students.UniqueStudentNo SET
Students.Login = [TempID]
WHERE (((CreatePartialFields.EnrollmentDate)=[Enter Enrollment Date]));

I couldn't figure out how to do this in one query, so the
CreatePartialFields is getting the first initial and last four of SSN and
concatenating it along with the Last name.

Where do I plug in the information Replace? Does it matter that this is
Access 2000? It doesn't seem to like Replace anywhere I put that statement.

Network Login: Replace([YourField]," ","")

 

[Guest]

That worked! Thank you very much!