Replace function

[IgorM]

Hi

I have a query that retreives data from a cartain column and converts it to
currency type.

The query in Access 2007 looked like this:

Select CCur([Revenues]![Wn-1]) FROM RevenuesCurrentYear

When I saved it in .mdb format and opened it Access 2003 errors occurred
because values grater than 1000 contained a white space. So I changed the
query to:

Select CCur(Replace([Revenues]![Wn-1],Chr(160),'')) FROM RevenuesCurrentYear

After that it worked just fine. But then I wanted to connect to this query
from Excel 2003. Unfortunatelly I get an error message 'Undefined function
Replace in expression'.
I tried to workaround this issue using custom function:

Function RemoveWhiteSpaces(StringIn As String) As String

Dim NewString As String

NewString = Replace(StringIn, Chr(160), "")
RemoveWhiteSpaces = NewString

End Function

Unfortunatelly I get the same message. How can I resolve this issue?
I will just add that I have all latest updates installed.

Kind regards
IgorM

 

[Dale Fye]

sounds like your [Wn-1 ] field is a string.

Why would you do that to store numbers?

 

[IgorM]

Yes, it is in fact a string type field. It is this way because I use a
joined table - a CSV file that comes from an accounting software.
Unfortunately, due to my regional settings the field separator and decimal
separator is the same in the csv file exported from the programme, i.e.
comma. So without some extra manipulation it is impossible to differentiate
a decimal separator from a field separator. Moreover, the accounting
software adds a thousand separator to those values - white space, character
160. I was also unable to find an option that allows to specify a thousand
separator character in Access link table wizard (there is such an option in
Excel). To overcome this problem I decided it will be better to import it
(or rather link it) to Access as a text field (two text fields two be
precise - one for integrals and second for decimals) and then replace the
white space and concatenate decimals with integrals in a query. In order to
do that I used a CCur function. It worked fine in Access 2007. But then I
had to move to Access 2003. Unfortunately, as it appears there is a
significant difference in a CCur function in Access 2003 as it cannot handle
whitespaces (other converting functions too). Replace function solved the
problem but I cannot use it because it is unrecognizable when trying to use
them in Excel - linked to pivot table.
Hope my explanation helps.

sounds like your [Wn-1 ] field is a string.

Why would you do that to store numbers?


----
Dale

Hi

I have a query that retreives data from a cartain column and converts it
to
currency type.

The query in Access 2007 looked like this:

Select CCur([Revenues]![Wn-1]) FROM RevenuesCurrentYear

When I saved it in .mdb format and opened it Access 2003 errors occurred
because values grater than 1000 contained a white space. So I changed the
query to:

Select CCur(Replace([Revenues]![Wn-1],Chr(160),'')) FROM
RevenuesCurrentYear

After that it worked just fine. But then I wanted to connect to this
query
from Excel 2003. Unfortunatelly I get an error message 'Undefined
function
Replace in expression'.
I tried to workaround this issue using custom function:

Function RemoveWhiteSpaces(StringIn As String) As String

Dim NewString As String

NewString = Replace(StringIn, Chr(160), "")
RemoveWhiteSpaces = NewString

End Function

Unfortunatelly I get the same message. How can I resolve this issue?
I will just add that I have all latest updates installed.

Kind regards
IgorM

 

[Dale Fye]

I've had this problem with Excel in the past as well. Had some monster
queries that I wanted to be able to link to from Excel, but they were using
all sorts of Access and user defined functions, so they would not work when
called from Excel. I eventually had to modify my code so that they queries
created "temporary" tables in another database, and use those tables for my
Excel graphs and tables.

