Adding a character depending on string length 2

[OMoore]

Hi all,

I have text field in a table that must have six characters, which are usually but not always numbers. An example of the standard format is 088154. The first digit is necessary, and in most cases is a zero.

My problem starts when I import text from a tab delimeted text file. The data is being imported without the zero as first digit.

I need to read the text field and, if the length of the string is less than 6 characters, add a zero before the other 5 characters.

Hope this is clear!!

As I have no experience with strings (yet - I've just bought the developers handbook and am about to jump into it!), can anyone point me in the right direction of how to deal with this problem?

Thanks,
Owen

 

[DBAMJA]

If your data is always going to be 5 characters and you just need to add one character it is like this:

If Len(field_name) = 5 then
field_name = "0" & field_name
end if



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[luceze]

Do you need to store the value in the table or just view it in a query or function?

If you need to store the value in the table you can use an update query to change the values in the table.
Example SQL:
UPDATE YourTable SET YourTable.YourField= IIf(Len([YourField])=5,"0" & [YourField],[YourField]);

If you just need to view (not change) then you can use the same string function in a query

Expr1: = IIf(Len([YourField])=5,"0" & [YourField],[YourField]);

All of this is assuming that the field length is either 5 or 6.

HTH,
Eric

 

[OMoore]

Thanks guys.

It works great using both ways.

Owen