Replacing decimal point with a comma

[RexesOperator]

I'm trying to replace the decimal in a number with a comma. Both numbers come from the same table/query. Both are double, standard with three decimal places.

This works: =Replace([NBCC30],".",",")

This doesn't: =Replace([JANDRY2pt5%],".",","). The number remains in its original form.

I've been at this for an hour now, trying various combinations, including copying the not working one into the working one. The result is the same.

I remember seeing something about a love/hate relationship with Access. Right now, I love to hate it!

 

[boblarson]

It may be that you are experiencing pain due to using special characters in your field names: JANDRY2pt5%

I would rename that field to JANDRY2pt5Pct and any other field names that use special characters as they will only cause you grief at some point along the way.

Never use Access reserved words or special characters (except underscores _ ) in object names. I would also never use spaces in your names either as it makes life a LOT easier when doing coding or queries.

 

[RexesOperator]

I was hoping to avoid that, because it means changing about a dozen variables in a couple of dozen queries, forms and reports. It will also make some very long names for some of the controls. You may have noticed I at leased use pt for decimal!

Sigh.

I'm beginning to think Robert Mitchum worked with Access (for reference see an American film classic called "Night of the Hunter" made in 1955).

 

[boblarson]

I totally understand your pain in having to go change things. But, it will really work out better in the long run. Plus, you'll now know not to do that again. (I've been there and had to do that many years ago when I started out, and it was not fun fixing it all).

 

[RexesOperator]

The % was the problem. It all works now. And surprisingly, the name change wasn't that onerous. I'm only working on two reports right now, but their queries picked up the changes correctly.

All smiles for now (until the next Access speed bump..)

 

[RexesOperator]

Speed bump:

Last question tonight, I promise:

=Replace([PCPNANNUAL],".",",") gives a #Error message, but

=Replace([RAINANNUAL],".",",") doesn't.

I have tried deleting the text box, and copying in another one from another report that uses the same field. I have even tried copying the name from the query to make sure it was spelled correctly. I have closed the database and opened it again. I also moved each text box to make sure there was no one lurking underneath (been there done that already).

Any other ideas? It's not a non-zero issue since the default value in all these numeric fields is -999 (there is a reason for doing this).

All other functions are working fine.

 

[boblarson]

Are the text boxes named the same as the fields? If so, you should rename the text boxes to something like txtPCPNANNUAL.

 

[RexesOperator]

Their current names are:

txtrptNBCCFrenchLetterannualPrecip
txtrptNBCCFrenchLetterannualRain

I tried changing
txtrptNBCCFrenchLetterannualRain to txtrptNBCCFrenchLetteryearlyRain

but that didn't help either.

 

[boblarson]

Sorry, but it doesn't make sense to me either. If the datatype of the two are the same and the names of the controls are different from the field names, it should work the same.

 

[RexesOperator]

When I have time tomorrow I will post a stripped version and see if you can see what's wrong.

I've relatives in Portland - their pictures are almost enough to make move there.

 

[boblarson]

We're experiencing high winds and heavy rain right now though. But, it could be worse - at least we've had very little snow so far this winter.

 

[RexesOperator]

After all that effort the light bulb suddenly went on - there was no default value in the [RAINANNUAL] field. As soon as I put in the default value the function worked perfectly. The reason it didn't work before was because Replace had nothing to Replace!

Here's a giant thanks and the loudest doh! in the forum.

 

[boblarson]

That would do it. Glad to hear you got it sorted.