Add text to make max length in field (1 Viewer)

[Hemish]

Hi,

I have 2 fields in a query which I want to write to a table

Eg ProductName (field length 10) and Product Code (field length 5) the fields need to be filled out so the length makes the maximum text

For example product name 'test' length is 4 i have to add 0 to make the length to 10 so the field will be 000000test same thing applies to Product code. I have 700 records like this, is there a way in VBA where it adds the extra text to make it the maximum length.

Hope this makes sense

Thank you

 

[plog]

I have 2 fields in a query which I want to write to a table

Why? How come these must be in a table and you can't just use that query?

 

[Thales750]

You don't have to worry about field lengths, you need to test the string length. If, and then concatenate leading "0"

 

[Hemish]

Why? How come these must be in a table and you can't just use that query?

I can run this off a query just thought i run this off a table as it will be faster to run

 

[plog]

So how much time are you saving between the two methods?

 

[Hemish]

You don't have to worry about field lengths, you need to test the string length. If, and then concatenate leading "0"

Do i need to create lots of if statements saying if lenght is 4 then need (6) 000000 to make 10 or if length is 6 then i need (4) 0000 to make 10.?

 

[plog]

Use a loop. Calculate how many you will need to prefix it with and then run a loop that many times to add a '0' each time.

 

[JHB]

Do i need to create lots of if statements saying if lenght is 4 then need (6) 000000 to make 10 or if length is 6 then i need (4) 0000 to make 10.?

In a query, (replace the name with yours):

SELECT Left("0000000000",10-Len(CStr([ProductName]))) & CStr([ProductName]) AS TheProductName, Left("00000",5-Len(CStr([ProductCode]))) & CStr([ProductCode]) AS TheProductCode
FROM TextLen;

In a form, (replace the name with yours):

  Me.ProductCode = Left("00000", 5 - Len(CStr(Me.ProductCode))) & CStr(Me.ProductCode)
  Me.ProductName = Left("0000000000", 10 - Len(CStr(Me.ProductName))) & CStr(Me.ProductName)

 

[Thales750]

Good Job JHB

 

[stopher]

Left("0000000000",10-Len(CStr([ProductName]))) & CStr([ProductName])

There's a slightly cheekier way:

Right("0000000000" & [ProductName],10)

 

[gemma-the-husky]

why on earth do you need to add leading zeroes to fill a string to a set length?

 

[Thales750]

Sorting. Text fields require leading zeros.

 

[Thales750]

There's a slightly cheekier way:

Right("0000000000" & [ProductName],10)

Does cheekier mean better?

 

[gemma-the-husky]

Does cheekier mean better?

it seems better to me. it's another way of using the actual data "as is" without having to amend the actual stored data.

 

[Thales750]

it seems better to me. it's another way of using the actual data "as is" without having to amend the actual stored data.

I wasn't challenging the answer, I'm inquiring about the meaning of "cheeky"

 

[stopher]

I wasn't challenging the answer, I'm inquiring about the meaning of "cheeky"

Cheeky as in stepping outside the logical way of solving. The logical way to think about solving the problem is to left append exactly the correct number of zeros so the problem is a case of working out generating a string of zeros of the required length (see JHBs solution). The solution I gave is, I would consider, a less logical way i.e. left appending plenty of zeros and then chopping the string to the required length.

btw, STRING("0",n) produces a string of n zeros which could be helpful in several solutions.

Another amusing method is:

Replace(Format([myNumber],"@@@@@@@@@@")," ","0")

Chris