Insert leading zeros...MS Access? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert leading zeros...MS Access?

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-28 : 10:42:09

This isnt SQL, but an Access question. Is it possible to insert leading zeros into access?

The account number has a max of 12 digits and the leading zeros can vary.

The user inputs 1234 and then its filled with 000000001234
OR The user inputs 12345 then its filled with 000000012345

and so on...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 11:05:07

There is an access forum.
You can right justify in most languages by

right('000000000000' + convert(varchar(12),@i), 12)
not sure about access but something like
right("000000000000" & @i), 12)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-28 : 11:59:30

Warning: don't confuse formatting with what you are storing.

In Access, the format() function makes this pretty easy:

format(SomeValue,"00000000000")

will padd SomeValue with as many zeroes as you enter. See the help for more info on the function, it is very powerful.

- Jeff

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-28 : 13:30:26

Could I do...?

SELECT *
FROM table
WHERE id = format(SomeValue,"00000000000")

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 13:31:56

Have you tried it?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-12-28 : 13:54:57

Yes, and it's not working for me. Does the syntax look off?

INNER JOIN [ORDER HEADER] ON ARCUSFIL_SQL.Cus_No=format([ORDER HEADER].CustomerNo,"00000000000")

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 14:03:24

What datatype are [ORDER HEADER].CustomerNo and ARCUSFIL_SQL.Cus_No

Try selecting the values that you are trying to join on - it'll probably show you what's wrong.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources