Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using Replace Function in Access SQL 1

Status
Not open for further replies.
fischadler

fischadler

Programmer
May 31, 2002
258
MT
Hi,
I am trying to run this Select query through ASP:

Code: 
SELECT EmployersID, EmployerName, PostCode 
FROM Employers
WHERE (((Replace(PostCode,' ','',1,-1))='N78PS'));

This query works fine in Access when pasted in SQL View, but when I run it in ASP, I am getting this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression.


Is there some way around this?

Thanks!

-Fischadler
 
Sort by date Sort by votes
yes...replace() function doesnt work outside the MS Access when used in a access query...here is the work around...

1. create a new query in access...lets say myquery
2. Put this code in the query
3. on the Asp page....change your sql statement as follows...

SQL = "Execute myquery"

and then you are good to go...

-DNG
 
Upvote 0 Downvote
OK, thanks. But the 'N78PS' part is dynamic and depends on user input.

I found a make shift work around by creating two fields, one containing the original post code and one containing a copy of the post code without spaces. This is saved that way during the original saving of the record. The comparison is then made against the "no-spaces" postcode.

Good to know about the "Execute" SQL command - will serve for other purposes. I'm curious about why thd Replace function does not work outside access :(.

Thanks again.

-Fischadler
 
Upvote 0 Downvote
ok then you have to do this...

create the new query "myquery" and have the following sql statement in it...

Code: 
[red]PARAMETERS pcode Text (255);[/red]
SELECT EmployersID, EmployerName, PostCode 
FROM Employers
WHERE (((Replace(PostCode,' ','',1,-1))=[postcode]));

and in the ASP page have the following...

SQL = "EXECUTE myquery '"&yourvariable&"' "

-DNG
 
Upvote 0 Downvote
oops...typo...i meant this:

Code: 
PARAMETERS pcode Text (255);
SELECT EmployersID, EmployerName, PostCode 
FROM Employers
WHERE (((Replace(PostCode,' ','',1,-1))=[[red]pcode[/red]]));

-DNG
 
Upvote 0 Downvote
Still getting the original error:

Microsoft][ODBC Microsoft Access Driver] Undefined function 'Replace' in expression.

This is what I am doing:

Code: 
CleanPostCode = "N78PS"  'debugging purposes
strSQL = "EXECUTE qrySearchForVenues '" & CleanPostCode & "' "
rstVenues.Open strSQL

-Fischadler
 
Upvote 0 Downvote
OK. I will check from my office tomorrow because I am now at home. In the meantime, a star goes to you for your time. And thanks for the "PARAMETERS" tip - I didn't know about that!

Thanks again!

-Fischadler
 
Upvote 0 Downvote
Nope...it didn't work, even after installing Visual Basic 6 with SP 5.

It's just the "replace" function that seems to bother the web server. I even tried creating another query that loads like the original table but uses the Replace function. Then I call the query through my SQL string in ASP like this:
Code: 
CleanPostCode = "N78PS"
strSQL = "SELECT qryTest.RatingsEmployersID, qryTest.EmployerName, qryTest.PostCode, qryTest.Expr1 "
strSQL = strSQL & "FROM qryTest "
strSQL = strSQL & "WHERE (((qryTest.Expr1)='" & CleanPostCode & "'));"

Still didn't work.

-Fischadler
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
194
leifoet
leifoet
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
326
leifoet
leifoet
Swi
  • Locked
  • Question
PDF Interrogation
Replies
0
Views
110
Swi
Swi
leifoet
  • Locked
  • Question
Using date picker gives an error message, only at application start
Replies
1
Views
203
MarkSweetland
MarkSweetland
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
185
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top