[RESOLVED] Access XP Query - Preserve number format in calculated field

[jazFunk]

In the image I've circled the formatting I'd like to retain in the calculated field. Can this be done?

[jazFunk]

Either my question is poorly written or this forum area doesn't get much attention.

[Chrissy]

I would guess poorly written.... I'm not really sure what exactly you are asking. What calculated field are you referring too and what is the calculation you are using?

[jazFunk]

I'm not even sure if 'calculated field' is an appropriate term. In the image, the field named 'Station' is a combination of fields 'CS_NUM', 'CS_MP', 'PLUS_MINUS and 'LOC_CD'. The issue is that values from field 'CS_MP', such as '0.000', end up as '0' in the combined field. I'd prefer they retain the '0.000' format. Also note that field 'CS_MP' is number formatted properly in the database to display 3 decimal places. I believe the problem is related to converting it to a string and technically '0.000' is ZERO.

[FunkyDexter]

Unfortunately your picture doesn't show for me so I'm not sure if this answer is apropriate but it sounds to me like you're looking for the Format command in Access.

Your calculated field will have an underlying formula. You simply need to wrap the bit of the formula that's converting CS_MP to a string in a format with the apropriate format mask.

[jazFunk]

Wish I would've noticed this two weeks ago. Oh well, your suggestions was right on target and solved my problem.

Referencing the image above, if anyone can see it, I changed the calculation of the field to:

Station: [CS_NUM] & " " & Format([CS_MP],"#0.000") & [PLUS_MINUS] & [LOC_CD]

And produced, i.e., "54011 0.000+R" instead of "54011 0+R".

Thank you!