Export query to CSV

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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")
 
Thanks Ken,
i appreciate your help
George

Ken Snell (MVP) said:
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>

George said:
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
Click to expand...
Click to expand...
 
Ken said:
For three decimal places:

My3: Format([RealFieldName], "0.000")
Click to expand...

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")
Click to expand...

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.

--
 
Back
Top