Export query to CSV

[Guest]

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

 

[Ken Snell \(MVP\)]

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")

 

[Guest]

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.

For three decimal places:

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


For date without the time:

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


--

Ken Snell
<MS ACCESS MVP>

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

 

[onedaywhen]

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.

--