.dbf export issues

[Oy Vey]

Hi all,

I've run into a problem exporting data to dBase V format from an Access query. Normally, my solution would be to change the export format, but right now that isn't an option.

The process I'm describing works as such: I pull data from a SQL Server database, fold, spindle and mutilate it, then take the results and export them to a .dbf. This process is time tested and 100% automated as most of the jobs that follow this process are pretty straightforward.

The oddball job is different from all the others in that a final query that is actually exported contains a dozen fields with display data generated by an immediate if (field: iif(blah=true, X)). The problem is that since its a generated text type, the fieldsize defaults to 255 on a field with a single character. Multiply that by 12-15 and I get a consistent "field will not fit in record" error.

The obvious solution is to change the query to a make table or append query with predefined fieldsizes, but that would hamper the automation bit if I had to create a separate output table for each one of these jobs. Is there any way to force a fieldsize in a select query?

 

[namliam]

Try doing the ifs in functions, there you can "mastermind" the fields to numbers/strings/booleans.

Regards

 

[Oy Vey]

I don't really see how that would help, quite honestly, and in fact, one field out of the dozen actually does make a user defined function call to generate its single character string output. The problem is that an Access query that contains any logic or function call beyond setting a label will create a text field at maximum size. You can test this by creating any select query, adding switches or iifs and then using it as a make table query. I was really wondering if there was anything specific I had missed, such as a hidden, undocumented easter egg in the QBE that is only accessible if you double click on a grid, rotate the mouse 90 degrees and sacrifice a chicken.

 

[namliam]

Have you tried looking to export specifications? I know you dont need it to export to dbf, but might be an option...

Regards

 

[akt01]

Oy Vey - you said:

"The obvious solution is to change the query to a make table or append query with predefined fieldsizes"

Are you saying that it a maketable query can set the field type/size for each field of the new table? How do you do that?