Remove Blanks In Text String

[frosttr]

Ok, I know this is probably a very simple thing, but I've racked my brain and can't find a solution.

Table: Consists of several fields, which I will be utilizing the Company_Name, SalesID, Company_Code, combining the 3 into one text string to create a unique ID for naming conventions. The SalesID & Company_Code are text fields with 3 & 4 digit codes respectfully, and the Company_Name field is a 50 character field with actual company names.

Query: The query will merge the above 3 named fields to one text string field for a unique ID.

Problem: I need to remove all spaces between words so that I have one continous string. I know this can't be done with Clean or Trim or a combination, since they only remove the blank spaces before & after the text or spaces between words that are more than one character length.
For example, Company_Name is Jack Myers Trucking Company, SalesID is 123 and Company_Code is 5678. Once I run the query, it should return (ideally) the unique ID of 123JackMyersTruckingCompany5678. (Character case is not an issue).
Does anyone have any suggestions on how to accomplish this? I can get the results of 123Jack Myers Trucking Company5678, but can't get rid of the spaces between the name.
Suggestions are greatly appreciated!

 

[SgtJarrow]

Look at the Replace function:

Replace(string, character to search for, character to replace with)

so

Replace(Company_Name, " ", "")

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer