In a query, I convert text fields to the correct format ie
Excess: Val([EXS])
etc, and set the properties in the query to out put as i request, example, in the properties of Excess I set the format as standard and decimal places to two.
When I run a macro to export the results it seems the format and decimal requests are ignored.
The output returns 0 instead of 0.00 ?
There must be either a setting for this or an explanation, the people wanting the data express a monetry field should be number format with two decimal places.. ?? How do I produce the export as I have designed ?
Transfertext
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Transfertext
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfertext
The Format property is only for display; the underlying number still has (or may have) many more decimal places.
For export purposes, you could create a formatted text field:
Excess: Format([EXS],"0.00")
Because it is text, it will be exported "as is".
For export purposes, you could create a formatted text field:
Excess: Format([EXS],"0.00")
Because it is text, it will be exported "as is".
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Transfertext
Would this be the same for dates, ie:
27/10/2009 00:00:00
to
27/10/2009
Invoice Date: Format([INV_DTE],"##/##/####")
27/10/2009 00:00:00
to
27/10/2009
Invoice Date: Format([INV_DTE],"##/##/####")
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfertext
For dates, you have to use a specific notation: d = day, m = month, and y = year, so:
Invoice Date: Format([INV_DTE],"dd/mm/yyyy")
Invoice Date: Format([INV_DTE],"dd/mm/yyyy")
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Transfertext
Hi Hans
I've been messing with this for a couple of hrs now, I have the date format locked down but none of the monetry fields output correctly.
The forma command isn't working, ie 0 = 0 as opposed to being 0.00.
The field names are:
Hours
Parts
Excess
Recovery
Other
Repair Cost
Invoice Total
Paint & Materials
SELECT tblEST.Est, tblEST.Sup, tblEST.INV_NO AS [Invoice No], tblEST.TOT_HRS AS Hours, Format([AMD_PRT_CST],"0.00") AS Parts, Format([EXS],"0.00") AS Excess, Format([INV_DTE],"dd/mm/yyyy") AS [Invoice Date], tblEST.VEH_MAK AS Make, tblEST.VEH_MOD AS Model, tblEST.REG AS Registration, tblEST.OWN_PCD AS [Post Code], tblEST.Client AS [Work Provider Name], Format([Bookin],"dd/mm/yyyy") AS [Job Start Date], Format([Completed],"dd/mm/yyyy") AS [Job Finish Date], tblEST.STA, qryRecoveryGRP.[SumOfRecovery Total] AS Recovery, qryNoneRecoveryGRP.[SumOfNone Recovery Total] AS Other, [qryMercTotalsGRP-XS].[Invoice Total Less XS] AS [Repair Cost], qryMercTotalsGRP.[Invoice Total Inc XS] AS [Invoice Total], [qryMercTotalsGRP-XS].SumOfLabour AS Labour, qryMercTotalsGRP.SumOfPaint AS [Paint & Materials], Format(tblEst.Finished,"dd/mm/yyyy") AS Finished, tblEst.ESt & "-" & tblEst.Sup AS [Job Number], tblEST.JobType AS [Job Type], tblEST.VEH_CHA_NO AS VIN
FROM qryMercTotalsGRP INNER JOIN ([qryMercTotalsGRP-XS] INNER JOIN (qryNoneRecoveryGRP RIGHT JOIN (qryRecoveryGRP RIGHT JOIN tblEST ON (qryRecoveryGRP.[Estimate No] = tblEST.Est) AND (qryRecoveryGRP.Supp = tblEST.Sup)) ON (qryNoneRecoveryGRP.[Estimate No] = tblEST.Est) AND (qryNoneRecoveryGRP.Supp = tblEST.Sup)) ON ([qryMercTotalsGRP-XS].Sup = tblEST.Sup) AND ([qryMercTotalsGRP-XS].Est = tblEST.Est)) ON (qryMercTotalsGRP.Sup = tblEST.Sup) AND (qryMercTotalsGRP.Est = tblEST.Est)
WHERE (((tblEST.STA)="F"))
ORDER BY Format(tblEst.Finished,"dd/mm/yyyy");
The format command has been applied to the query fields,
Excess: Format([EXS],"0.00")
But they don't show correctly in the CSV ??
I've been messing with this for a couple of hrs now, I have the date format locked down but none of the monetry fields output correctly.
The forma command isn't working, ie 0 = 0 as opposed to being 0.00.
The field names are:
Hours
Parts
Excess
Recovery
Other
Repair Cost
Invoice Total
Paint & Materials
SELECT tblEST.Est, tblEST.Sup, tblEST.INV_NO AS [Invoice No], tblEST.TOT_HRS AS Hours, Format([AMD_PRT_CST],"0.00") AS Parts, Format([EXS],"0.00") AS Excess, Format([INV_DTE],"dd/mm/yyyy") AS [Invoice Date], tblEST.VEH_MAK AS Make, tblEST.VEH_MOD AS Model, tblEST.REG AS Registration, tblEST.OWN_PCD AS [Post Code], tblEST.Client AS [Work Provider Name], Format([Bookin],"dd/mm/yyyy") AS [Job Start Date], Format([Completed],"dd/mm/yyyy") AS [Job Finish Date], tblEST.STA, qryRecoveryGRP.[SumOfRecovery Total] AS Recovery, qryNoneRecoveryGRP.[SumOfNone Recovery Total] AS Other, [qryMercTotalsGRP-XS].[Invoice Total Less XS] AS [Repair Cost], qryMercTotalsGRP.[Invoice Total Inc XS] AS [Invoice Total], [qryMercTotalsGRP-XS].SumOfLabour AS Labour, qryMercTotalsGRP.SumOfPaint AS [Paint & Materials], Format(tblEst.Finished,"dd/mm/yyyy") AS Finished, tblEst.ESt & "-" & tblEst.Sup AS [Job Number], tblEST.JobType AS [Job Type], tblEST.VEH_CHA_NO AS VIN
FROM qryMercTotalsGRP INNER JOIN ([qryMercTotalsGRP-XS] INNER JOIN (qryNoneRecoveryGRP RIGHT JOIN (qryRecoveryGRP RIGHT JOIN tblEST ON (qryRecoveryGRP.[Estimate No] = tblEST.Est) AND (qryRecoveryGRP.Supp = tblEST.Sup)) ON (qryNoneRecoveryGRP.[Estimate No] = tblEST.Est) AND (qryNoneRecoveryGRP.Supp = tblEST.Sup)) ON ([qryMercTotalsGRP-XS].Sup = tblEST.Sup) AND ([qryMercTotalsGRP-XS].Est = tblEST.Est)) ON (qryMercTotalsGRP.Sup = tblEST.Sup) AND (qryMercTotalsGRP.Est = tblEST.Est)
WHERE (((tblEST.STA)="F"))
ORDER BY Format(tblEst.Finished,"dd/mm/yyyy");
The format command has been applied to the query fields,
Excess: Format([EXS],"0.00")
But they don't show correctly in the CSV ??
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78487
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfertext
Access respected text fields in a previous version, but I see that it now stubbornly insists on converting them back to numeric and ignore the format.
Getting around it would require writing a lot of VBA code, I fear...
Getting around it would require writing a lot of VBA code, I fear...
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Transfertext
We'll not go there then !! I'm just glad it wasn't me making errors.
I traced back to underlying queries and even set the format in those too, just couldn't understand why the formula's wouldn't work.
Regards
I traced back to underlying queries and even set the format in those too, just couldn't understand why the formula's wouldn't work.
Regards
Cheers ...
Dave.
Dave.