×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Append query dropping leading zero
2

Append query dropping leading zero

Append query dropping leading zero

(OP)
Hello,

I wonder if someone can help me with an append query.

I need to append bank numbers and telephone numbers to a table.

When I run the append query from design view, the zeros are displayed.

The destination table has the fields set as 'Short Text', but when the append query is run, the destination table / fields drop the leading zeros - this makes bank codes incorrect as well as phone numbers.

For example when a phone numer appears as 077******** in the query, when it is appended it shows 77*****

Many thanks for any help. Mark

RE: Append query dropping leading zero

By an 'append query' I understand it as an Insert statement.
So, somewhere in your app there should be something like:

Insert INTO tbl_MyTable (..., BankNumber, PhoneNumber, ...)
VALUES (..., '001234000', '0008765400', ...


Unless by 'append bank numbers and telephone numbers to a table' you mean you already have these records in the table, but need to UPDATE bank numbers and telephone numbers for each/some record(s).

In this case you would have an UPDATE statements somewhere, something like:

UPDATE tbl_MyTable Set 
  ...
, BankNumber  = '001234000'
, PhoneNumber = '0008765400'
, ...
Where MyID = 12345
 
And if those 2 fields are set as 'Short Text' all zeros should be retained.
Unless you display the data as Numbers for some reasons

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Append query dropping leading zero

(OP)
Thanks as always Andy.

I will look again tomorrow and try to see if I can work out what is going on.

I’m wondering now if the records in the access table which the records are appended to are showing the leading zeros but the zeros are disappearing when I export the table to an excel spreadsheet. I should have mentioned that i was exporting the access table to excel. Thanks Mark

RE: Append query dropping leading zero

Quote:

the [leading] zeros are disappearing when I export the table to an excel spreadsheet

You ought to be EXPORTING to a TEXT file and then IMPORTING into an existing workbook, rather than OPENING a workbook with your data already in it.

When you IMPORT the text data, the IMPORT manager (Data > Get External Data > From Text File) will give you the opportunity to choose the DATA TYPE for the column having the leading zeros as TEXT DATA TYPE and any other columns that Excel might change unexpectedly.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Append query dropping leading zero

Withholding the evidence… hammer

Unfortunately in your case, Excel is ‘helping’ you. Excel ‘looks’ at first, let’s say, 20 rows of data and ‘evaluates’ it. If is sees something that looks like a number (although it is a string) it ‘concludes’: Hey, it is a number column and I will be nice and help the user to threat it right as numbers.

Try what Skips suggests and see if your problem goes away.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close