Undefined funtion 'Replace' in expression, how to create work around without using SQL
Posted on 2006-03-29
I apologize for the length. In reading previous postings, more info is better than not enough.
How do I strip off the qualifier (<) in a column that has positive numbers and qualifier + numbers in Access without using the SQL “replace” function?
How do I get Access to automatically populate those numbers in a new column in the query so when I open the query the new column with just numbers in it is automatically updated?
What I have:
Access 2003 SP1
Read only linked tables
Query with filter to the tables
What I want to do:
Connect to the query with stats software program - would like to open the stats software and get the up to date information
Error when connecting to query:
Couldn't Open External Database. Error(3085)
Undefined function 'Replace' in expression.
I have spent endless hours on the internet looking for an answer and found your website. I paid the fee, killed a tree printing off all the posting related to the ‘replace’ function and read them. I actually thought I could do this on my own….. Please help! Let me start off with saying I have not had any official training for access. I am a self taught person so if you are going to answer, please take it slow.
I have a dB that has linked tables where I do not have access to change them - read only linked ODBC. I have created a query that combines the information that I need from these tables and filter out a subset of the data based on M#. I then have to create another column in the query to house modified data (stripping out the < or > with the replace function = Result: Replace([CSresult],"<",""). The query works exactly how I want it to but I am not able to connect the stats software to the query - I get the error message above. Did a little research and called some people and found (whether is it correct or not, I really couldn't tell you - remember I am an ordinary girl who is learning on the fly) that Jet Engine is used to talk between Access and the stats software and that Jet Engine has the issue with the "replace" function. Checked to see if I have the most recent version of Access, Jet Engine and the stats software to begin with and I am up to date!
Below is an explanation of what I am doing:
Tables = Table I and Table 2 - liked by ODBC and read only access
Query = Combination of Table 1 & 2 with a filter by M# that looks something like this ….00012345 or ….00000345 or ….00059245. The leading numbers are of no concern but I do filter off the last six numbers and create a specific query for each number - I link the stat software program to this query. (I could also have a query without the M# filter and then create another query with the filter if that would be easier).
The query also has a column of numerical values - CSresult (column holds positive numbers and numbers that have < or > sign in front of them ). Again, I can create the Result column with Replace([CSresult],"<","") but can't connect the stats software program.
M# CSresult Result(this is what I want)
….00012345 0.12 0.12
….00012345 <1.0 1.0 or 1
….00012345 2.18 2.18
….00000345 1.113 1.113
….00000345 195 195
….00000345 <5 5
….00059245 <0.10 0.10 or 0.1
….00059245 <1.00 1.00 or 1
….00059245 <20.00 20.00 or 20
Think I have figured out a portion of this puzzle by reading the posts but I need someone to hold my hand and tell me exactly what I need to do.
(create a module)
Strip off the < in VB code since I am not having any luck in SQL and it doesn't seem anyone else is doing it in the postings
Get the result into a string variable
strResult = Replace(strResult, ">", "")
strResult = Replace(strResult, "<", "")
strResult = Replace(strResult, "=", "")
I don't have a lot of time to learn VB at this moment or how to use the module portion of Access. What do I need to do to strip off the < sign and automatically populate a Result column in the query so when I open the query the Result column is there with data in it for any number of rows or any additional filters that I apply to that query. Hope I am making sense…. Thanks in advance!