We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Undefined funtion 'Replace' in expression, how to create work around without using SQL

gagliakm
gagliakm asked
on
Medium Priority
317 Views
Last Modified: 2008-02-01
I apologize for the length. In reading previous postings, more info is better than not enough.
Question:
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!
Comment
Watch Question

Most Valuable Expert 2014

Commented:
First of all welcome to EE. :-)

It would help if we could see your query as it is now.  In the Access DB, go into the design view of the query and then select the "SQL" view, and past that here.
CERTIFIED EXPERT
Top Expert 2006

Commented:
just a thought, check your references, go to code module, then select Tools/References
is everything okay? have u anything listed as MISSING

Author

Commented:
SELECT DISTINCT dbo_SAMPLE.REG_ON, dbo_SAMPLE.LOT, dbo_SAMPLEPARAM.PA_NAME, dbo_SAMPLEPARAM.NRESULT, Replace([CSResult],"<","") AS Result, dbo_SAMPLEPARAM.CSRESULT, dbo_SAMPLEPARAM.LIMIT1LO, dbo_SAMPLEPARAM.LIMIT1HI, dbo_SAMPLEPARAM.LIMIT2LO, dbo_SAMPLEPARAM.LIMIT2HI, dbo_SAMPLEPARAM.LDL
FROM dbo_SAMPLE INNER JOIN dbo_SAMPLEPARAM ON dbo_SAMPLE.SAMPLE_ID = dbo_SAMPLEPARAM.SAMPLE_ID
WHERE (((dbo_SAMPLEPARAM.PA_NAME)<>"UNANALYZED" And (dbo_SAMPLEPARAM.PA_NAME)<>"tank") AND ((dbo_SAMPLEPARAM.NRESULT) Is Not Null) AND ((dbo_SAMPLE.SAMPLE_TYPE) Like "*39184") AND ((dbo_SAMPLE.METHOD_SUBCLASS)="batch") AND ((dbo_SAMPLE.SAMPLE_STATUS)<>"vo1" And (dbo_SAMPLE.SAMPLE_STATUS)<>"Vrj"));
CERTIFIED EXPERT
Top Expert 2006

Commented:
I cant see anything wrong with your use of Replace

I have on occasion had problems with references (mainly due to copy of DB from one O/S to another different one)

sometimes commands like left$ fail

hence the reason to look at your references

Author

Commented:
I don't have any module created to check the code.  I need to create one but don't know what to write.
Most Valuable Expert 2014

Commented:
>> hence the reason to look at your references

Go to the debug window (Ctrl+G) Tools --> References
CERTIFIED EXPERT
Top Expert 2006

Commented:
u dont need code, simply go to code window

u can hit ALT-F11 from Access window

if any listed as MISSING, simply uncheck

make a note of what u uncheck, as u may need it again

then click ok

do select debug then compile, if all works fine, then try your query again

list what is MISSING here as you may need it
if u do need it, simply go back to references, find that reference and check it, then click ok

Author

Commented:
Told you I was new at this!  (Ctrl+G) Tools --> References brings up the reference box where it lists check boxes.  (telling you to make sure I am in the right area) There are so may listed.  The following are checked:
VB for appilcations, OLE automation
MS access 11.0 library, DAO 3.6 object library, activex data objects 2.1 library, common dialog control 6.0(sp3)
I also see JET Expression service type library, JtExporter and JtExplorerLite 1.0 type library that are not checked.

CERTIFIED EXPERT
Top Expert 2006

Commented:
well it looks like your references are okay

try this, create a new module (on left hand side pane, right click, select insert module

add this code

public sub TestReplace

    msgbox  Replace("linda is mad","mad","crazy")

end sub



now type in Debug/Compile

what happens, if no errors then noting happens

click mouse somewhere in that code, then hit F5

what happens, u shud get a msgbox


Most Valuable Expert 2014

Commented:
>> Told you I was new at this!

Don't worry, we'll walk you through this.

>> There are so may listed.  

The ones that are checked should be all you need checked.

Author

Commented:
After i type Debug/Compile it turns red and nothing else happens
Hit F5 and Macros box appears with the Macro Name: TestReplace - i didn't run it or cancel it just left it open.

Thank you all for helping!!!  :)