Given your situation, I would probably do the initial import of the data
into a temporary table, with the string functionality that you describe. I
would then write a query that takes the data from this temporary table
(strips the space from the thousands separator, then combines the "decimals
and integrals" into a single number) and appends it to the actual table you
want to use in your application. This is the way I generally handle most
data I import from an external source.

NewField: val(replace([IntegralPart], " ", "") & "." & [DecimalPart])

Once you have done this, you can delete the data from the temp table.

Then, since your data will reside in a numeric field you won't have to worry
about the replace function being called from Excel.

----
HTH
Dale

Yes, it is in fact a string type field. It is this way because I use a
joined table - a CSV file that comes from an accounting software.
Unfortunately, due to my regional settings the field separator and decimal
separator is the same in the csv file exported from the programme, i.e.
comma. So without some extra manipulation it is impossible to differentiate
a decimal separator from a field separator. Moreover, the accounting
software adds a thousand separator to those values - white space, character
160. I was also unable to find an option that allows to specify a thousand
separator character in Access link table wizard (there is such an option in
Excel). To overcome this problem I decided it will be better to import it
(or rather link it) to Access as a text field (two text fields two be
precise - one for integrals and second for decimals) and then replace the
white space and concatenate decimals with integrals in a query. In order to
do that I used a CCur function. It worked fine in Access 2007. But then I
had to move to Access 2003. Unfortunately, as it appears there is a
significant difference in a CCur function in Access 2003 as it cannot handle
whitespaces (other converting functions too). Replace function solved the
problem but I cannot use it because it is unrecognizable when trying to use
them in Excel - linked to pivot table.
Hope my explanation helps.

sounds like your [Wn-1 ] field is a string.

Why would you do that to store numbers?


----
Dale

Hi

I have a query that retreives data from a cartain column and converts it
to
currency type.

The query in Access 2007 looked like this:

Select CCur([Revenues]![Wn-1]) FROM RevenuesCurrentYear

When I saved it in .mdb format and opened it Access 2003 errors occurred
because values grater than 1000 contained a white space. So I changed the
query to:

Select CCur(Replace([Revenues]![Wn-1],Chr(160),'')) FROM
RevenuesCurrentYear

After that it worked just fine. But then I wanted to connect to this
query
from Excel 2003. Unfortunatelly I get an error message 'Undefined
function
Replace in expression'.
I tried to workaround this issue using custom function:

Function RemoveWhiteSpaces(StringIn As String) As String

Dim NewString As String

NewString = Replace(StringIn, Chr(160), "")
RemoveWhiteSpaces = NewString

End Function

Unfortunatelly I get the same message. How can I resolve this issue?
I will just add that I have all latest updates installed.

Kind regards
IgorM

 

[IgorM]

I wanted to avoid the situation when the user is forced to open Access when
the source data is changed. I just wanted the user to swap a CSV file, open
Excel and click refresh in a table. I'm working on a query that will
workaround the problem with white spaces. To do that I use Left, Mid and
Right functions which are ok with an Access-Excel connection.

SELECT CCur(IIf(Len([RevenuesCurrentYear]![Integrals])>8,
Left([RevenuesCurrentYear]![Integrals],InStr([RevenuesCurrentYear]![Integrals],Chr(160))-1)&MID([RevenuesCurrentYear]![Integrals],InStr([RevenuesCurrentYear]![Integrals],Chr(160))+1,3)&Right([RevenuesCurrentYear]![Integrals],3),
IIF(Len([RevenuesCurrentYear]![Integrals])>4,Left([RevenuesCurrentYear]![Integrals],InStr([RevenuesCurrentYear]![Integrals],Chr(160))-1)&Right([RevenuesCurrentYear]![Integrals],3),[RevenuesCurrentYear]![Integrals])
)) AS Wn
FROM RevenuesCurrentYear;

It is fine with values less than 999 999 999. I surely won't have larger
values (it is not such a big company).

I've had this problem with Excel in the past as well. Had some monster
queries that I wanted to be able to link to from Excel, but they were
using
all sorts of Access and user defined functions, so they would not work
when
called from Excel. I eventually had to modify my code so that they
queries
created "temporary" tables in another database, and use those tables for
my
Excel graphs and tables.

Given your situation, I would probably do the initial import of the data
into a temporary table, with the string functionality that you describe.
I
would then write a query that takes the data from this temporary table
(strips the space from the thousands separator, then combines the
"decimals
and integrals" into a single number) and appends it to the actual table
you
want to use in your application. This is the way I generally handle most
data I import from an external source.

NewField: val(replace([IntegralPart], " ", "") & "." & [DecimalPart])

Once you have done this, you can delete the data from the temp table.

Then, since your data will reside in a numeric field you won't have to
worry
about the replace function being called from Excel.

----
HTH
Dale

Yes, it is in fact a string type field. It is this way because I use a
joined table - a CSV file that comes from an accounting software.
Unfortunately, due to my regional settings the field separator and
decimal
separator is the same in the csv file exported from the programme, i.e.
comma. So without some extra manipulation it is impossible to
differentiate
a decimal separator from a field separator. Moreover, the accounting
software adds a thousand separator to those values - white space,
character
160. I was also unable to find an option that allows to specify a
thousand
separator character in Access link table wizard (there is such an option
in
Excel). To overcome this problem I decided it will be better to import it
(or rather link it) to Access as a text field (two text fields two be
precise - one for integrals and second for decimals) and then replace the
white space and concatenate decimals with integrals in a query. In order
to
do that I used a CCur function. It worked fine in Access 2007. But then I
had to move to Access 2003. Unfortunately, as it appears there is a
significant difference in a CCur function in Access 2003 as it cannot
handle
whitespaces (other converting functions too). Replace function solved the
problem but I cannot use it because it is unrecognizable when trying to
use
them in Excel - linked to pivot table.
Hope my explanation helps.

sounds like your [Wn-1 ] field is a string.

Why would you do that to store numbers?


----
Dale



:

Hi

I have a query that retreives data from a cartain column and converts
it
to
currency type.

The query in Access 2007 looked like this:

Select CCur([Revenues]![Wn-1]) FROM RevenuesCurrentYear

When I saved it in .mdb format and opened it Access 2003 errors
occurred
because values grater than 1000 contained a white space. So I changed
the
query to:

Select CCur(Replace([Revenues]![Wn-1],Chr(160),'')) FROM
RevenuesCurrentYear

After that it worked just fine. But then I wanted to connect to this
query
from Excel 2003. Unfortunatelly I get an error message 'Undefined
function
Replace in expression'.
I tried to workaround this issue using custom function:

Function RemoveWhiteSpaces(StringIn As String) As String

Dim NewString As String

NewString = Replace(StringIn, Chr(160), "")
RemoveWhiteSpaces = NewString

End Function

Unfortunatelly I get the same message. How can I resolve this issue?
I will just add that I have all latest updates installed.

Kind regards
IgorM