Help: Adding Spaces Based on Total Character Count

[JSmith26]

Sorry, I'm a real newbie. I've learned so much from reading on this forum.

I have 4 fields I'm trying to combine, but I need to add spaces between field 1 and the rest of them. The total character count needs to be 22 including the spaces.

Example:

Field 1: THE

Field 2: 1234

Field 3: BOAT

Field 4: 0001

End Result: THE 1234BOAT0001

Need to add 7 spaces to equal 22 characters.

Fields 1, 2, and 3 can vary in number of characters.

Hoping that makes sense. Any ideas or assistance would be appreciated.

Thanks in advance- Jon

 

[plog]

I would build a custom function in VBA. You would concatenate the fields with data (http://www.techonthenet.com/access/functions/string/concat.php) get the length of that string (http://www.techonthenet.com/access/functions/string/len.php). Subtract that number from 22. Then concatenate a new string but with the appropriate number of characters between the first field and the rest.

Now what happens if the number of characters without extra spaces is >22?

 

[JSmith26]

The number of characters will never >22, but will always be less. I'm going to research further into what you posted and will post back up if I have any questions. Thanks- Jon

 

[JSmith26]

I'm able to use "concatenate" to combine and "len" to do the count, but is there a way to concatenate adding the spaces based on the value found through "len"? Thanks in advance- Jon.

 

[plog]

You would use a loop (http://www.classanytime.com/mis333k/sjloopsarrays.html). My advice would be a for loop. Basically you would calculate how many spaces you need:


SpacesNeeded = (22-Len(YourString))

Then you would set your loop variable to go from 0 to less than SpacesNeeded adding one space each time. Then when it completes you will have SpacesNeeded number of spaces added:

EndingString = Field1
    ' above holds ending string that will be returned from function

SpacesNeeded = (22-Len(Field1 & Field2 & Field3 & Field4))
    ' determines how many spaces to add between field1 and field2     

For 0 to SpacesNeeded
    ' adds correct number of spaces between field 1 and field2 
    EndingString = EndingString & " "
Next

EndingString = EndingString & Field2 & Field3 & Field4

The above is very rough code, it probably doesn't work, but it illustrates all the things you need to do so that you get the results you want.

 

[stopher]

Here's an expression that gives you what you want:

[Field1] & Replace(Format([Field1] & [Field2] & [Field3] & [Field4],String(22,"@")),[Field1],"")

It works like this. The following inner expression concatenates the four words and appends as many spaces to the left make 22 characters:

Format([Field1] & [Field2] & [Field3] & [Field4],String(22,"@"))

But in the above gives us field1 in the wrong place i.e. it needs to be before the appended spaces not after. So we use Replace to remove field1 by replacing the occurrence with an empty string. The we simply append to the left.

hth
Chris

 

[Simon_MT]

More than anything else spaces in record identifier is bad. If can mean end of field or create problems for web based applications. Emails may fail with spaces in the email header syntax.

Simon