|
| 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
February 15th, 2007, 05:38 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Adding a prefix number
Hello,
In my query I have a text field called "Location" in this field I usually have a single digit number or a double or triple digit number. What I want to do is if a single digit number appears I want the field to be in four digit: Example if a single digit number "1" appears I want "000" added to the "1" giving "0001" or if I have a two digit number "14" I want the field to be "0014" What I did was using a IIF statement such as IIF([Location]="1", then "0001","") now I know there has to be a better way. Any tips will be appreciated. Thank you in advance. 
|
|
#2
February 15th, 2007, 06:40 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Adding a prefix number
Location4: Right("0000" & [Location],4)
HTH, Rob "CAM" wrote in message ... Hello, In my query I have a text field called "Location" in this field I usually have a single digit number or a double or triple digit number. What I want to do is if a single digit number appears I want the field to be in four digit: Example if a single digit number "1" appears I want "000" added to the "1" giving "0001" or if I have a two digit number "14" I want the field to be "0014" What I did was using a IIF statement such as IIF([Location]="1", then "0001","") now I know there has to be a better way. Any tips will be appreciated. Thank you in advance.
|
|
#3
February 15th, 2007, 06:52 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Adding a prefix number
On Wed, 14 Feb 2007 21:38:29 -0800, "CAM"
wrote: Hello, In my query I have a text field called "Location" in this field I usually have a single digit number or a double or triple digit number. What I want to do is if a single digit number appears I want the field to be in four digit: Example if a single digit number "1" appears I want "000" added to the "1" giving "0001" or if I have a two digit number "14" I want the field to be "0014" What I did was using a IIF statement such as IIF([Location]="1", then "0001","") now I know there has to be a better way. Any tips will be appreciated. Thank you in advance. Try: ShowNum: Right("0000" & [Location], 4) If you want to store the leading zeroes in the table without typing them, you can use code in the data entry Form textbox's AfterUpdate event setting the control using the same expression. John W. Vinson [MVP]
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
