Append query dropping leading zero 2

[Moss100]

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

 

[Andrzejek]

By an 'append query' I understand it as an Insert statement.
So, somewhere in your app there should be something like:
[tt]
Insert INTO tbl_MyTable (..., BankNumber, PhoneNumber, ...)
VALUES (..., '001234000', '0008765400', ...[/tt]

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:

[pre]
UPDATE tbl_MyTable Set
...
, BankNumber = '001234000'
, PhoneNumber = '0008765400'
, ...
Where MyID = 12345
[/pre]
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

 

[Moss100]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Andrzejek]

Withholding the evidence…

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