Format to result in 4 digits

[Design by Sue]

My form displays the Employee Number correctly as 0049, yet when I refer to that field in my code it changes it to 49. I need the code results to show the full number with the proceeding zeros. I tried to format the declared value but this did not work. Any suggestions?

strEmpNumber = Format(Me.EmployeeNumber, "####")

The strange thing to me is that the number is formatted correctly in the underlying table so I don't understand why the zeros are being stripped

 

[mike60smart]

My form displays the Employee Number correctly as 0049, yet when I refer to that field in my code it changes it to 49. I need the code results to show the full number with the proceeding zeros. I tried to format the declared value but this did not work. Any suggestions?

strEmpNumber = Format(Me.EmployeeNumber, "####")

The strange thing to me is that the number is formatted correctly in the underlying table so I don't understand why the zeros are being stripped

Hi
I was told recently by "Pat Hardman" that you should not do any formatting of fields in a table.
You can't have the field in the table displaying a Zero.
The only time it will display a Zero is as the default.
If you enter 0049 into the table it will only display the 49

If you want to display the preceding Zero's you would have to make the field a Text DataType.

 

[GPGeorge]

My form displays the Employee Number correctly as 0049, yet when I refer to that field in my code it changes it to 49. I need the code results to show the full number with the proceeding zeros. I tried to format the declared value but this did not work. Any suggestions?

strEmpNumber = Format(Me.EmployeeNumber, "####")

The strange thing to me is that the number is formatted correctly in the underlying table so I don't understand why the zeros are being stripped

There is a difference between the value of a number, and how you DISPLAY that number in a form or report. In this case, for example, the two leading zero's have no meaning, no value. They are only there for DISPLAY purposes. So don't worry about them until you need to DISPLAY the values somewhere for users.

You tried to Format() the number, but Format(), again, is only for DISPLAY purposes and has nothing to do with the underlying value.

Where and why do you need to show this four-digit DISPLAY FORMAT?
===========================

Also, as Mike pointed out, you may be using the wrong data type anyway.

EmployeeNumber is probably not an actual number, in the sense implied in this discussion. You can easily prove it by adding two EmployeeNumbers together. Does that create a valid result? Perhaps easier to see and understand, add two telephone numbers together. Does that result in anything meaningful?

The point here is that text strings can consist of alpha characters, digits, or even a combination of alpha characters and digits. Your EmployeeNumber is really just a string of 4 digits and not an mathematically valid "number" at all.

 

[Gasman]

49 is 0049. In code work on the number. The format is for display only. Or make it a string and a lot of work for yourself.

Formats should not really be used in tables, as it causes the issue you have now. IT IS A FORMAT !!!, the clue is in the name?

 

[Design by Sue]

Ok got that - yes it is a text field and because everything else in the database is working correctly (and I use the EmpNumber on almost every form) I do not want to start making changes such as this. I really don't understand why this one form will not see the number as it is in the table (4 number places) As I said this works on all other forms. I can't see any difference between what I have done on those other forms and on this one. Must be time to walk away because many times if I come back to it another day I see the issue.

Meanwhile if anyone know why this text entry is being trimmed on this form any insight will be appreciate.

Thanks for helping me understand format better.

 

[GPGeorge]

Ok got that - yes it is a text field and because everything else in the database is working correctly (and I use the EmpNumber on almost every form) I do not want to start making changes such as this. I really don't understand why this one form will not see the number as it is in the table (4 number places) As I said this works on all other forms. I can't see any difference between what I have done on those other forms and on this one. Must be time to walk away because many times if I come back to it another day I see the issue.

Meanwhile if anyone know why this text entry is being trimmed on this form any insight will be appreciate.

Thanks for helping me understand format better.

We'd need to see the actual form in question to address your question.

 

[xavier.batlle]

Perhaps this can help you, but I suggest you to listen the advice you have been given:

? Format("49","####") 
49
? Format ("49","0000")
0049

 

[Gasman]

You are seeing a FORMAT in the table, not the real value. Almost as bad as table lookups.
Use the format in your forms and reports NOT tables!!!
Really do not not know how many times we need to say this, as it causes exactly this issue you have now.

 

[Design by Sue]

Perhaps this can help you, but I suggest you to listen the advice you have been given:

? Format("49","####")
49
? Format ("49","0000")
0049

Thank you - if you are suggesting adding that to the code as below - that does not work either. Would there be another place I could add it?


strEmpNumber = Format(Me.EmployeeNumber, "0000")

 

[Design by Sue]

You are seeing a FORMAT in the table, not the real value. Almost as bad as table lookups.
Use the format in your forms and reports NOT tables!!!
Really do not not know how many times we need to say this, as it causes exactly this issue you have now.

The table does not have a format for that field

 

[Gasman]

If it was actually a text field, then I would expect 0049 everywhere?, so you are not telling us everything?

 

[Gasman]

Upload the dB with this table, else we will be here all night.

 

[gemma-the-husky]

This has probably been stated, but "0049" is a text string, not a number filled with leading zeros. I doubt you need to manipulate it as a number.

If it's a text string then values 0049, 049 and 49 are different and not equivalent, which may be important in your system.

 

[GPGeorge]

Thank you - if you are suggesting adding that to the code as below - that does not work either. Would there be another place I could add it?


strEmpNumber = Format(Me.EmployeeNumber, "0000")

FORMAT for DISPLAY means exactly that. It is NOT going to change the actual value in the control, which is a number.

You can Format the value in the control and assign that to a variable. That does NOT change the value itself in the control.

Controls have a Format property. See if you can use that to get the DISPLAY you desire.

 

[Design by Sue]

If it was actually a text field, then I would expect 0049 everywhere?, so you are not telling us everything?

Yeah - I get that. but not on purpose.

 

[Design by Sue]

This has probably been stated, but "0049" is a text string, not a number filled with leading zeros. I doubt you need to manipulate it as a number.

If it's a text string then values 0049, 049 and 49 are different and not equivalent, which may be important in your system.

Yep there is my problem.

 

[Design by Sue]

FORMAT for DISPLAY means exactly that. It is NOT going to change the actual value in the control, which is a number.

You can Format the value in the control and assign that to a variable. That does NOT change the value itself in the control.

Controls have a Format property. See if you can use that to get the DISPLAY you desire.

View attachment 115410

Thanks - that was what i thought.

 

[GPGeorge]

Yep there is my problem.

This has to end.

Either change the field from a Number to a string or apply the appropriate format in controls where it's needed.

 

[Isaac]

IMO the most direct way to do this is to format it as text for display purposes.

Format([ColumnName,"@")

 

[Design by Sue]

Upload the dB with this table, else we will be here all night.

Understood - and thanks. I am going to stop working on this for a bit because as I said many times the problem comes to me when I come back to it. The comments have been very helpful - formatting is not going to help and the fact the the field is a text field I should be seeing the text as it is in the table. This gives me a good start when I come back fresh. If I can't solve it I will try to upload.