Exporting dollar amounts to a .csv file drops cents (.00)

[lac55]

I have a query that contains several dollar amount columns. I have used the Format function (Format(fieldname,"Standard") in order to retain the cents. If the amount contains cents, it exports correctly. But if there are no cents (.00), the export drops the .00. I have also used Cstr(Format(fieldname,"Standard")) to no avail. My query results look perfect. The .csv results do not.

I have been having the same problem with exporting date fields where I have stripped off the time portion. The query looks great, but in the .csv file, 0:00 has been appended to every date field.

I have Access 2007 SP2. I am willing to try any suggestions.

Thank you in advance.

[nico5038]

Try to use the second parameter of the format() with "Fixed" or use a mask with 0.00

[Mihail]

Also take a look here !

[lac55]

I will just keep trying different things. I have tried all suggestions and the query results look great. It is during the export that I have trouble. And, the export works just fine on some of my amount columns, but not on all. Thanks for all the suggestions.

[lac55]

I did just now have some success with this last column by using Total Fees: Format([total_fees],"Currency"). I do end up with the $ sign, but I don't think that will be a problem for my end user. I can't explain why the "currency" parameter works and Total Fees: Format([total_fees],"Standard") and Total Fees: Format([total_fees],"Fixed") don't, but at this point, after 3 days, I'm just happy to be able to give my end user the cents on all amount fields.

BTW, when I try Format(currencyfield, "0.00"), the "0.00" automatically changes to "Fixed". When I enter "###,##0.00", Access automatically changes it to "Standard". Is there by any chance a way to use "Currency" and suppress the $ sign? I'm guessing the answer to that would be to use "Standard" or "Fixed", but those are the 2 settings that don't give me the cents portion on this one column.

I even had the database adminstrator check to see if this column in the source ODBC database was set up like all the other amount columns that export correctly. The answer was yes.

Thanks again to everyone for all your suggestions!

[NeoPa]

At which point does this change occur?

I have a saved query with a Format(,'#,##0.00') expression in it and it always loads with that same string parameter exactly as I saved it. I'm using Access 2003 on XP. What about you?

[lac55]

Access 2007 . . . newer isn't always better. As soon as I type "#,##0.00" and hit enter, it changes.

[NeoPa]

Office 2007 is better than diddly.

Just as a last test try using the SQL standard quotes (') instead of the ones Access uses ("). I doubt it will fool it but you may as well try, and it's probably better practice anyway ;-)

[lac55]

I'll give that a try. But just to recap. Everything I try looks great in the query results. It is only after I export the query to a .csv file that I have issues. The query looks great . . . this one column in the .csv file doesn't.

I suspect it has more to do with the TransferText macro action and the Export function from the ribbon than with the actual Access functions. When I go through the Export wizard from the ribbon and see what the results will look like, the cents in that one amount column are not displayed if the amount is even dollars.

[NeoPa]

I'm confused this seems to be a direct contradiction of what you said in post #7.

Is the query ever saved without the string parameter replaced by the appropriate text version ("Standard; Fixed; etc)?

[lac55]

No, the query never saves without replacing the "0.00" with "Fixed" or "###,##0.00" with "Standard".

I'm not sure what Post #7 is. I have only posted 2 questions on this site and admit I know very little about the process. Does #7 means the 7th item in the above stream? If so, what I meant to say was when I am typing in the field inside my query in Design View and type "0.00" as part of the Format function, as soon as I hit enter, Access changes the "0.00" to "Fixed".

[NeoPa]

Post #7 refers to the 7th post in the thread. Each post has its ordinal number included immediately after the timestamp is displayed (EG. 18 minutes ago #11) currently by your latest post. It can be found below the post contents. It's useful as a reference, but can change if earlier posts get deleted for any reason (which they do).

If your QueryDefs are always changed to reflect "Fixed" or "Standard" then the behaviour you describe is entirely unsurprising. These will work fine in most cases, but they rely on the number of DP being determined for you. This is not good when you require that they're set explicitly. Does that all make sense?

[lac55]

Ahhh . . . I see the post # now. Thanks for the explanation. Do you happen to know if there is a way in Access 2007 to keep the QueryDefs from always changing to "Fixed" or "Standard". I would like to have more control over that.

Just an added note. I opened my query in which "#,##0.00" gets automatically changed to "Standard" in Design view. Then I switched to SQL view and changed the parameter back to "#,##0.00" and saved it. It retained the "#,##0.00" in SQL view, but in Design view, it still shows "Standard".

It's odd that if I use the "Currency" parameter, I get my desired result of have cents included with whole dollar amount fields. But "Standard" and "Fixed" don't. There just seems to be no rhyme or reason for this one column.

Oh, and one other interesting tidbit. When I go through the Export wizard, I can see the amount field with cents. But when I click Finish and my .cvs file gets created, the cents are not in the file (again, for this one field).

[NeoPa]

Lac55:
Do you happen to know if there is a way in Access 2007 to keep the QueryDefs from always changing to "Fixed" or "Standard". I would like to have more control over that.

I still use 2003 myself mainly, but when I move you can be sure that it will be to 2010. i'm more than happy to bypass Office 2007 and Windows Vista ;-) In answer though, I know of no way to force that other than the suggestions I made earlier which I suspect had no effect.

Lac55:
It retained the "#,##0.00" in SQL view, but in Design view, it still shows "Standard".

Did you check the SQL view again after saving it in Design View? It's a forlorn hope, I know, but worth checking maybe.

[lac55]

Yeah, I think Access 2007 has issues with the Export function from the ribbon and the TransferText action in a macro. I don't have any trouble in the queries getting the results I want. It's only when exporting to a .cvs file that I have trouble . . . and it isn't on every field. Most are working great.

And yes, I checked the SQL view after saving in Design view. The "#,##0.00" is stil in SQL view. But it didn't affect my results. I had hope there for a minute, especially after seeing the cents in the wizard. But, alas, no cents in the .csv file.

[NeoPa]

I'm sorry I can't be more help. If I had something to play with (A2007 I mean - not your project) I might be able to figure something out, but ...

[Mihail]

Correct me, please, if I am wrong.
For me, the currency format, is only a way to display data.
For me looks like a DATE format (i.e the same date, say 4 February 2012, can be show as 04.02.2012 or 02.04.2012 or 2012.02.04 and so on depending how the date is formatted). But, in internal memory is no change: the date is stored as a number.
The same (I think) happen with the currency formats. After the value, the user can see $, Euro, Lei (this is for Romania :) ) and so on but the number in internal memory is the same.
It is why I think that the format can not be used to solve the problem here.

Sorry if I misunderstand something.

[Mihail]

This work very well for me (see attachment)

[NeoPa]

Mihail:
Sorry if I misunderstand something.

The query being discussed returns a string value representing a formatted value. The underlying, or original, value is not at issue here but only the resultant formatted string. In that sense, the formatted string is the only item of importance.

[Mihail]

Seems that I understand well this time, NeoPa.
The query in attached database (post #18) do that: when exporting as .csv the values are with 2 decimals.

[NeoPa]

Mihail:
For me looks like a DATE format (i.e the same date, say 4 February 2012, can be show as 04.02.2012 or 02.04.2012 or 2012.02.04 and so on depending how the date is formatted). But, in internal memory is no change: the date is stored as a number.

My response was to your post #17, where it seemed clear your understanding was not correct. You are explaining how dates etc are stored - which is irrelevant to the question, as the question deals with string values after conversion (where your explanation has no bearing).

What is in an attached ZIP file is entirely irrelevant. Getting a file to work doesn't necessarily indicate an understanding of the problem, and I'm not about to check that for you even if you wanted me to. It has no associated explanation so is pointless as far as I am concerned (How many would even look at it?). Hence, such an approach is not encouraged. If it helps anyone, then great, but as I've explained to you before, the value of a simple attachment to a thread is very limited.