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

Question

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 format([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

By "It doesn't work", what do you mean? It shows ID without formatting. It doesn't list anything in the list box? You get an error? — BIBD
– BIBD, Commented May 12, 2011 at 19:07
Note, using Name as a column name is not a great idea as NAME is a reserved word in MS Access. It can cause you lots of grief later on. support.microsoft.com/kb/286335 — BIBD
– BIBD, Commented May 12, 2011 at 19:14

RolandTumble · Accepted Answer · 2011-05-12 21:57:20Z

1

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.

answered May 12, 2011 at 21:57

RolandTumble

4,7434 gold badges34 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kaunglay Over a year ago

Now, i found format function in other forms works well, but it doesn't work in one form. When I removed format function in SQL statement, this form run correctly. After inserting format function in SQL statement, I got this error. <Path> is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. (Error 3044)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related