Author

Commented:
My query works fine with the replace function, i just can not connect the stats software program to the query.  We are not trying to figure out why the query doesn't work are we?

Author

Commented:
I hit the run button and received an error
Compile error:  Syntax error

Author

Commented:
Public Sub TestReplace() came up as a yellow line - Does that means there is a problem with it?
CERTIFIED EXPERT
Top Expert 2006

Commented:
I thought problem was due to Replace, due to undefined expression.

r u using stats program to run query? I wonder if query is run client side, perhaps thats why
whats it written in?
can u specify to run server side? what happens then

Most Valuable Expert 2014

Commented:
>> stats software program

Is it compled VB or something like that?

Author

Commented:
OK created another dB with two columns of numbers - the other databace stated that I did not have exclusive access to the dB at this time.  I still have the same error when trying to connect the stat software with the newly created dB.

In a new module i pasted
public sub TestReplace

    msgbox  Replace("linda is mad","mad","crazy")

end sub
hit the F5 key and the message states linda is crazy

Author

Commented:
New SQL looks like this
SELECT data.csresult, Replace([csresult],"<","") AS Cleaned
FROM data;

Don't know, wish i knew -  stat software is Northwest Analytical Quality Analyst
Most Valuable Expert 2014
Commented:
If you do a make table of the Query
------------------------------------------------
SELECT DISTINCT dbo_SAMPLE.REG_ON, dbo_SAMPLE.LOT, dbo_SAMPLEPARAM.PA_NAME, dbo_SAMPLEPARAM.NRESULT, Replace([CSResult],"<","") AS Result, dbo_SAMPLEPARAM.CSRESULT, dbo_SAMPLEPARAM.LIMIT1LO, dbo_SAMPLEPARAM.LIMIT1HI, dbo_SAMPLEPARAM.LIMIT2LO, dbo_SAMPLEPARAM.LIMIT2HI, dbo_SAMPLEPARAM.LDL
INTO Sample_Table
FROM dbo_SAMPLE INNER JOIN dbo_SAMPLEPARAM ON dbo_SAMPLE.SAMPLE_ID = dbo_SAMPLEPARAM.SAMPLE_ID
WHERE (((dbo_SAMPLEPARAM.PA_NAME)<>"UNANALYZED" And (dbo_SAMPLEPARAM.PA_NAME)<>"tank") AND ((dbo_SAMPLEPARAM.NRESULT) Is Not Null) AND ((dbo_SAMPLE.SAMPLE_TYPE) Like "*39184") AND ((dbo_SAMPLE.METHOD_SUBCLASS)="batch") AND ((dbo_SAMPLE.SAMPLE_STATUS)<>"vo1" And (dbo_SAMPLE.SAMPLE_STATUS)<>"Vrj"));
------------------------------------------------

Then have the stat SW connect to the Sample_Table, does it work?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Sorry it took so long to respond - had to verify data.  Yes this works.  I had initially thought of using a make table at one time but did not go down that path because I didn't know how automate the "make table".  From what I know about the make tables, I would have to click on icon to get it to update the table.  I have approx 200 of these to do and would prefer not to mess with access after I connect the stat SW.  
I am using an ASCII text file that includes a connect command and an askfilter command to query a selected date range to pull into stat SW. I have successfully connected to the query (as long as it does not have the replace function) and can connect with the make table table you just had me create.  Is there a way to have the dB update the table without user interface - doing that for a large volume of tables (not slowing the dB down)?
If not, I would entertain any other ideas anyone might have  :)

Author

Commented:
Is there a way to remove the qualifier without the make table or SQL?
Most Valuable Expert 2014

