Help with append query

[denileigh]

Hello,

I have an append query that kicks misc. data into a 500byte string which is required by many of our customers.

We have a customer however, who is requesting modifications. What I need is a field in the append query what will count each line and enter the corresponding line number in the string formatted as 000.

For example, if there are 5 lines in the file than the first would be 000 and the second 001 and so on.

If there are 10 then 001, 002 - 010 etc.

I have no idea how to have it count each line and then format the output as per above. Can anyone help?

Thanks so much!

 

[DCrake]

It would only be able to count the number of lines if you are using a line terminator as opposed to a normal wordwrap line feed. Are you using a normal memo field to store your misc data into it? and can you offer a sample of the contents?

 

[denileigh]

Whew - this is over my head. lol I'm trying though.

The append query throws the data into a table. I then open the table and export it to a fixed with .txt file that kicks out the 500byte line...for example:

1HCPT 11314 UBR ACFX072335TE080521682782 CSAB 1 1 8030RR 28 8030RR 1 196950000000000DY Rubberlining Interior E


However, they can wind up with up to 50 lines or so.

Does that help explain any?

 

[DCrake]

So the export that is creating the txt file is the source data. You need to look at how yu are creating the txt file.

If everything worked as you wanted what would the txt file look like?

 

[denileigh]

The append query pulls the data and appends it to a table. I then open the table and export the table as fixed width data, the result of which is posted above. I'll put it here again.

Output of .txt file is as such:

1HCPT 11314 UBR ACFX072335TE080521682782 CSAB 1 1 8030RR 28 8030RR 1 196950000000000DY Rubberlining Interior E


I need to add a the field that counts the lines somehow.

 

[DCrake]

The append query pulls the data and appends it to a table.

Firstly does the append query append the data into one or more fields in the table

I then open the table and export the table as fixed width data

How many fields are they in the table

the result of which is posted above. I'll put it here again.
1HCPT 11314 UBR ACFX072335TE080521682782 CSAB 1 1 8030RR 28 8030RR 1 196950000000000DY Rubberlining Interior E

What are you using as a delimiter?

I need to add a the field that counts the lines somehow.

in the above example how many lines would you expect to see?

 

[denileigh]

DCrake:

Thanks for helping. I set the basics of this up for another customer years ago and am having trouble remembering how I did it - that plus I have no idea how to make it count. Let me see if I can answer you below.

The table has 52 fields as does the corresponding append query. There is no delimiter - the field widths are set in the table itself and when I choose fixed width in the output options, it throws them into correct format.

Here is what the output string would look like with the counter inserted (in bold):
1HCPT 10933 UBR ACFX072334TE080118682782 CSAB 1 1 8009RR 09 8009RR 1 038000000000000DY Strip Hydro Blast Water 00001 E 659968

 

[DCrake]

Send me a copy pf your db to look at.

 

[denileigh]

Is there a way to shrink it and do that? We've been using it for years and it's 57,000 kb+.

Can we add a field to the query that counts somehow?

 

[denileigh]

Or to the table?

 

[DCrake]

Have you run a compact an repair lately? You can also zip it up if it is still to big.

 

[denileigh]

No, I haven't run one - I'll do that. As far as the database though, it also has employee records, social security numbers, etc. in it so I'm just not at liberty to share it.

I'm still thinking maybe the table could count rows and total or something? NO?

 

[DCrake]

If you can just send the bits that are of concern that would do.

 

[denileigh]

DCrake,

Thanks again for all your help. I've figured out how to number the lines. I put an autonumber column in the table and it's working GREAT! It is formatted properly and everything.

The problem now is that when I export it to a .txt file, it is losing the formatting which I am hoping is a much easier problem to fix.

The table is showing the proper format (ie: 00002) however, the output becomes 2.

Can you help?

Thanks again so much!

 

[vbaInet]

The output on a query or on a form?

 

[denileigh]

I have an append query that throws data into a table which is then exported into a fixed width .txt file.

That particular field is losing the format when exporting.

 

[vbaInet]

If you use a query and format the field you can export that to the text file and it will preserve the formatting.

1. Create a query based on the appended table
2. Use the Format() function to format the ID field in the query
3. Then export the query to the text file

 

[denileigh]

vba,

I cannot use the append query to do so because I cannot figure out how to pull all the data in and create a field that will auto number each line, hence, the auto number line in the table.

Is there a way to make the table output the data in a certain format?

Thanks,
Dianne

 

[vbaInet]

Maybe you want to re-read this:

1. Create a query based on the appended table

This means, Create a SELECT query and use that new table in the query. Pull all the records from the table INTO the query. I didn't say use an append query. Do you understand now?

Select the table, click Query Design, include all the fields from this table.

 

[denileigh]

So, I need to run my clear records query, then my current append query to the 500byte table and then create yet another table & select query to make that field output properly?