Using Replace in query

[Flokke]

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

	artikelnummer2 = request.form("f_zoek")
	artikelnummer = trim(artikelnummer2)
	
	IF artikelnummer <> "" THEN
		set oRSonderdeel = server.CreateObject("ADODB.recordset")
		sqltext = "SELECT * FROM tblItem WHERE ItemCD LIKE '%"& artikelnummer & "%' ORDER BY ItemCD"

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:

artnr = replace(artikelnummer, " ", "")

And this is working.
But I also want to do a:

replace('ItemCD',' ','')

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

	artikelnummer2 = request.form("f_zoek")
	artikelnummer = trim(artikelnummer2)
	artnr = replace(artikelnummer, " ", "")

	IF artnr <> "" THEN
		set oRSonderdeel = server.CreateObject("ADODB.recordset")
		sqltext = "SELECT * FROM tblItem WHERE replace('ItemCD',' ','') LIKE '%"& artnr & "%' ORDER BY ItemCD"

Any help or other possibility would be appreciated.
Thx
Flokke

[jhardman]

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

[Flokke]

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

[NeoPa]

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 ;-)

[jhardman]

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

[Flokke]

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).

[NeoPa]

Have you tried out using the DAO library for this instead of the ADODB one Flokke?

[Flokke]

Hello NeoPa

I got following: Server.CreateOb ject Failed

		set oRSonderdeel = server.CreateObject("DAO.recordset")
		sqltext = "SELECT * FROM tblItem WHERE replace('ItemCD',' ','') LIKE '%"& artnr & "%' ORDER BY ItemCD"

[jhardman]

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

[Flokke]

Hello Jared

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

[NeoPa]

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).

[Flokke]

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

[NeoPa]

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.

[Flokke]

Thx. Maybe I'll try that some time.