Question for the experts

[mr moe]

hi all,
I have a number field that is 8 digits long, I want to insert an * between every 2 numbers, any of u guys can help. Thanks in advance.

mr moe.

 

[dgm]

Do u only want to display it like this? In a report or form?
Or do u want to store it in the table like this?

If it's just to display in a form or report, change the format property to:
00\*00\*00\*00

 

[mr moe]

No I want to display the new data in the table. Thanks.

 

[neileg]

Why? Tables are for storing data. Forms and reports are for displaying data. In any event, there is no difference between the original data and the reformatted data. You can change the format in the table as dgm sets out. But you should still ask yourself why.

 

[mr moe]

Here is the reason, I have another table which is a text field that has the formate I'm looking for, I want to link the new field with the old one. Basically I know that what I'm asking for is not logical but I 'm really forced to do it that way.

 

[neileg]

So change the format in the table. Go into design mode and for the field you want to change, type dgm's format into the format box.

 

[mr moe]

I can't get it.

 

[mr moe]

Do you have any suggestions, I don't understand your reply.

 

[FoFa]

What about creating a query on the "old" format so it formats that data like the "new" format, and use that query to match the data on? That way you do not have to permanantly change the data or modify an application?

 

[mr moe]

Here is the deal, The new format which doesn't have the dots is the one I need, while the old one I only need part of it. It's good idea for temporarily fix but how do i create the query to remove the dots, any idea? Thanks

 

[neileg]

You can apply formatting in the table, in a form, a report or a query. The principles are exactly the same.

I would always avoid formatting in the table because I prefer to see the 'real' data there, but Access allows you to do this if you want.

Are you saying you don't know how to apply formatting?

Why don't you spell out exactly what your data looks like and what you want it to look like, otherwise we will struggle to help you.

 

[mr moe]

Ok here is an exmaple of the data. I have this number "Text formatted" from excel 22.33.55 , I'm importing this to access on the other hand I have another table in access which has this number 223355 they are identical expect for some reason the section I'm importing from excel has dots in it, I don't know why they choose to do it that way, but that's like 300 rows and I can't do it manually. So what I'm looking for is either ente dots in the access table or remove the dots in the excel. Again they are formatted as text.

Please let me know if I need to explaine more.

 

[namliam]

In excel simply do a search and replace. Search for . and replace by Null [or nothing] then your dots are gone and they are numbers once again. The reason they did it is that it eases the reading of the number.

Regards

The Mailman

 

[mr moe]

Thanks but I dont' think that the find and replace is gonig to solve it, because I have some numbers like this ex:02.35.87 when I do find and replace the number beccomes like this 23587 while the other table has the full number 023587 I don't know what to do I'm lost. Any of you guys know how to insert dots between every two digits for a fixed lenght of six digits.

 

[namliam]

You can use a format(field,"000000") to fill the missing leading zeros

if you must stick to the insertion of dots:
left(field,2) & "." & mid(field,2,2) & "."& right(field,2)
or might be
left(field,2) & "." & mid(field,3,2) & "."& right(field,2)
I forget and to lazy to test at this moment.

Regards

The Mailman

 

[mr moe]

Thanks, How do I insert the dots, do I write an updat query?

 

[namliam]

Yep, you could try writing VBA if you so desire but thats doing it the hard way.

I still say kill the dots (and leading zero's) from the excel file. AND kill the leading zero's from the access table. Change the format fo the field to number and display the dots and leading zero's using formats.... But hey its your DB.... Yours would be the "easy" way, mine would be the "Good" practice/ Harder way

Regards