I am trying to import data from an excel sheet to access to create a table, one of the field is telephone numbers which all got a "0" in front. I think access does not reconganise the initial zero, so after it's done all the zero disappeared.:(
so can anyone please tell me how can I prevent this from hapening?:confused:

many thanks:)

You can probably either go into the spreadsheet and work out a find and replace to remove the zeros for that column.

Or import that column into a text field of your database, then update a number column that will remove the zeros. For example say the telephone number was stored as 01112223333 then in an update query you could use Right([TextTelephoneFieldName), 9).

Apologies, I don't know about creating a macro or using VBA in Excel to precleanse the data prior to importing.

-dK

Sorry for not making myself clear, actually I was seeking to KEEP the "0"s, as the phone No. all got zero in front, and access decide to omit that zero when importing. thanks for helping out anyway.

Ah ... I am sorry, I reread again and now see where you are asking that. Der on me. =]
To my knowledge because it is a number field, Access will always drop the leading zero.

On the import wizard, tell it to use a text field for that column.

-dK

OK i'll try that, thanks

well, when I'm importing using the wizard, the option of data type is grayed out, i.e. i can see the option box but can't choose it.

Hmmm. I've no experience with that happening - so don't know what to tell you except for some ideas on a work around.

You can either go ahead and create a table and import into it or try to trick it. The trick is to insert a row underneath your headings and type in whatever you want in the other columns (or copy and past from the row beneath) but in the telephone column, type in a buncha text gibberish. Since this is the first data row, the wizard will be sure to pick it up and should set the field to text - see if that will force it into text. Then delete your garbage row after import.

-dK

yeah, it's fix now thank you very much, dK.

iloveplmm

since i got you online i got another problem, hope maybe you can solve for me,
i got a database of inventory of books. everytime a book is sold, how can i someway tag or mark it as "sold", or maybe transfer the record to a "books sold" table. so that particular book does not show up when another buyer search for book available?
thank in advance
i can post a copy of my access if you need to look at it

iloveplmm

oops you went off line, nevermind i'll post a new thread.

You could add a field to your book table perhaps called "Status". In the form you use, you could set a combo box to two options; "Sold" or "In Stock".

From your buyer search query, you could use "In Stock" in the criteria for "Status" to only shows those in stock. From your sold query, you could use "Sold" to show those books that were sold.

-dK

i'll give t try, thanks again dK.

iloveplmm