CODEWITHSUNDEEP
ms-accessms-access-2013
Smith Stanley
Smith Stanley

Reputation: 481

Force Short TextField To Always Be X Characters

I am attempting to use a IIF() statement in a query to update a short text field to always be 4 characters. This is my syntax, but 

SELECT DISTINCT IIf(Len([User ID] = 1), "000" & [User ID], IIf(Len([User ID] = 2), "00" & [User ID], IIf(Len([User ID] = 3), "0" & [User ID], [User ID]))) AS ST
FROM _TestData;

However it seems to be appending 0's to the data regardless of length?

Upvotes: 0

Views: 37

Answers (2)

Gustav
Gustav

Reputation: 55841

It could be this simple:

SELECT DISTINCT Right("000" & [User ID], 4) AS ST
FROM TestData;

Upvotes: 2

Sergey S.
Sergey S.

Reputation: 6336

If [User ID] is number, try this:

SELECT DISTINCT Format([User ID], "0000") AS ST
FROM _TestData;

Update

Missed that the field is short text. In this case

SELECT DISTINCT Format(CLng(Nz([User ID],0)), "0000") AS ST
FROM _TestData;

Upvotes: 0

Related Questions