Commented:
Can you run an update query on the linked tables. Not sure which table the CSResult is in but this is the general idea.
-------------------------------------------------------
UPDATE dbo_SAMPLE
SET [CSResult]= Replace([CSResult],"<","")
------------------------------------------------------

And then build an autoexec macro that runs when you open the db.

Author

Commented:
I have to look up how to do it and the try it.
Most Valuable Expert 2014

Commented:
Just create the query(s).

Then go to the macros page and do new macro, name it "autoexec" and then hit the seletion for run query action.

Author

Commented:
Not able to update the table - permission denied.  I am only set up for read only to these linked tables.  :(  
Thanks again for continuing to work with me on this.
These are the approaches we have tried so far;  
X  Update Table - Can not update the linked tables with read only access
X  Make table - need to find a way to automatically update the table without user
One thougth I had was Access VB -but I don't know how to do it.  In a previous posting I wrote this....

"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, "=", "")"

rockiroads asked and my reply
I thought problem was due to Replace, due to undefined expression.
r u using stats program to run query?  No.  I am trying to connect to the query with the stats program. When I try to connect, I get the Undefined function 'Replace' in expression error.  The stats programers state it is an issue with the Jet Engine and that I would have to speak with IT on my side  - probably creating the update table that jimpen was trying to do
I wonder if query is run client side, perhaps thats why
whats it written in?
can u specify to run server side? what happens then - don't know what you are asking, i beleive i may have answered that above.



Most Valuable Expert 2014

Commented:
>> Not able to update the table - permission denied.  I am only set up for read only to these linked tables.

Well you can do the make table in the autoexec macro as well. That may be the route to go.

Then have a scheduled task on a server to open the DB automatically so it update the table.

Commented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Accept: jimpen {http:#16326793}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

jjafferr
EE Cleanup Volunteer
CERTIFIED EXPERT
Top Expert 2006

Commented:
No objections from me

the only other things that could be looked at is service packs. gagliakm mentioned Jet. Question is are they using the latest service pack? if not it might be worth a try to upgrade.
Alternatively they could try re-registering the dll.

Commented:
gagliakm

This is a common problem with Access 2000 without any Service Patches,
so the best thing to do is to change the Replace Function with something else (I can see the guys who tried to help, were trying to make the Replace work, rather than to change the Replace).
 
I replaced:
Replace([CSResult],"<","") AS Result
with
iif(left([CSResult])="<" or left([CSResult])=">" or left([CSResult])="=",mid([CSResult],2),[CSResult]) AS Result

please try the SQL now:

SELECT DISTINCT dbo_SAMPLE.REG_ON, dbo_SAMPLE.LOT, dbo_SAMPLEPARAM.PA_NAME, dbo_SAMPLEPARAM.NRESULT, iif(left([CSResult])="<" or left([CSResult])=">" or left([CSResult])="=",mid([CSResult],2),[CSResult]) AS Result, dbo_SAMPLEPARAM.CSRESULT, dbo_SAMPLEPARAM.LIMIT1LO, dbo_SAMPLEPARAM.LIMIT1HI, dbo_SAMPLEPARAM.LIMIT2LO, dbo_SAMPLEPARAM.LIMIT2HI, dbo_SAMPLEPARAM.LDL
FROM dbo_SAMPLE INNER JOIN dbo_SAMPLEPARAM ON dbo_SAMPLE.SAMPLE_ID = dbo_SAMPLEPARAM.SAMPLE_ID
WHERE (((dbo_SAMPLEPARAM.PA_NAME)<>"UNANALYZED" And (dbo_SAMPLEPARAM.PA_NAME)<>"tank") AND ((dbo_SAMPLEPARAM.NRESULT) Is Not Null) AND ((dbo_SAMPLE.SAMPLE_TYPE) Like "*39184") AND ((dbo_SAMPLE.METHOD_SUBCLASS)="batch") AND ((dbo_SAMPLE.SAMPLE_STATUS)<>"vo1" And (dbo_SAMPLE.SAMPLE_STATUS)<>"Vrj"));


jaffer
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.