Field Length Issue

[Sash]

I'll try to make the long story short.

I'm importing a fixed width file into a table, doing some stuff, and
exporting it again. My problem is that if I import field AcctNum in as a 18
character field, but it only has 10 characters I'm loosing the 8 spaces. Is
there a way when I'm exporting the data to say pad the field? I'm exporting
it through VBA to a file.

Maybe format(rs.fields("AcctNo"), "000000000000000000") ??? Will that work
if AcctNo is a text fields? Guess I was also looking for something a bit less
time consuming because I have about 50 fields.

Thanks,
Sash

 

[Jeff Boyce]

Sash

Nope. "Format" is not the same as "storage".

On the other hand, if you were to use a query to gather what you want to
export, and explicitly set the value to have "padding" and then explicitly
convert it to a character string (see CStr() function), you'll probably get
your full width.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jeff Boyce]

Sash

As another alternative, take a look at Access HELP on Import/Export specs...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Sash]

I'm not storing the data or using a query, but writing it to a file using
vba. For example:

Dim FH as string
Open "c:\Output.txt" for Output ast #1
FH = rs.Fields("AcctNo") & chr(9) & rs.Fields("Item")
Print #1, FH

So in this example, the file would be 12345 [tab] 8795465498

When I import AcctNo (12345) into the table from the raw data, I'm doing so
as fixed with at 18 characters. In SQL Server if you have it set as a fixed
field, it gets padded with the additional 13 characters. Doesn't seem so in
Access, so I'm loosing those 13 characters to make it 18 and that doesn't
work for what I'm trying to do. I was looking for an easy way to get the
padding and would prefer to do it in my code.

 

[Jeff Boyce]

I thought your original description only mentioned AcctNo.

Are you working with two fixed width fields instead of one?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I'm not storing the data or using a query, but writing it to a file using
vba. For example:

Dim FH as string
Open "c:\Output.txt" for Output ast #1
FH = rs.Fields("AcctNo") & chr(9) & rs.Fields("Item")
Print #1, FH

So in this example, the file would be 12345 [tab] 8795465498

When I import AcctNo (12345) into the table from the raw data, I'm doing
so
as fixed with at 18 characters. In SQL Server if you have it set as a
fixed
field, it gets padded with the additional 13 characters. Doesn't seem so
in
Access, so I'm loosing those 13 characters to make it 18 and that doesn't
work for what I'm trying to do. I was looking for an easy way to get the
padding and would prefer to do it in my code.

Sash

Nope. "Format" is not the same as "storage".

On the other hand, if you were to use a query to gather what you want to
export, and explicitly set the value to have "padding" and then
explicitly
convert it to a character string (see CStr() function), you'll probably
get
your full width.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.




.

 

[Sash]

All 50 fields are imported as fixed width and I want them to be padded to
remain however many characters they're set to be. Two examples below:

AcctNo, text, length: 18
Item, text, lenght: 6

However the data imported for each is:
AcctNo: 12345
Item: 654321

Item woud be fine as it contains 6 characters. AcctNo, I would need to add
13 spaces for it to equal 18.

I thought your original description only mentioned AcctNo.

Are you working with two fixed width fields instead of one?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I'm not storing the data or using a query, but writing it to a file using
vba. For example:

Dim FH as string
Open "c:\Output.txt" for Output ast #1
FH = rs.Fields("AcctNo") & chr(9) & rs.Fields("Item")
Print #1, FH

So in this example, the file would be 12345 [tab] 8795465498

When I import AcctNo (12345) into the table from the raw data, I'm doing
so
as fixed with at 18 characters. In SQL Server if you have it set as a
fixed
field, it gets padded with the additional 13 characters. Doesn't seem so
in
Access, so I'm loosing those 13 characters to make it 18 and that doesn't
work for what I'm trying to do. I was looking for an easy way to get the
padding and would prefer to do it in my code.

Sash

Nope. "Format" is not the same as "storage".

On the other hand, if you were to use a query to gather what you want to
export, and explicitly set the value to have "padding" and then
explicitly
convert it to a character string (see CStr() function), you'll probably
get
your full width.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I'll try to make the long story short.

I'm importing a fixed width file into a table, doing some stuff, and
exporting it again. My problem is that if I import field AcctNum in as
a
18
character field, but it only has 10 characters I'm loosing the 8
spaces.
Is
there a way when I'm exporting the data to say pad the field? I'm
exporting
it through VBA to a file.

Maybe format(rs.fields("AcctNo"), "000000000000000000") ??? Will that
work
if AcctNo is a text fields? Guess I was also looking for something a
bit
less
time consuming because I have about 50 fields.

Thanks,
Sash


.

.

 

[Stuart McCall]

I'll try to make the long story short.

I'm importing a fixed width file into a table, doing some stuff, and
exporting it again. My problem is that if I import field AcctNum in as a
18
character field, but it only has 10 characters I'm loosing the 8 spaces.
Is
there a way when I'm exporting the data to say pad the field? I'm
exporting
it through VBA to a file.

Maybe format(rs.fields("AcctNo"), "000000000000000000") ??? Will that
work
if AcctNo is a text fields? Guess I was also looking for something a bit
less
time consuming because I have about 50 fields.

Thanks,
Sash

You can declare fixed length string variables and vba will do the padding
for you. Eg:

Dim strAcctNum As String * 18
strAcctNum = rs!AcctNum

When strAcctNum is written to the file it will be exactly 18 characters
long.