Format function not working when fill data to list box by sql select statement

In Access 2003, programming with VBA.

I have a list box to fill data with two columns. One column is ID and the other column is Name. I want the 4 digit format on the ID column

I try at list box' Row Source Properties

I write select sql statment like this

select for开发者_运维知识库mat([ID],'0000'), Name from myTable

It doesn't work.

When I remove format function, it works.

I want to fill the data with the required format

I've only got 2007 here, so it might be a version difference....

Depending on what you mean by "it doesn't work", one of my answers has to be "Yes, it does". My test form did display the formatted ID field correctly.

However, it's no longer the ID field in the control--it's now Expr1, so this does break binding. If you want your display as you described, but still want the control bound to the ID column, try this:

First modify your Row Source to be:

select ID, Format([ID],'0000') As FormattedID, [Name] As SomeName from myTable

(and while you're at it, change the name of Name if you can--I've aliased it in case you don't have control over the table design). Then set Bound Column to 1, Columns to 3 and Column Widths to 0";1";1". This will hide the bound ID but display the formatted one to the user.