Question csv export adding decimal places

[claris]

Hi people

I have a standard csv export which is adding decimal places to a field which needs to be standard.

I have tried all the normal ways of stopping this (setting the field format within the query and data build itself) and am now stuck as to what else to do. It seems it is ok when opened in Excel, but when opened with Notepad the decimals appear.

Here's my code:

DoCmd.TransferText acExportDelim, , "SALES_", "I:\Statistics\" & Year & Month & Day & "\SALES_" & Year & Month & Day & ".csv", -1


Not sure whether more detail is needed here...

Really appreciate your help.

Thanks
Claris

 

[WayPay]

Try this:

sDate = Format(dtDate, "yyyymmdd")
sFileName = "I:\Statistics\" & sDate & "\SALES_" & sDate & ".csv"
DoCmd.TransferText acExportDelim, , "SALES_", sFileName, -1

Split into multiple statements for clarity. Supply your own dtDate.

 

[claris]

Thanks WayPay but it's a field within the export which is 'growing' a decimal, not the format of the dates.

 

[WayPay]

Have you tried

column_name: Int( [table_name].[column_name] )

as a query expression?

 

[claris]

Yep, still doesn't like it, but thanks!

 

[WayPay]

Grr..running out of ideas .

What is the format for the offending expression set to?

 

[claris]

Tell me about it!
It's set to fixed, zero decimal places.

 

[WayPay]

Now I'm really out of ideas. Can you post your database (or a relevant sample thereof)?

 

[MrQ]

Hi Guys,
What about CStr?

Q

 

[claris]

WHAT!?!? I tried CStr first and it didn't work!!! Now it has! How odd, perhaps changing the query column format and Cstr just bumped it.

Cheers guys - Mr Q - you rule

=]

 

[MrQ]

Thanks Claris
Glad I could help

Q