Transfertext

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Transfertext

Post by D Willett »

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 ?
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfertext

Post by HansV »

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".
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Transfertext

Post by D Willett »

Would this be the same for dates, ie:

27/10/2009 00:00:00
to
27/10/2009

Invoice Date: Format([INV_DTE],"##/##/####")
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfertext

Post by HansV »

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")
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Transfertext

Post by D Willett »

Cheers Hans I'll give it a go.

Regards
Cheers ...

Dave.

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Transfertext

Post by D Willett »

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 ??
Cheers ...

Dave.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfertext

Post by HansV »

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. :aflame:
Getting around it would require writing a lot of VBA code, I fear...
Best wishes,
Hans

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: Transfertext

Post by D Willett »

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
Cheers ...

Dave.