Append Query & Formatting

[Krysti]

Hi,

I am trying to append an auto number field from one table to a text field in another table but I want to keep the format (0000000) of the auto number field. Right now let's say the number in the auto number field is 0000021. When I append it into the table with the text field it displays it as just 21. I have tried everything I can think of to no avail. I'm pretty familiar with Access, but only a little bit with code. Can anyone help me out? I would be so grateful

Christy

 

[Pat Hartman]

It will be much easier to work with the other table if you define the field that is to hold the autonumber as Long Integer.

To convert a number to text with leading zeros, you need to use the Format() function. The format assigned to the field in your table or form has NOTHING to do with how the data is stored or how it will be exported.

Modify your append query to include the Format() function.

 

[Krysti]

Hi Pat,

Thanks for the reply. Unfortunately I can't change it to Long Integer because I need text in that field also for some records. I tried using the format function, but can't seem to get the code right. Would you be able to give me an example of using the format function along with a field name for the Append To:? Sometimes I can figure out how the code works and sometimes I'm just a complete idiot!

The thing that is really frustrating is if I use Copy and Paste, it copies it with the formatting.

Frustrating!

Krysti

 

[Pat Hartman]

Open the query in QBE view. In the Field cell that references the autonumber field, replace the value with:

Format(YourField,"0000000")

 

[Krysti]

Thank you so much!! I really really appreciate it. I just wish I would have posted a LOT sooner. I wasted days and days trying to get this to work. You are great!

Krysti

 

[directormac]

I wasted days and days trying to get this to work.

Krysti -

That time was [dialect]"so not wasted".[/dialect] Slogging through problems is the way a lot of folks on this list learned. At the moment, there seems to be a rash of folks around here who want every scrap of info fed to them without even trying to figure it out for themselves first. Kudos to you for not being one of them.

--Proud Mac

 

[Krysti]

Definitely not one of them. That is how I learned Access. I will drive myself absolutely crazy before I ask for help

Thanks again!