Exporting to text file

[TH22]

Hello,
I wrote a query that I have a macro setup to run and export the results to a text file. When I run the query, the results are correct when shown in the results (select query) view in Access, but when I extract it to a text file, it doesnt extract it as needed. The problem is related to the field being numeric I think.

The query I am running is:
______________________
SELECT "T9" AS RECTYPE,
"92" AS AGENCYD,
"01" AS LOC,
" " AS FILLA1,
"TR0" AS BATCH1,
[Extract Date].DAY AS BATCH2,
" " AS FILLB1,
SELECT FORMAT(count(*),"0000000") FROM [Lead Data] WHERE [Extract Date].DAY = [Lead Data].CALL_DAY) AS REC_CNT,
" " AS FILLC1,
SELECT FORMAT(sum(right([Lead Data.Phone],4)),"000000000000000") FROM [Lead Data] WHERE [Extract Date].DAY = [Lead Data].CALL_DAY) AS HASH,
" " AS FILLD864
FROM [Extract Date];

______________________

The problem is related to the two embedded select statements within the query.
The first is counting the number of records in the table and returning a number (example: 10) and then adding zero padding to the left (Example: 000010).
The second is counting the last four bytes of a phone number for all the records in the table, and then adding zero padding to the left (Example: 000000000002315).

When looking at this in Access, I see
REC_CNT = 000010
Hash = 000000000002315

... these are both correct.

The problem is when I extract them to a text file.
The field '000010' is extracting into the file as '10 '
The field '000000000002315' is extrating into the file as '23.15 '

WOuld anyone have a recommendation for how I can extract it with the zero padding included and the decimal points removed.

Thanks!

 

[PHV]

What about this ?
SELECT "T9" AS RECTYPE,
"92" AS AGENCYD,
"01" AS LOC,
" " AS FILLA1,
"TR0" AS BATCH1,
E.DAY AS BATCH2,
" " AS FILLB1,
FORMAT(Count(*),"0000000") AS REC_CNT,
" " AS FILLC1,
FORMAT(Sum(Right(L.Phone,4)),"000000000000000") AS HASH,
" " AS FILLD864
FROM [Extract Date] AS E INNER JOIN [Lead Data] AS L ON E.DAY = L.CALL_DAY
GROUP BY E.DAY

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TH22]

Hi PHV.
Thanks for the response. I tried the query, but the result was the same. It looks ok when looking at it in access, but when exported to a text file, it still has the same problem.

 

[TH22]

Hello again, I have tried this from a different angle. My new question seems to be a problem with the export spec.

It seems like the export spec I have is expecting a numeric value, but there is no where to set this in the export specs, so Im not sure why this is happening. I have explained below.

Instead of trying to claculate the number in the query, I have decided to just hard code the number into the query.

If I hard code the value as all zeroes, I am still having the problem where when it is exported, it adds a decimal point in there. (Which Im still not sure why it is doing that, because I have done this elsewhere and havent had the problem).

If I hard code the value as all spaces or 'x's, I get an error message when exporting saying the following (I have condensed it a bit)
---
When exporting to text file, recieve the error'MS Office Access was unable to append all the data to the table'.
It says the problem is related to a key violation where either:
imported data doesnt match the field data type or filed size property in the destination table
-or-
ref integrity rules for a relationship defined between two tables are violated.
---


When I refer to 'hard code' Im saying Im putting this in the select statement:
"0000000000000000" AS HASH
or
" " AS HASH
or
"XXXXXXXXXXXXXXXX" AS HASH



Any help is appreciated. Thanks.

 

[PHV]

How do you export ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TH22]

Hi PHV,
I was exporting by right clicking on the query and selecting export (and I also setup the process as a macro). I then used a export specification to export it into the required positions within a fixed length file.

I have been working with this and I just got it to work.
I recreated the export specs 5 times, somehow on the fifth version, it decided to work correctly. I didnt do anything different and the export layout spec is the same, but for somereason on the fifth one it decided to work. When I have time, I will go back and try and figure out why it didnt work (for curiosity sake). But for now it is working.

Thanks for the help. It is appreciated.

 

[PHV]

At least you've learned that you didn't need the 2 subqueries ...