Avatar of whiwex
whiwex

asked on 

Is it possible to us replace in a query using vb 2005

Is this possible and if so why do I get a undefined funtion when I do the fill command
Me.daObligationsAdapter = New OleDb.OleDbDataAdapter("Select * From obligations order by val(replace([obligationnumber],'-',''))", cnADOconnection4)
 cbCommandBuilder4 = New OleDb.OleDbCommandBuilder(Me.daObligationsAdapter)
 Me.daObligationsAdapter.Fill(Me.dtAllObligations)    ERRORS HERE  
Visual Basic Classic.NET Programming

Avatar of undefined
Last Comment
JohnBPrice
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

No, you cannot use a VB function in a OLEDB query.  The database where the query will be executed does not know anything about VB functions, and the query engine will block such function usages.

AW
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

What database are you executing this query against?

you might look into creating a stored procedure (if the database supports Stored procedures) to do this for you.

AW
Avatar of whiwex
whiwex

ASKER

I'm using a Access database
Avatar of whiwex
whiwex

ASKER

My problem is I tryin to sort a field in the database ObligationNumber which contains numbers like this
07031-1
07031-10
03031-9
Because the field is text it doesn't sort properly
Avatar of JohnBPrice
JohnBPrice

Access supports replace, for example this query

SELECT Table1.Field1, Replace(field1,'Hi','Bye') AS Expr1
FROM Table1;

will produce these results

Field1      Expr1
go away      go away
hi there      Bye there
Avatar of Anthony Perkins
>>Access supports replace, for example this query<<
That is true,  However and unfortuntely JET does not.
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

And OLEDB uses the JET engine to process the queries to ACCESS, so the SQL must be essential 'pure' sql, with no 'local' functions.  OLEDB is a much more generic set of protocols and cannot use functions that are peculiar to any one back end database.

AW
Avatar of JohnBPrice
JohnBPrice

wow, you are right. that sucks.

This horrendous abuse of left, mid, and instr works though....

SELECT Table1.Field1, left([Field1] , instr([Field1],'-')-1 )   + Mid([Field1], instr([Field1],'-')+1   )    AS Expr1 FROM Table1;
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

However, that will not get whiwex what he needs, since the Left and Mid functions return STRINGS, and that way the thre example values will be trated as strings"

07031-1  becomes "07031"
07031-10 becomes "070310"  which will sort BEFORE "07032"
03031-9   becomes "030319"  which will sort AFTER "0303110"

AW
ASKER CERTIFIED SOLUTION
Avatar of JohnBPrice
JohnBPrice

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of JohnBPrice
JohnBPrice

Thanks.
Another thought to consider instead of zero-filling is to split the text into it's two number, and sorting by both, e.g.
order by val(left([Field1] , instr([Field1],'-')-1 )),  val( Mid([Field1], instr([Field1],'-')+1   )   )
Visual Basic Classic
Visual Basic Classic

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.

165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo