A way to determine length of length of value in a record

  • Thread starter Thread starter John
  • Start date Start date
J

John

I need to determine if the length of a record is a certain
number and then edit that record. For example if the
record is 8 characters, I need to make the field 5
characters then a space and then the last 3 characters.
Any help with that would be great.

Thanks,
John
 
John said:
I need to determine if the length of a record is a certain
number and then edit that record. For example if the
record is 8 characters, I need to make the field 5
characters then a space and then the last 3 characters.
Any help with that would be great.
Click to expand...

I'm not quite certain what you're asking! Do you want to do a search to find all records where the field is currently 8 bytes long, and update
ABCDEFGH
to
ABCDE FGH
or are you talking about doing this on data entry? Do you want to permanently store the 9-byte value, or is this just for display?

Two possible answers:

- If my first speculation is correct and you want to permanenly update: do an Update query with a criterion of

LIKE "????????"

and update to

Left([fieldname], 5) & " " & Right([fieldname], 3)

- If instead you just want to change the display appearance, set the Input Mask property of the field to

@@@@@ @@@
 
if you're refering to the length of the data in a text field, try this:

IIf(Len(TableName.FieldName) = 8, Left(TableName.FieldName, 5) & " " &
Right(TableName.FieldName, 3), TableName.FieldName)

the above should work in an update query, but *try it in a copy of the table
before touching live data*.

hth
 
Back
Top