Add a zero to the front of a text string

[ooch1]

Hello,

I am trying to add a zero to the front of an account ID, but am not sure what funtion to use??

e.g thranform acc no. 223422 into 0223422.

I am sure there will be a simple solution.

Cheers

Ooch

 

[scriverb]

If you want to permanently add a zero then you need an Update query for that.

Update [red][i]yourtablename[/i][/red] as A Set A.[Account ID] = "0" & A.[Account ID];

If you just want to display it with a leading zero(0) then use the Format function when displaying the field.

Format([Account ID], "0000000")

Let me know if either of these work for you situation.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[ooch1]

Scriverb,

Thanks for your help, but it has not quite worked, the qry ran ok, but did not change the results for whatever reason. I think that what may be completing matters could be the fact that the acc id is a text field.

Therefore, do i need to convert the account id to a number first, or can it be done just as text?

Ooch

 

[scriverb]

If the field is text then the query as I provided should add a zero on the front. What is the size of the text field. If too short it will truncate the zero off. If you change it to a number (long integer) a zero on the front has no effect but you can just use the Format function to display it with leading zeroes.

The decision now is what field type it is and do you really want to change it. All through your database the display and expressions will have to be modified if you change to a number. Expression comparison to a text field requires quotes around the value. If you can just expand the size of the field then the query should work. You entry of the datafield in a form then should be modified so that leading zero is added. this can be done with the InputMask and Format properties of the field.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[ooch1]

Scriverb,

For some reason when I put the formula in all i get is #name errors.

The sql for the qry is as follows:

SELECT CC_CampaignReport_20040518.ACCOUNT_NO,

Format$([CC_CampaignReport_20040518]![ACCOUNT_NO],"00000000000") AS ACCOUNT_NO,

CC_CampaignReport_20040518.SEGMENT, CC_CampaignReport_20040518.FULL_MPAN, CC_CampaignReport_20040518.PRICE_PLAN_ID, CC_CampaignReport_20040518.HIGH_LOW_IND, CVDate([CC_CampaignReport_20040518]![AGREE_START_DATE]) AS AGREE_START_DATE, CVDate([CC_CampaignReport_20040518]![AGREE_END_DATE]) AS AGREE_END_DATE, CC_CampaignReport_20040518.DISTRIBUTOR, CC_CampaignReport_20040518.STATUS, CC_CampaignReport_20040518.AGREE_TYPE, CC_CampaignReport_20040518.TIER_TYPE, CVDate([CC_CampaignReport_20040518]![NEXT_BILL_DATE]) AS NEXT_BILL_DATE, CC_CampaignReport_20040518.SPARE
FROM CC_CampaignReport_20040518;

Can you spot why this is not working for me??

Your help with this is much appreciated.

 

[scriverb]

If the field ACCOUNT_NO a string or a numeric field?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ooch1]

It is defeinelty a text field. It is no longer erroring though.

What it is doing is just leaving me with blank fields so where it should read "3248970" it reads " ".

Would it be easier if i converted the field to a numeric format, or can it be done as text?

Ooch

 

[scriverb]

Definately text. If the field ACCOUNT_NO is a text field then there should be leading zeroes out to 11 characters.

Format([CC_CampaignReport_20040518]![ACCOUNT_NO],"00000000000") AS ACCOUNT_NO,

I have coded the Format function rather than Format$.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ooch1]

Cracked it!!

I just deleted the original statement and started again and it worked fine.

Many, many thanks Sriverb


Ooch

 

[scriverb]

Outstanding. Glad that I could help you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ooch1]

Hello,

I now have a new addition to this problem, as the 0 is required to be on the right of the string as appose to at the start.....have you any ideas how this can be achieved.

Ooch

 

[scriverb]

Update me as to what the condition is of the AccountNo field. How many characters now. do you want to strip off one on the left and add it to the right? What do we need to do to it now. Or, are we starting over with the original data as it was and just need to add one to the right?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ooch1]

Sorry Bob i was a bit vague. Basically, all the same rules apply only the zero that was added to the left, instead needs to be on the right.



On a seperate note, i have a further query regarding changing a date format to text but keeping the date in the YYYMMDD format...should i raise a seperate post??

Ooch

 

[scriverb]

The only way to put a zero on the right is through cancatenation.

[CC_CampaignReport_20040518]![ACCOUNT_NO] & "0" AS ACCOUNT_NO

To convert a date to a string representation of the date use the following:

CStr(Format([datefield],"mm/dd/yyyy"))

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ooch1]

Bob,

Just to let you it has all worked fine, thanks again for your assistance!!

Ooch

 

[ooch1]

Bob,

I have just discovered a problem. This is a make table qry, it makes the table ok, but then i try to change the field size (to 11 bytes) and i lose the zero and it puts a space at the front. Would you know what is causing this problem?

Ooch

 

[scriverb]

If you cancatenate the "9" on the field it actually modifies the value of the field. If you used teh Format function to show the field with the extra "0's" then it really hasn't changed. Which did you do?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]