If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need to have a field always have 13 characters & insert leading ze
I need to create a table that includes a field that has to have 13 characters
(numbers) in it. If the user only enters 9 digits (or 11), it needs to insert leading zeroes to make the value 13 characters. The value has to include all 13 characters so it can't just be a display thing. The value has to actually be 13 characters long. Thanks! |
#2
|
|||
|
|||
Need to have a field always have 13 characters & insert leading ze
This is one of many ways to do it...
In your form's Before Update event, you code: Me!Number = Right$("0000000000000" & Me!Number,13) -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "WolfDog" wrote: I need to create a table that includes a field that has to have 13 characters (numbers) in it. If the user only enters 9 digits (or 11), it needs to insert leading zeroes to make the value 13 characters. The value has to include all 13 characters so it can't just be a display thing. The value has to actually be 13 characters long. Thanks! |
#3
|
|||
|
|||
Need to have a field always have 13 characters & insert leadin
Dorian,
What if I am not using forms and entering data directly to table (or importing data to table?) "Dorian" wrote: This is one of many ways to do it... In your form's Before Update event, you code: Me!Number = Right$("0000000000000" & Me!Number,13) -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "WolfDog" wrote: I need to create a table that includes a field that has to have 13 characters (numbers) in it. If the user only enters 9 digits (or 11), it needs to insert leading zeroes to make the value 13 characters. The value has to include all 13 characters so it can't just be a display thing. The value has to actually be 13 characters long. Thanks! |
#4
|
|||
|
|||
Need to have a field always have 13 characters & insert leadin
Hi,
Try setting the Validation Rule property for the column to: Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]" And so as to make it kinder to the user set the Validation Text property to something like: Please enter a 13-digit number. Clifford Bass "WolfDog" wrote: Dorian, What if I am not using forms and entering data directly to table (or importing data to table?) |
#5
|
|||
|
|||
Need to have a field always have 13 characters & insert leading ze
Depends on whether you want to STORE thirteen characters for each value or
DISPLAY thirteen characters. If you need to store 13, consider using a Text data type for the field. If you wish to display 13, and plan to "do math" on the values stored, use numeric AND use a format as advised else-thread. Regards Jeff Boyce Microsoft Office/Access MVP "WolfDog" wrote in message news I need to create a table that includes a field that has to have 13 characters (numbers) in it. If the user only enters 9 digits (or 11), it needs to insert leading zeroes to make the value 13 characters. The value has to include all 13 characters so it can't just be a display thing. The value has to actually be 13 characters long. Thanks! |
#6
|
|||
|
|||
Need to have a field always have 13 characters & insert leadin
IMO, don't allow data entry directly into tables. Always use forms for user
interaction. -- Duane Hookom Microsoft Access MVP "WolfDog" wrote: Dorian, What if I am not using forms and entering data directly to table (or importing data to table?) "Dorian" wrote: This is one of many ways to do it... In your form's Before Update event, you code: Me!Number = Right$("0000000000000" & Me!Number,13) -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "WolfDog" wrote: I need to create a table that includes a field that has to have 13 characters (numbers) in it. If the user only enters 9 digits (or 11), it needs to insert leading zeroes to make the value 13 characters. The value has to include all 13 characters so it can't just be a display thing. The value has to actually be 13 characters long. Thanks! |
#7
|
|||
|
|||
Need to have a field always have 13 characters & insert leadin
Jeff,
I need to actually store the value as 13 characters. The intent is to allow the user to input their internal "item #" and have it automatically add leading zeroes and store that value as a 13 character string. The end user of the value requires a 13 digit number. No math will be done...reference only. "Jeff Boyce" wrote: Depends on whether you want to STORE thirteen characters for each value or DISPLAY thirteen characters. If you need to store 13, consider using a Text data type for the field. If you wish to display 13, and plan to "do math" on the values stored, use numeric AND use a format as advised else-thread. Regards Jeff Boyce Microsoft Office/Access MVP "WolfDog" wrote in message news I need to create a table that includes a field that has to have 13 characters (numbers) in it. If the user only enters 9 digits (or 11), it needs to insert leading zeroes to make the value 13 characters. The value has to include all 13 characters so it can't just be a display thing. The value has to actually be 13 characters long. Thanks! |
#8
|
|||
|
|||
Need to have a field always have 13 characters & insert leadin
The intent is to allow the user to input their internal "item #" and have it
automatically add leading zeroes and store that value as a 13 character string. The end user of the value requires a 13 digit number. No math will be done...reference only. The automatic part part is where I am having trouble. "Clifford Bass" wrote: Hi, Try setting the Validation Rule property for the column to: Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]" And so as to make it kinder to the user set the Validation Text property to something like: Please enter a 13-digit number. Clifford Bass "WolfDog" wrote: Dorian, What if I am not using forms and entering data directly to table (or importing data to table?) |
#9
|
|||
|
|||
Need to have a field always have 13 characters & insert leadin
Hi,
For direct entry or imports, I do not believe you can do the automatic padding of zeroes. For imports, you could run a process after the fact that pads those fields that do not have thirteen digits. Or write your own customized import process. Be that as it may, my recommendation would be to go with a numeric/decimal value that allows for up to thirteen digits and has its format set to "0000000000000". This will work in all cases and will always show the zeroes. The end-user does not need the thing stored as a text field with all thirteen characters. Yeah, they may "require" it, but if they always see of thirteen characters, why should they care about what is underneath? When stored as a numeric value, searching will be simplified. No need to type all thirteen digits into the search dialog or other places that ask for an item number. Clifford Bass "WolfDog" wrote: The intent is to allow the user to input their internal "item #" and have it automatically add leading zeroes and store that value as a 13 character string. The end user of the value requires a 13 digit number. No math will be done...reference only. The automatic part part is where I am having trouble. |
#10
|
|||
|
|||
Need to have a field always have 13 characters & insert leadin
Don't know if you found a solution to your issue. The below little
routine returns a 13 character string with leading zeros and the number set to the right. HTH -- -Larry- -- "WolfDog" wrote in message ... Jeff, I need to actually store the value as 13 characters. The intent is to allow the user to input their internal "item #" and have it automatically add leading zeroes and store that value as a 13 character string. The end user of the value requires a 13 digit number. No math will be done...reference only. "Jeff Boyce" wrote: Depends on whether you want to STORE thirteen characters for each value or DISPLAY thirteen characters. If you need to store 13, consider using a Text data type for the field. If you wish to display 13, and plan to "do math" on the values stored, use numeric AND use a format as advised else-thread. Regards Jeff Boyce Microsoft Office/Access MVP "WolfDog" wrote in message news I need to create a table that includes a field that has to have 13 characters (numbers) in it. If the user only enters 9 digits (or 11), it needs to insert leading zeroes to make the value 13 characters. The value has to include all 13 characters so it can't just be a display thing. The value has to actually be 13 characters long. Thanks! |
|
Thread Tools | |
Display Modes | |
|
|