Query Help Needed Please

[TerryD]

I have a query that I am running on a table. I have a field "StrCI" that is
a text field. There are numbers in the field on some records and alpha
letters in the field on other records. What I want to be able to do is that
if the query returns a value in the field that is only 3 digits long I want
to be able to add a "0" the the beginning of the the field to make it a 4
digit number. I can not make this field a numeric field, it has to stay as
text.

Any help in how to accomplish this would be greatly appreciated.

Thanks,
Terry

 

[Jeff Boyce]

Create an update query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pieter Wijnen]

ie

UPDATE myTable
SET MyField = Format(MyField,'0000')
Where IsNumeric(MyField) = True
AND Len(MyField)<4

HTH

Pieter

 

[Guest]

Let me make sure I understand what you want to do. If the value in a field
is text, you don't want to do anything to it, but if the value is numeric,
you want to format it as a 4 character string that starts with zero. If that
is the case, try something like:

Select IIF(isnumeric([YourField]), Format([YourField], "0000"), [YourField])
as NewName FROM yourTable

HTH
Dale