export problem

[mboe]

I am trying to export a query to a fixed length text file. I setup the specs for the fields and tested the export fine. However, when I made some adjustments to the formating it fails. So I went throught and format field by field testing the export each time and this is a problem I found.

I have a date field formated 11/01/2003. My export spec is 8 characters and dates are put in ymd order with no delimiter. Problem is it exports as 2003111, dropping the 0 in the day field. I need all 8 characters. So I formated the field with Format(datefield,"yyyymmdd") and it looks good. However, the export says my table field is too small to hold the newly formated date. The date has 8 characters and the export specs has 8 characters.

Anyone else have a problem like this?

 

[mboe]

Same problem different field, this time a number field setup for 11 characters.

Works:
Curtailment_Amount_1: IIf(DCount("[loan_no]","fhlb_pi","[loan_no] = '" & [loan_number] & "'")>=1,DLookUp("[amttxn]","fhlb_activity","[effectiveDate] = #" & [effectiveDate] & "# and [Loan_no] = '" & [loan_number] & "' and [amttxn] <> " & [SPrinciple] & " and [txn_code] = 856"),0)*100

shows $235.00 as 23500 so I format it to add the preceding zeros.

Doesn't Work:
Curtailment_Amount_1: Format(IIf(DCount("[loan_no]","fhlb_pi","[loan_no] = '" & [loan_number] & "'")>=1,DLookUp("[amttxn]","fhlb_activity","[effectiveDate] = #" & [effectiveDate] & "# and [Loan_no] = '" & [loan_number] & "' and [amttxn] <> " & [SPrinciple] & " and [txn_code] = 856"),0)*100,"00000000000")

Shows $235.00 as 00000023500 but won't export, field too small.

 

[mboe]

It doens't seem to matter how many characters the field actually has. It still gives the error "field is too small to accept data".

I changed the format format to five characters on the query (i.e. "00000") so $235.00 shows up like 23500 and it still shows as too big for the field. In this case the export specs have the field as 11 characters and I am just trying to give it 5.

I could really use some help on this one. It doesn't seem to make any sense.