Sorry, I’m not a native English speaker but I hope that I can get my message across anyway.
I have a problem with split forms and tables. If I have a record in my table that contains values such as “000111” the split form’s (connected to that table) datasheet view shows only “111”. So the datasheet view drops of all the zeros. Of course this is not a major problem because the actual data is stored in the table, but it is a bit confusing for the other users. Is there any way to fix it?
Full Version: A Problem With Split Forms
Hi,
What is the data type of your field? Do you have any formatting set at the table level?
Just my 2 cents...
What is the data type of your field? Do you have any formatting set at the table level?
Just my 2 cents...
Is it a text or number field? What version are you using? I tried the same thing on my end (access 2010), and it shows the leading zero's in both the form and datasheet portion if it is a text field.
The data type is number (double). If I change that to "text", it just wipes out all the zeros from the table. But it is actually true - if the datatype is text, the fields are shown correctly in the splitform too. The solution could then be that I add the zeros afterwards... the problem is only that I have thousand records. 
So the question is actually: how can I convert a number field to text field without losing the leading zeros?
So the question is actually: how can I convert a number field to text field without losing the leading zeros?
Sorry, but this really doesn't make sense! If it's a Number Field it won't have leading Zeros! If you enter leading Zeros into a Field defined as a Number Access will drop them when you exit the Field/Control. The only way to have the leading Zeros, as you've just stated, is for the Field to be defined as Text!
You can display Fields defined as Numbers with leading Zeros, by using the Format Function, but what is being displayed is actually a String, which is to say Text.
If your app is truly exhibiting the behavior you state,
the only thing I can think of is that the Form, the Control holding the data or the Database itself is Corrupted. Controls can and do become Corrupted and that would be the first thing that I'd check. You can simply delete the Control then re-create it to find out.
Linq ;0)>
You can display Fields defined as Numbers with leading Zeros, by using the Format Function, but what is being displayed is actually a String, which is to say Text.
If your app is truly exhibiting the behavior you state,
QUOTE (Nes @ May 29 2012, 03:21 AM) 
...If I change that to "text", it just wipes out all the zeros from the table.
....how can I convert a number field to text field without losing the leading zeros?
....how can I convert a number field to text field without losing the leading zeros?
the only thing I can think of is that the Form, the Control holding the data or the Database itself is Corrupted. Controls can and do become Corrupted and that would be the first thing that I'd check. You can simply delete the Control then re-create it to find out.
Linq ;0)>
Hi Nes,
What's being "stored" in the table, and what's being "displayed" to the user "can be" two different things. What are you most concerned about? Do you want to "store" the number with leading zeroes, or just display them with leading zeroes, or both?
Just my 2 cents...
QUOTE (Nes @ May 29 2012, 12:21 AM)
The data type is number (double). If I change that to "text", it just wipes out all the zeros from the table. But it is actually true - if the datatype is text, the fields are shown correctly in the splitform too. The solution could then be that I add the zeros afterwards... the problem is only that I have thousand records. So the question is actually: how can I convert a number field to text field without losing the leading zeros?
So the question is actually: how can I convert a number field to text field without losing the leading zeros?What's being "stored" in the table, and what's being "displayed" to the user "can be" two different things. What are you most concerned about? Do you want to "store" the number with leading zeroes, or just display them with leading zeroes, or both?
Just my 2 cents...
theDBguy: Both. I guess I'm mixing up the terms quite badly because my access is not in english, sorry for that. But the format function could be the answer to my questions, have to try it next time I'm at work. Thanks to you all! 
But the format function could be the answer to my questions, have to try it next time I'm at work. Thanks to you all!
Hi Nes,
If you want to store the leading zeroes, then you definitely need to change the field's data type to Text. To update all existing numbers to text with leading zeroes, we need to know how many characters you want to store in the table. For example, six characters will look something like: 000111 or 001111, or 000011.
Just my 2 cents...
If you want to store the leading zeroes, then you definitely need to change the field's data type to Text. To update all existing numbers to text with leading zeroes, we need to know how many characters you want to store in the table. For example, six characters will look something like: 000111 or 001111, or 000011.
Just my 2 cents...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.