Format a calculated field in access query, form or report

[Grieve1]

Hi I am able to format the field using either CDbl() or Formatpercent([],2)but because another field has a Dlookup into it this causes errors, I think it is because formatting causes field data type to change to text. So is there any way of formatting field and keeping data type. Many Thanks for any asistance.

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya Grieve1 . . .

[blue]I think it is because formatting causes field data type to change to text.[/blue]

Not only are you correct in the above quote (espcially since the [blue]%[/blue] symbol is appended), but bear in mind, [blue]FormatPercent[/blue] first [blue]multiples by 100![/blue]

So all you need to do is convert those values back to their [blue]origional true[/blue] (divided by 100) [blue]values[/blue]. I have no idea where your using the formatted values in [blue]DLookUp[/blue], but here's how to convert them back:

[blue]   CDbl(Left([[purple][B][I]PercentFieldName[/I][/B][/purple]],Len([purple][B][I]PercentFieldName[/I][/B][/purple])-1))/100[/blue]

You can replace [blue]CDbl[/blue] with [blue]Val()[/blue] or [blue]CSng()[/blue] or what ever you like according to desired accuracy.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886