Query Export formatting problem 2

[bac2u]

Hi, how do I make a calculated field in a query retain the display format I've chosen for it (000000000) when the field is exported? The field is a dollars and cents field, 5.54 for example, that I've created an expression field for which multiplies the value in the table by 100 (to strip the decimal place), then I've formatted the field to display as 00000000554. It has to have the leading zeros otherwise it won't upload properly to the system I'm taking it to. No matter what I do, in the export file it still displays the value in the field as 554.

What I'm wanting is something like a "format formula as values" function like there is in Excel. Any help would be greatly appreciated.

 

[hneal98]

You can open the query in design mode and then open the propery dialog box and then click on the field you want to format in the query. You will notice that the properties change and you should see a Format property and an input mask. You should be able to use a combination of the two to get what you want.

 

[JerryKlmns]

Istead of formating the field would try to build the field like:

FormatedField:Right("00000000000" & ([Field1]*100),11)

Give it a try

 

[hneal98]

That is a better Idea.

 

[bac2u]

This is terrific!! It works great; however, I have TWO different fields in this query that I need to use this special formatting on, and when I do the second one, I get this message:
"Duplicate output alias "FormatedField". What do I need to do to be able to use this on more than one field of the query?

Here are the two fields I've set up in the query:
FormatedField:Right("00000000000" & ([Field34]*100),11)
and
FormatedField:Right("00000000000" & ([Cost Center Total]*100),11)

These two fields are in separate tables, if that makes a difference.

Thanks so much for this idea! I'm so close now to getting this right!

 

[lespaul]

even if the fields are in different tables, they need different names IN THE QUERY.

If you modify it to:
FormatedField1:Right("00000000000" & ([Field34]*100),11)
and
FormatedField2:Right("00000000000" & ([Cost Center Total]*100),11)

you shouldn't have that problem.

HTH

Leslie

 

[bac2u]

Success! This solution worked perfectly, and I thank all of you so much for your expertise in helping me solve this.
You're the best!!!!!