By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Using Replace in query

P: 7
Hello

I'm using an Access (could be 2000 or higher. Didn't make it) database.

I have a search box on a website where visitors can search for item numbers (tblItem > ItemCD).

Everything is fine if I use
Expand|Select|Wrap|Line Numbers
  1.     artikelnummer2 = request.form("f_zoek")
  2.     artikelnummer = trim(artikelnummer2)
  3.  
  4.     IF artikelnummer <> "" THEN
  5.         set oRSonderdeel = server.CreateObject("ADODB.recordset")
  6.         sqltext = "SELECT * FROM tblItem WHERE ItemCD LIKE '%"& artikelnummer & "%' ORDER BY ItemCD"
  7.  
Problem is that some partnumber contain spaces, other do not... and with the code above the number or part of it has to be inputted as it is in the table.

What I would like to achieve is that from the input from the search all spaces are out:

Expand|Select|Wrap|Line Numbers
  1. artnr = replace(artikelnummer, " ", "")
  2.  
And this is working.
But I also want to do a:
Expand|Select|Wrap|Line Numbers
  1. replace('ItemCD',' ','')
  2.  
in the query itself... and that is not working.

The table 'tblItem' contains about 75000 records.
I got following error.

Microsoft JET Database Engine error '80040e14'
Undefined function 'replace' in expression.

This is the code
Expand|Select|Wrap|Line Numbers
  1.     artikelnummer2 = request.form("f_zoek")
  2.     artikelnummer = trim(artikelnummer2)
  3.     artnr = replace(artikelnummer, " ", "")
  4.  
  5.     IF artnr <> "" THEN
  6.         set oRSonderdeel = server.CreateObject("ADODB.recordset")
  7.         sqltext = "SELECT * FROM tblItem WHERE replace('ItemCD',' ','') LIKE '%"& artnr & "%' ORDER BY ItemCD"
  8.  
Any help or other possibility would be appreciated.
Thx
Flokke
Dec 10 '10 #1
Share this Question
Share on Google+
13 Replies


jhardman
Expert 2.5K+
P: 3,405
So if I understand this right, you want to type in 'cdf12r' and get matches 'cdf 12r' and 'cdf 12 r' right? I think we will need to bring in an access expert, I'll ask someone to join.

Jared
Dec 10 '10 #2

P: 7
exactly Jared. I want to type in 'sdef' or 's d e f' where i can get the spaces off. but i need to have a match with ' sd ef' or other. So I thought to remove the spaces in both and it should work, but the replace in the query doesnt work. thx for your help.
Flokke
Dec 10 '10 #3

NeoPa
Expert Mod 15k+
P: 31,540
I can only contribute to this by saying that your SQL would work if run within Access. I have no experience using Access queries from outside of Access I'm afraid. I'm one of the Access experts Jared referred to.

One thing which may be important is that Access uses DAO natively, rather than ADODB. I would expect only DAO recordset processing supports using the more standard VBA type function calls (such as Replace()).

Whatever you end up using though, your code for the function call needs the parameter 'ItemCD' to be changed to [ItemCD]. Clearly this is not the cause of your problem, but it will be the cause of another if not fixed ;-)
Dec 11 '10 #4

jhardman
Expert 2.5K+
P: 3,405
One of the reasons I suggested we get an access expert was that I wasn't sure Replace() would work in an access query. Glad to hear it would work.

Would there be any problem removing the spaces permanently from that column (besides the logistics of updating 7500 records)?

Jared
Dec 11 '10 #5

P: 7
Thank you. I will have to find a workaround I suppose.
@Jared: removing the spaces isn't an option because there are also articles with . in them; Like 123.456.789 (and I also need to be able to find them when giving in: 123456789).
Dec 13 '10 #6

NeoPa
Expert Mod 15k+
P: 31,540
Have you tried out using the DAO library for this instead of the ADODB one Flokke?
Dec 13 '10 #7

P: 7
Hello NeoPa

I got following: Server.CreateObject Failed

Expand|Select|Wrap|Line Numbers
  1.         set oRSonderdeel = server.CreateObject("DAO.recordset")
  2.         sqltext = "SELECT * FROM tblItem WHERE replace('ItemCD',' ','') LIKE '%"& artnr & "%' ORDER BY ItemCD"
  3.  
Dec 14 '10 #8

jhardman
Expert 2.5K+
P: 3,405
Adodb is fairly standard, I'm pretty certain I used it when I used access. The error suggests the script doesn't recognize "dao.recordset".

Jared
Dec 14 '10 #9

P: 7
Hello Jared

That's what I also think. adodb is standard, but dao isn't I think.
Dec 14 '10 #10

NeoPa
Expert Mod 15k+
P: 31,540
ADODB is certainly more standard. It will work, as is, for more back-end systems (It will also work in that same way for Access or Jet of course). What it doesn't allow you to do though, is to use the non-standard features of Jet. Only DAO will allow you to do that. I wouldn't suggest that you use DAO generally in your database work. Only in a situation, such as this, where you need more than the basic, standard, SQL facilities.

DAO has a similar, but not identical, API, I'm afraid. To get the correct API calls working for DAO you can go into the Help system for Access (from the Integrated Development Environment - IDE - for VBA).
Dec 15 '10 #11

P: 7
Thank you NeoPa.

What I think the best solution is:

I'll ask the creators of the database (I didn't create it myself because it is also used in a stand-alone app and uploaded by customer on regular bases) to create an additional field (eg ItemCD2) where the value is placed without '.' or ' '. I think that would be easy because the Replace function works in Access.

Then I could do the search on ItemCD2...

Correct me if I'm wrong.

Anyway I appreciated your help!
Flokke
Dec 16 '10 #12

NeoPa
Expert Mod 15k+
P: 31,540
No correction required ;-)

There is another possible solution though. I suspect the chances of it working actually to be less than 50%, but it's probably worth trying anyway. You could get a QueryDef (saved query in Access) to be set up that creates that field on the fly, and work with the QueryDef instead. If it works it would be a neater and more contained solution than having to create an extra, non-normalised, field in the table, that requires permanent maintenance.
Dec 16 '10 #13

P: 7
Thx. Maybe I'll try that some time.
Dec 17 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.