Replacing a numerical Format

[Tom]

Hi Guys,

I have a table containing numerical information. Anything
larger that a thousand is displayed in the following
format:

1.999 rather than just 1999

I am trying to use an update query to change this
numerical format to contain no point or comma, but
unsuccessfully. Can anyone help?

Tom

 

[Steve Schapel]

Tom,

The first point is that a format is just a format... it affects the way
the data is displayed (appearance), but does not have any relevance to
the actual value, so an Update Query (which works on the value) will not
be applicable,

The second point is that the appearance of the data in a table is not
normally of any importance, as the table should not normally be used
for access to the data, it is a storage purpose. The appearance is
normally more important in the context of a form or report.

In any case, I think if you set the Format property of the field (or
form control) to General Number rather than Standard it will not show
the thousands separator.

 

[Tom]

Hi Steve,

Thanks very much for your help on this. I have tried what
you have suggested, but unfortunately I can't get it to
work. The Data I am using in my base table is actually
imported in from another source as a txt. file, so I am
not actually typing the information in myself. I have
changed the format as you have suggested, but instead of
changing 1.345 to 1345, it is reading it as a decimal and
simply converting the number to 1. Any ideas if I can get
round this?

Regards,
Tom

 

[Steve Schapel]

Tom,

Ok, dealing with data imported from other systems sometimes requires a
bit of fiddling. The behaviour you describe is not the result of the
General Number format I suggested, as this allows for decimals. It
would indicate that you are importing it into a field whose field size
is Integer or Long Integer.

Anyway, if the data is coming in as text, it might work to do it in two
steps. Inport the data from the .txt file into a "holding" table, as
text, at which point your 1.345 will be a string. And then use an
Append Query to move it from there into the required table, where you
have the field set up as a number data type as appropriate, and in your
append query make a calculated field like this...
NumericalValue: Val(Replace([YourTextField],".",""))

 

[Tom]

Steve, sorry for being a pain, but I don't have Replace as
a defined function. Is there an alternative function I
can use or can I somewhere get the VBA code to write a
module to create this function? I'm sure this will work,
as it was kind of what I was trying to achieve at the
start.

I really appreciate your help on this!

Tom

-----Original Message-----
Tom,

Ok, dealing with data imported from other systems sometimes requires a
bit of fiddling. The behaviour you describe is not the result of the
General Number format I suggested, as this allows for decimals. It
would indicate that you are importing it into a field whose field size
is Integer or Long Integer.

Anyway, if the data is coming in as text, it might work to do it in two
steps. Inport the data from the .txt file into a "holding" table, as
text, at which point your 1.345 will be a string. And then use an
Append Query to move it from there into the required table, where you
have the field set up as a number data type as appropriate, and in your
append query make a calculated field like this...
NumericalValue: Val(Replace([YourTextField],".",""))

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

Thanks very much for your help on this. I have tried what
you have suggested, but unfortunately I can't get it to
work. The Data I am using in my base table is actually
imported in from another source as a txt. file, so I am
not actually typing the information in myself. I have
changed the format as you have suggested, but instead of
changing 1.345 to 1345, it is reading it as a decimal and
simply converting the number to 1. Any ideas if I can get
round this?

Regards,
Tom

.

 

[Steve Schapel]

Tom,

Apparently you are using Acces 97 or earlier version. Correct? In that
case, you can do like this...
Val(Left([YourTextField],InStr([YourTextField],".")-1) &
Mid([YourTextField],InStr([YourTextField],".")+1))

Now, you will tell me there is a problem with this because some of the
numbers are more than a million, and they come through in your data like
1.456.789

 

[Tom]

No, I'm using Access 2000. I have tried the function
below but unfortunately I am getting a compilation error.
None of my figures will be over a million, only in the
thousands, so I shouldn't get the error you suggest
below. I don't know why I don't have Replace as a defined
function if it is standard on Access 2000. I'm really
sorry for being a pain about this!

Tom

-----Original Message-----
Tom,

Apparently you are using Acces 97 or earlier version. Correct? In that
case, you can do like this...
Val(Left([YourTextField],InStr([YourTextField],".")-1) &
Mid([YourTextField],InStr([YourTextField],".")+1))

Now, you will tell me there is a problem with this because some of the
numbers are more than a million, and they come through in your data like
1.456.789

--
Steve Schapel, Microsoft Access MVP

Steve, sorry for being a pain, but I don't have Replace as
a defined function. Is there an alternative function I
can use or can I somewhere get the VBA code to write a
module to create this function? I'm sure this will work,
as it was kind of what I was trying to achieve at the
start.

I really appreciate your help on this!

Tom

.

 

[Steve Schapel]

Tom,

No need for sorry, and you are not being a pain. You are making
appropriate use of a technical discussion forum

Ok, now I remember! There was a bug with the Replace function in the
original release of Access 2000, which means it only works correctly in
modules. I understand this bug is fixed in the Office 2000 service
release. If this is the cause of the problem, your choices are:
1. Download and install SR3 for Office 2000, after which Replace should
work as advertised.
2. Use the alternative method I suggested with the Instr() function.
3. Write a custom replacement function in a standard module, and use
this instead. This can be done like this...
Public Function MyReplace(Input As String, Search As String,
Replacement As String) As String
MyReplace = Replace(Input, Search, Replacement)
End Function
.... and then in your query, use like this...
NumericalValue: Val(MyReplace([YourTextField],".",""))

 

[Tom]

Hi Steve,

Thanks for your help on this! I have downloaded the
service packs on to my PC, so that should solve the
problem. Thanks again for your patience.

Tom

-----Original Message-----
Tom,

No need for sorry, and you are not being a pain. You are making
appropriate use of a technical discussion forum

Ok, now I remember! There was a bug with the Replace function in the
original release of Access 2000, which means it only works correctly in
modules. I understand this bug is fixed in the Office 2000 service
release. If this is the cause of the problem, your choices are:
1. Download and install SR3 for Office 2000, after which Replace should
work as advertised.
2. Use the alternative method I suggested with the Instr () function.
3. Write a custom replacement function in a standard module, and use
this instead. This can be done like this...
Public Function MyReplace(Input As String, Search As String,
Replacement As String) As String
MyReplace = Replace(Input, Search, Replacement)
End Function
.... and then in your query, use like this...
NumericalValue: Val(MyReplace([YourTextField],".",""))

--
Steve Schapel, Microsoft Access MVP

No, I'm using Access 2000. I have tried the function
below but unfortunately I am getting a compilation error.
None of my figures will be over a million, only in the
thousands, so I shouldn't get the error you suggest
below. I don't know why I don't have Replace as a defined
function if it is standard on Access 2000. I'm really
sorry for being a pain about this!

Tom

.