Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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

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!
0
gagliakm
Asked:
gagliakm
  • 13
  • 8
  • 6
  • +1
1 Solution
 
Jim P.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.
0
 
rockiroadsCommented:
just a thought, check your references, go to code module, then select Tools/References
is everything okay? have u anything listed as MISSING
0
 
gagliakmAuthor 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"));
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rockiroadsCommented:
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
0
 
gagliakmAuthor Commented:
I don't have any module created to check the code.  I need to create one but don't know what to write.
0
 
Jim P.Commented:
>> hence the reason to look at your references

Go to the debug window (Ctrl+G) Tools --> References
0
 
rockiroadsCommented:
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
0
 
gagliakmAuthor 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.

0
 
rockiroadsCommented:
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


0
 
Jim P.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.
0
 
gagliakmAuthor 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!!!  :)
0
 
gagliakmAuthor 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?
0
 
gagliakmAuthor Commented:
I hit the run button and received an error
Compile error:  Syntax error
0
 
gagliakmAuthor Commented:
Public Sub TestReplace() came up as a yellow line - Does that means there is a problem with it?
0
 
rockiroadsCommented:
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

0
 
Jim P.Commented:
>> stats software program

Is it compled VB or something like that?
0
 
gagliakmAuthor 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
0
 
gagliakmAuthor 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
0
 
Jim P.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?
0
 
gagliakmAuthor 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  :)
0
 
gagliakmAuthor Commented:
Is there a way to remove the qualifier without the make table or SQL?
0
 
Jim P.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.
0
 
gagliakmAuthor Commented:
I have to look up how to do it and the try it.
0
 
Jim P.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.
0
 
gagliakmAuthor 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.



0
 
Jim P.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.
0
 
jjafferrCommented:
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
0
 
rockiroadsCommented:
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.

0
 
jjafferrCommented:
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 13
  • 8
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now