Export query to CSV

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George - 27 Jul 2006 01:05 GMT

Hi,
I have two export questions:
1. What do i change in my query or table to allow me to export to 3
decimals. At the moment when i export the 3rd decimal gets cut off?
2. 1 field has the date in it and when i export it includes the time. how do
i take out the time and only export the date?
Thanks very much
George

Reply to this Message

Ken Snell (MVP) - 27 Jul 2006 03:52 GMT

Use a query that has calculated fields in place of the actual fields where
you want to change the format of the field's output.

For three decimal places:

My3: Format([RealFieldName], "0.000")

For date without the time:

MyD: Format([RealFieldName], "mm/dd/yyyy")

Signature

Ken Snell
<MS ACCESS MVP>

> Hi,
> I have two export questions:
[quoted text clipped - 5 lines]
> Thanks very much
> George

Reply to this Message

George - 27 Jul 2006 06:31 GMT

Thanks Ken,
i appreciate your help
George

> Use a query that has calculated fields in place of the actual fields where
> you want to change the format of the field's output.
[quoted text clipped - 16 lines]
> > Thanks very much
> > George

Reply to this Message

onedaywhen - 27 Jul 2006 09:11 GMT

> For three decimal places:
>
> My3: Format([RealFieldName], "0.000")

Which rounding algorithm?

SELECT 0.0025 AS RealFieldName,
FORMAT(RealFieldName, '0.000') AS My3

returns '0.003' (i.e. arithmetic rounding),

SELECT 0.0025 AS RealFieldName,
ROUND(RealFieldName, 3) AS My3

returns 0.002 (i.e. banker's rounding).

Which data type? (think schema.ini file)

SELECT 0.0025 AS RealFieldName,
TYPENAME(FORMAT(RealFieldName, '0.000'))

returns 'String' (i.e. converted to NVARCHAR),

SELECT 0.0025 AS RealFieldName,
TYPENAME(ROUND(RealFieldName, 3))

returns 'Decimal' (i.e. original data type retained).

> For date without the time:
>
> MyD: Format([RealFieldName], "mm/dd/yyyy")

Which region?

SELECT DATESERIAL(2006, 4, 1) AS RealFieldName,
MONTH(FORMAT(RealFieldName, 'mm/dd/yyyy'))

returns 1 for UK region and 4 for US region,

SELECT DATESERIAL(2006, 4, 1) AS RealFieldName,
MONTH(FORMAT(RealFieldName, 'yyyy-mm-dd'))

returns 2 for all regions (i.e. 'yyyy-mm-dd' is region independent).

Which data type?

SELECT DATESERIAL(2006, 4, 1) + TIMESERIAL(5, 6, 7) AS RealFieldName,
TYPENAME(FORMAT(RealFieldName, 'yyyy-mm-dd'))

returns 'String' (i.e. converted to NVARCHAR)

SELECT DATESERIAL(2006, 4, 1) + TIMESERIAL(5, 6, 7) AS RealFieldName,
TYPENAME(DATEVALUE(RealFieldName))

returns 'Date' (i.e. original data type retained).

Jamie.

--

Reply to this Message