Query Question

[Joe Blow]

Hello all and TIA...

Access 2003
Win XP Pro

I am writing a mktbl query that pulls data from a SQL Server.

One particular text field is 16 digits long, so the field in the table I am
"making" is a text field and is 16 digits long. However, I also need to run
an append query after the mktbl, where the same field is 17 digits.

When I run it, it truncates the last character from the SQL table and stores
it in my table.

Is there a way to force the mktbl query to say, regardless of how many
characters the field is that I am pulling from, I want this field to be 17
digits?

I tried:

ORDER_NO: Format([ORDER_NO_SQL],"@@@@@@@@@@@@@@@@@")
But it made the field 255 characters.


I can run the mktbl on the second table first, which would force that field
to 17 digits, then the append will store 16 digits in the same table without
problems. I just need to run the "16" before the "17".


Thanks,

-Joe

 

[Brendan Reynolds]

First let's create a source table with a 16-char text field ...

CREATE TABLE TestTable (TestID int PRIMARY KEY, TestText char(16))

And now let's insert some test data ..

INSERT INTO TestTable(TestID, TestText) VALUES(1, "1234567890123456")

And now here's our make-table query ... note how we append a space to create
the 17-char field ...

SELECT TestTable.TestID, TestTable.TestText & " " AS TestText INTO NewTable
FROM TestTable;

If necessary, you can run an update query afterwards to remove those
trailing spaces ...

UPDATE NewTable SET NewTable.TestText = Trim$([TestText]);

 

[Guest]

Hi Joe,

You can likely use code to change the field size, after the initial make
table query. Try Googling for that (Google Advanced Groups) if you feel that
such a solution is really required. On the other hand, why not just create a
text field with 255 characters? Who cares if only 16 or 17 characters are
used. JET does not reserve unused space in a text data type.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________