Format a text box

[Design by Sue]

I have a text box (ID) who's control source is based on a column of a query that is the basis of a combo box on the same form. This field, ID, is formatted 00000;-00000;00000;"Null", so there are a minimum of 5 digits in the number. Ever where else that this ID shows up, it is formatted correctly, even shows up in the table (where the format is also set) correctly. BUT when I call up the number through the query and use the code =[P Description].[Column](1) to refer to it, it shows up as an unformatted number (i.e. 00015 shows up as 15). (and yes, I have put the formatting code in the format properties.)

I have had this issue previously, but just ignored it. I can't in this case. Does anyone know what is causing this?

Sue

 

[Galaxiom]

I must be misunderstanding because a column cannot be referred to directly in a query.

However the number is still the number wherever it appears. It would need the Format function applied in the query if you wish to display it formatted in the query itself.

Normally though the query would be used as a RecordSource of a form of report and the format applied to the textbox.

 

[Design by Sue]

Sorry - There is a combo box on the form that the user can select the name of the item from. That has a row source of a query of my table. Based on that selection the rest of the form displays the remaining records in the query by using the code =[Name].[Column](2), etc. It is the ID number for which I am having a problem formatting. Strange thing is that the ID is formatted correctly if you run that query. It is somehow loosing the formatting by being put into the text box on the form.

Sue

 

[Galaxiom]

Format is just a display setting. It won't come across to the form.
Put the same Format property as the table into the textbox Format property on the form.

It probably appears on the query because it got it from the table. Tables and queries are almost the smae thing to Access in many ways. But a Form is a different kind of object entirely.

 

[Design by Sue]

And there is the problem - I have the format property for the text box set exactly as I have it everywhere else in the database, but it will not reflect the formatting when the box is a bound column to another control. (thanks for the help in expressing this). This is not the first time I have seen this behavior, and as I said previously, I had to ignore it before but can't in this instance.)

Thanks
Sue

 

[Design by Sue]

Anyone know what to do with this? The format property of the text box not having an effect on the text box when the box is bound to a column of another control on the form.

I am stopped at this point and can't go further so help greatly appreciated.

Sue

 

[boblarson]

Anyone know what to do with this? The format property of the text box not having an effect on the text box when the box is bound to a column of another control on the form.

I am stopped at this point and can't go further so help greatly appreciated.

Sue

What is the control source? If it is a formula, you will have to format it THERE not as the text box format. You should always try to format at the very last place you want to use it.

 

[Design by Sue]

The control source is =[Description].[Column](2). Description is a combo box that the user selects the item from by it's description. There are no formulas involved. It it taken from column 2 of the query that is the rowsource for the combo box.

Sue

 

[Design by Sue]

FYI the formatting I am trying to apply is 00000;-00000;00000;"Null", which is what I found to force the number to 5 digits, with leading zeros to make it always 5 digits. I have tried to use the afterupdate event in a number of places but it doesn't recognize the sequence. When I trimmed it down to 00000 - access trimmed it to 0, which doesn't do what I need.

Sue

 

[Design by Sue]

Sorry for machine gun posts, but I just found the following, which access let's go through, but it has no effect. I put this in the afterupdate event of the combo box.

Me.Item_ID.Format = Format$(lngNum, "00000")

Sue

 

[boblarson]

You would format at the formula (expression I really mean) level:

=Format([Description].[Column](2),00000;-00000;00000;"Null")

 

[Design by Sue]

Not sure what you mean - I tried to put your code as the control source for the text box, but access resets it to =[Description].[Column](2)

Where should I put this?

Sue

 

[boblarson]

Not sure what you mean - I tried to put your code as the control source, but access resets it to =[Description].[Column](2)

Where should I put this?

Sue

Yes, you put it into the control source (not the format) - make sure you have. And, make sure your form is in design view before putting it there. And, you might try removing the format from the control's format property if it is still there as that might be causing it to do this.

 

[Design by Sue]

There is no format in the format property. And I am in design view to add the code. I even save when I enter it, but when I go to form view to test it, access resets it to =Format([Description].[Column](2),00000;-00000;00000;"Null")

Sue

 

[Design by Sue]

Just for laughs, I tried to enter the code in the build of the control source and I get a message "You omitted and operand or operator, you entered and invalid character or comma, or you entered text without surrounding it in quote marks>"

Does that mean anything that might help get through this?

 

[boblarson]

Ooops! My bad. Put quotes around it like this (Enlarged to show better):

=Format([Description].[Column](2),"00000;-00000;00000;'Null'")

 

[Design by Sue]

Now I get The expression you entered is missing a closing parenthesis, bracket or vertical bar.

 

[boblarson]

Now I get The expression you entered is missing a closing parenthesis, bracket or vertical bar.

Post what you have currently and did you remember to either change the double quotes around the word NULL to use single quotes or did you copy and paste what I wrote and use that?

 

[Design by Sue]

I copied and pasted what you posted - or at least I thought I did. I just retried it and Hallelujah it worked. I am working on a Mac running windows, so I must have used control c instead of command c and didn't get the copy correct.

Thanks for coming to my rescue again

Sue