how do I format a case statement in Access

[John W. Vinson]

You don't. It's not supported in the Access dialect of SQL.

For a more detailed answer please post a more detailed question in the big
textbox, not just the subject line.

John W. Vinson [MVP]

 

[John]

John,

I have a column in a table that can contain 6 or 7 characters.
If the column has a column length of 6 I want to add a '0' to the beginning
characters.

I was hoping to use a case statement or some other means to do this.
If there is an alterative means of doing this in a query can you show me how
it is done. I cannot find any reference to alterative methods.

John

 

[John W. Vinson]

John,

I have a column in a table that can contain 6 or 7 characters.
If the column has a column length of 6 I want to add a '0' to the beginning
characters.

I was hoping to use a case statement or some other means to do this.
If there is an alterative means of doing this in a query can you show me how
it is done. I cannot find any reference to alterative methods.

Run an Update query updating the field to

Right("0000000" & [fieldname], 7)

Alternatively, create a Query based on the table; include a calculated field

IsSix: Len([fieldname])

and put a criterion on it of =6 to find just those records that have six
characters in the field. Include the field as well, and update it to

"0" & [fieldname]


John W. Vinson [MVP]