A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need to have a field always have 13 characters & insert leading ze



 
 
Thread Tools Display Modes
  #1  
Old September 21st, 2009, 06:14 PM posted to microsoft.public.access.tablesdbdesign
WolfDog
external usenet poster
 
Posts: 4
Default 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  
Old September 21st, 2009, 06:22 PM posted to microsoft.public.access.tablesdbdesign
Dorian
external usenet poster
 
Posts: 542
Default 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  
Old September 21st, 2009, 07:37 PM posted to microsoft.public.access.tablesdbdesign
WolfDog
external usenet poster
 
Posts: 4
Default 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  
Old September 21st, 2009, 07:59 PM posted to microsoft.public.access.tablesdbdesign
Clifford Bass[_2_]
external usenet poster
 
Posts: 1,295
Default 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  
Old September 21st, 2009, 08:20 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old September 21st, 2009, 08:30 PM posted to microsoft.public.access.tablesdbdesign
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old September 21st, 2009, 08:37 PM posted to microsoft.public.access.tablesdbdesign
WolfDog
external usenet poster
 
Posts: 4
Default 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  
Old September 21st, 2009, 08:38 PM posted to microsoft.public.access.tablesdbdesign
WolfDog
external usenet poster
 
Posts: 4
Default 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  
Old September 21st, 2009, 09:02 PM posted to microsoft.public.access.tablesdbdesign
Clifford Bass[_2_]
external usenet poster
 
Posts: 1,295
Default 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  
Old September 22nd, 2009, 02:50 AM posted to microsoft.public.access.tablesdbdesign
Larry Daugherty
external usenet poster
 
Posts: 1,012
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.