Access Export to Txt Decimals

[jarett]

Setting up this export from a query, in the query the number looks like a whole number but when the data is exported it adds a decimal and 2 zeros. This value is coming from a predefined table that I cannot change the format. Should I be able to change the properties within the query to get the correct result in the txt file? This is my query result and the text file it exports. The other number exports correctly, which I am assuming it is a different format because it is left justified.

 

[Joe4]

It may depend on exactly how you are exporting it (manually or by macro or VBA code)?
Can you list out your exact steps?

I would recommend trying to format that field in the query for zero decimals, and try exporting again.
If that works, you are all set. If not, you may need to convert that field to Text in your query with a function before exporting.

 

[jarett]

It may depend on exactly how you are exporting it (manually or by macro or VBA code)?
Can you list out your exact steps?

I would recommend trying to format that field in the query for zero decimals, and try exporting again.
If that works, you are all set. If not, you may need to convert that field to Text in your query with a function before exporting.

Yes I am exporting through a macro. I set up the export specification with the following specs; comma delimited with text qualifier=none. In the wizard it looks correct with no decimal. I went to the query and right clicked the field, properties and selected 0 decimal places, format general number, should I try something else? This is the formula I have to use to get the value if it helps explain,Qty Ordered: IIf(IsNull([PO068_MXPPOTierDistribution]![QuantityOrdered]),([po_detail1]![QuantityOrdered]),[PO068_MXPPOTierDistribution]![QuantityOrdered])

 

[Joe4]

Try wrapping your formula in the "Str" function, which should convert it to Text, in which case it should then export exactly as it appears in the query, i.e.

Qty Ordered: Str(IIf(IsNull([PO068_MXPPOTierDistribution]![QuantityOrdered]),([po_detail1]![QuantityOrdered]),[PO068_MXPPOTierDistribution]![QuantityOrdered]))

See: MS Access: Str Function

Not sure, but it might make you change your Export Specification after that, as it is no longer a numeric field.

 

[jarett]

Try wrapping your formula in the "Str" function, which should convert it to Text, in which case it should then export exactly as it appears in the query, i.e.

Qty Ordered: Str(IIf(IsNull([PO068_MXPPOTierDistribution]![QuantityOrdered]),([po_detail1]![QuantityOrdered]),[PO068_MXPPOTierDistribution]![QuantityOrdered]))

See: MS Access: Str Function

Not sure, but it might make you change your Export Specification after that, as it is no longer a numeric field.

Worked awesome thanks

 

[Joe4]

You are welcome.

Typically, if you have control over the data Table, you choose a number option that does not allow decimals, and I think that would take care of it organically.
But since you don't, we need to address it in the Query, and there are a few "tricks" we can use.