Re: MID(,,) equiv in JET SQL using VB6/ADO

Tech-Archive recommends: Speed Up your PC by fixing your registry


"Paul Clement" <UseAdddressAtEndofMessage@xxxxxxxxxxxxxx> wrote in message
news:mj5s239aifkcvu40vi2a94rmd3f53n6c25@xxxxxxxxxx
On Mon, 23 Apr 2007 14:19:36 -0500, "jpm" <uh@xxxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

¤ Hello,
¤
¤ What equivalent of the MID(,,) vba expression can I use in a query
against a
¤ JET database? I'm using VB6 and ADO 2.6.
¤
¤ I think that with DAO I might use this function; I know that with T-SQL
¤ querying I'd use the subString(,,) function.
¤ Both functions are failing to execute when running the query against the
Jet
¤ file:
¤
¤ >>>"SELECT order_num, Mid([invoicedate], 3, 2) as Month,
mid([InvoiceDate],
¤ >>>1,2) as Yr, cust_Code , soldtoName, ProdLine INTO tmpOrders FROM
ADtl
¤ >>>INNER JOIN AHd ON Adtl.Lorder = AHd.HOrder WHERE InvoiceDate Between
¤ >>>'060101' And '060301' And (fg = 'xXX-NC' or fg = 'xXX.NC')"<<<<
¤
¤ Thanks for any input,

The Mid function is supported by Jet SQL, however just as in VBA if Mid
attempts to process invalid
data an error will be generated.

What type of error are you encountering? You may have to use the IIf
function to validate your data
before attempting to execute Mid.


Paul, please explain to me what the OP was attempting to do. I think I
missed something? <g>

-ralph


.

Quantcast